SQLite中两列组合外键的正确实现及约束失败排查

一、外键约束失败的核心原因 #

1. 数据类型不匹配：待插入的project_id是字符串"02"，但projects表的id是INTEGER类型。SQLite外键校验会严格匹配类型，字符串"02"与整数2无法被判定为相等，导致找不到对应父记录。
2. 父表缺失对应记录：即使类型修正，projects表中必须先存在(id=2, year=2000)的记录，否则外键约束依然触发失败。

二、正确的组合外键表结构实现 #

你的现有表结构在组合外键的语法定义上是正确的，可通过以下优化确保约束严谨性与类型一致性：

1. projects表（父表） #

CREATE TABLE "projects" (
    "id" INTEGER CHECK("id" BETWEEN 0 AND 99),
    "year" INTEGER CHECK("year" BETWEEN 1000 AND 9999),
    "api" TEXT NOT NULL,
    "analyst_id" TEXT CHECK(length("analyst_id") BETWEEN 2 AND 3),
    PRIMARY KEY ("id","year"),
    FOREIGN KEY ("analyst_id") REFERENCES "analysts"("id")
);

保持id的INTEGER类型，确保与子表外键类型统一。

2. analysis表（子表） #

CREATE TABLE "analysis" (
    "id" INTEGER PRIMARY KEY,
    "project_id" INTEGER NOT NULL,
    "project_year" INTEGER NOT NULL,
    "analyst_id" TEXT CHECK(length("analyst_id") BETWEEN 2 AND 3),
    "analyte" TEXT NOT NULL,
    "pharma_form" TEXT NOT NULL,
    "lot" TEXT NOT NULL,
    "months" TEXT NOT NULL,
    "analysis" TEXT NOT NULL CHECK("analysis" IN ('development', 'validation', 'stability')),
    "start" DATE,
    "end" DATE,
    CONSTRAINT FK_project FOREIGN KEY ("project_id", "project_year") 
        REFERENCES "projects"("id", "year")
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY ("analyst_id") REFERENCES "analysts"("id")
);

关键调整：

• 给project_id和project_year添加NOT NULL约束，匹配父表主键的非空属性
• 明确外键级联规则（可根据业务需求调整，比如ON DELETE CASCADE）
• 确保project_id为INTEGER类型，与父表id严格匹配

三、插入数据的正确流程 #

1. 先插入父表记录：在插入analysis数据前，必须先向projects表添加对应主键组合的记录：

INSERT INTO projects (id, year, api, analyst_id)
VALUES (2, 2000, '对应API名称', 'AF');

注意id使用整数2，而非字符串"02"。

2. 修正子表插入数据：调整project_id类型为整数，同时规范日期格式（SQLite建议用YYYY-MM-DD）：

INSERT INTO analysis (id, project_id, project_year, analyst_id, analyte, pharma_form, lot, months, analysis, start, end)
VALUES (2303028, 2, 2000, 'AF', 'Verapamil Clorhidrato', 'tablet', '1', '36', 'stability', '2023-03-13', NULL);

四、额外注意事项 #

• 确保SQLite开启外键约束：默认可能关闭，需执行PRAGMA foreign_keys = ON;启用，否则外键规则不生效
• 检查analyst_id是否存在于analysts表中，该字段同样是外键，缺失会触发约束失败

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