PHP脚本创建带UNIQUE约束的SQLite表报错如何解决？

问题根因 #

你第一次建表失败的核心原因是SQL列定义的语法顺序错误：SQLite的列定义语法要求为 列名 数据类型 [约束]，你把UNIQUE约束写在了数据类型VARCHAR(255)前面，违反语法规范导致建表语句执行失败，表没有被创建，后续操作自然报错。

方案1：建表时直接定义UNIQUE约束 #

推荐直接使用PHP内置的SQLite3扩展操作数据库，避免shell调用带来的权限、转义、错误无法捕获等问题。

写法1：单列单独加约束

$db = new SQLite3('/location/file.db');
// 注意UNIQUE放在数据类型后面
$createSql = <<<SQL
CREATE TABLE $tablename (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    record_time_long VARCHAR(255) UNIQUE,
    update_time_long VARCHAR(255) UNIQUE,
    name VARCHAR(255),
    macaddress VARCHAR(255),
    longitude VARCHAR(255),
    latitude VARCHAR(255),
    sourcedevice VARCHAR(255),
    human_address VARCHAR(250),
    smce_hash VARCHAR(250),
    sm_hash VARCHAR(250),
    islowenergy VARCHAR(250),
    rssi INT(4)
);
SQL;
$db->exec($createSql);
// 可打印错误排查问题
if ($db->lastErrorCode() != 0) {
    var_dump($db->lastErrorMsg());
}

写法2：统一声明约束（适合联合唯一场景）

如果需要多列联合唯一，也可以在表定义末尾统一声明约束：

$createSql = <<<SQL
CREATE TABLE $tablename (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    record_time_long VARCHAR(255),
    update_time_long VARCHAR(255),
    name VARCHAR(255),
    macaddress VARCHAR(255),
    longitude VARCHAR(255),
    latitude VARCHAR(255),
    sourcedevice VARCHAR(255),
    human_address VARCHAR(250),
    smce_hash VARCHAR(250),
    sm_hash VARCHAR(250),
    islowenergy VARCHAR(250),
    rssi INT(4),
    UNIQUE (record_time_long),
    UNIQUE (update_time_long)
);
SQL;

方案2：已创建的表添加UNIQUE约束 #

你之前执行CREATE UNIQUE INDEX没生效的常见原因有3个：

• web运行用户（通常是www-data）没有数据库文件的写入权限，shell命令静默执行失败无输出
• $tablename变量在shell环境下解析错误，或者存在特殊字符未转义
• 表中已经存在重复的update_time_long值，导致索引创建失败，shell调用未返回错误信息

改用PHP内置SQLite3扩展执行即可明确捕获错误，SQLite不支持直接ALTER TABLE修改列加UNIQUE约束，创建唯一索引的效果和列级UNIQUE约束完全一致：

$db = new SQLite3('/location/file.db');
$indexSql = "CREATE UNIQUE INDEX idx_{$tablename}_update_time ON $tablename(update_time_long);";
$db->exec($indexSql);
// 打印错误信息排查问题
if ($db->lastErrorCode() != 0) {
    die("索引创建失败：" . $db->lastErrorMsg());
}

注意事项 #

• 不要通过exec()调用系统sqlite3命令操作数据库，会存在shell注入风险、权限问题、错误无法捕获的问题，优先使用PHP内置的SQLite3扩展
• 添加UNIQUE约束前必须确保对应列没有重复值，否则约束会创建失败

内容的提问来源于stack exchange，提问作者sOnt