SQLite实现5位ID校验位约束时测试触发约束失败如何解决

你编写的SQL存在三处核心错误，导致校验逻辑失效：

• 错误1：CAST转换的参数是布尔判断表达式substr(xxx) BETWEEN 0 AND 9，该表达式的返回值是0（判断不成立）或1（判断成立），而非你预期的对应位的数字值，直接导致校验和计算完全错误。
• 错误2：取校验和最后一位的逻辑不严谨，使用substr(...,2,1)仅适用于校验和为两位数的场景，若校验和为个位数（如和为8）或三位数（如和为102），该写法无法正确取到最后一位。
• 错误3：license_id定义为INTEGER类型，会自动丢弃ID的前导零，比如01234会被存储为1234，直接触发长度校验失败，不符合固定5位ID的需求。

CREATE TABLE GameLicense_16 (
    title TEXT,
    release_year INTEGER,
    platform TEXT,
    license_id TEXT PRIMARY KEY
    CHECK (LENGTH(license_id) = 5)
    CHECK (
        substr(license_id, 5, 1) = CAST(
            (CAST(substr(license_id, 1, 1) AS INTEGER) * 1 +
             CAST(substr(license_id, 2, 1) AS INTEGER) * 3 +
             CAST(substr(license_id, 3, 1) AS INTEGER) * 1 +
             CAST(substr(license_id, 4, 1) AS INTEGER) * 3) % 10
        AS TEXT)
    )
);

执行插入语句INSERT INTO GameLicense_16 (title, release_year, platform, license_id) VALUES ('test', 2023, 'PC', '12342');可以正常插入，不符合校验规则的ID会触发约束报错。如果确实需要使用INTEGER类型存储ID，可将长度校验改为CHECK (license_id BETWEEN 10000 AND 99999)，同时所有substr操作前先将license_id转成字符串即可。

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