SQLite3创建GameLicense表CHECK与外键约束失败问题求解

核心错误原因 #

• 前4位数字校验的substr函数缺失长度参数：SQLite中substr(X, start)会返回从起始位到字符串末尾的所有内容，你原有代码中substr(license_id,1)取到的是完整5位id，转成整数后必然大于9，直接触发CHECK约束失败。正确写法需要指定第三个参数为1，即substr(license_id, 1, 1)才能取到单个位置的字符。
• 误用不支持的UNSIGNED类型：SQLite没有无符号整数类型，CAST(xxx AS UNSIGNED)属于无效语法，直接替换为INTEGER即可。
• 校验位取最后一位的逻辑错误：你直接对数值计算结果调用substr无法生效，要么先将计算结果转为TEXT再取最后一位，更简单的方案是直接用模10运算% 10取最后一位，稳定性更高。
• 关联表外键语法错误：gameRental表中第一个外键定义末尾缺少逗号，ON UPDATE CASCADE仅绑定到了gamer_id的外键上，如果你需要license_id也支持级联更新需要单独声明。

你推测的外键报错原因完全正确：因为GameLicense表的CHECK约束错误，合法数据也无法插入，后续插入gameRental时找不到对应的license_id值，自然触发外键约束失败，修复主表约束后该问题会同步解决。

修正后完整代码 #

CREATE TABLE GameLicense (
    title TEXT,
    release_year INTEGER,
    platform TEXT,
    license_id TEXT PRIMARY KEY,
    -- 校验长度为5位
    CHECK (length(license_id) = 5),
    -- 校验前4位均为数字
    CHECK (CAST(substr(license_id,1,1) AS INTEGER) BETWEEN 0 AND 9),
    CHECK (CAST(substr(license_id,2,1) AS INTEGER) BETWEEN 0 AND 9),
    CHECK (CAST(substr(license_id,3,1) AS INTEGER) BETWEEN 0 AND 9),
    CHECK (CAST(substr(license_id,4,1) AS INTEGER) BETWEEN 0 AND 9),
    -- 校验最后一位校验位规则
    CHECK (
        CAST(substr(license_id, 5, 1) AS INTEGER) = 
        (
            1 * CAST(substr(license_id,1,1) AS INTEGER) + 
            3 * CAST(substr(license_id,2,1) AS INTEGER) + 
            1 * CAST(substr(license_id,3,1) AS INTEGER) + 
            3 * CAST(substr(license_id,4,1) AS INTEGER)
        ) % 10
    )
);

CREATE TABLE gameRental (
    gamer_id INTEGER,
    license_id TEXT,
    date_out TEXT,
    date_back TEXT,
    rental_cost REAL,
    FOREIGN KEY (license_id) REFERENCES GameLicense (license_id) ON UPDATE CASCADE,
    FOREIGN KEY (gamer_id) REFERENCES Gamer (gamer_id) ON UPDATE CASCADE
);

验证示例 #

你可以用合法数据测试插入：比如前四位取1234，计算校验位为1*1 +3*2 +1*3 +3*4 = 22，最后一位是2，所以license_id为12342，执行插入语句即可成功：

INSERT INTO GameLicense (title, release_year, platform, license_id)
VALUES ('测试游戏', 2024, 'PC', '12342');

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