SQLite多对多关联表外键建模及约束实现问题

SQLite 对外部键约束有明确要求：外键引用的列必须是被引用表的主键，或者该列上存在唯一约束。你当前的Link表主键是(link_id, part_id)复合主键，单独的link_id既不是主键也没有唯一约束，因此Main表的外键引用不符合规则，触发foreign key mismatch报错。

符合数据库第三范式，使用原生外键保证约束，性能最优，兼容原有查询逻辑。
核心思路是拆分出独立的LinkMaster表存储所有合法的link_id，作为全局唯一的外键引用源。

建表语句 #

-- 存储所有合法link_id，作为外键引用的主表
CREATE TABLE `LinkMaster` (
    `link_id` integer NOT NULL PRIMARY KEY
);

-- 原Link表改为关联表，外键关联LinkMaster
CREATE TABLE `Link` (
    `link_id` integer NOT NULL REFERENCES `LinkMaster`(`link_id`),
    `part_id` integer NOT NULL,
    CONSTRAINT `link_pk` PRIMARY KEY(`link_id`,`part_id`)
);

-- Main表外键改为关联LinkMaster的link_id
CREATE TABLE `Main` (
    `main_id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    `link_id` integer NOT NULL REFERENCES `LinkMaster`(`link_id`)
);

数据插入示例 #

插入逻辑需保证先插LinkMaster，再插关联的Link和Main数据，建议用事务包裹保证一致性：

-- 先插入合法的link_id
INSERT INTO `LinkMaster` (link_id) VALUES (1), (2);
-- 插入link对应的part关联关系
INSERT INTO `Link` (link_id, part_id) VALUES (1,10),(1,11),(1,12),(2,15);
-- 插入Main表数据，不会触发外键报错
INSERT INTO `Main` (main_id, link_id) VALUES (1,1),(2,1),(3,2);

约束满足说明 #

1. 原生外键自动保证Main表的link_id必须在LinkMaster中存在
2. 业务插入时用事务包裹LinkMaster和Link的插入操作，即可保证所有LinkMaster中的link_id在Link表中至少有一条对应记录，间接满足Main的link_id在Link中存在的要求
3. 原有查询逻辑完全兼容，不需要修改任何业务查询代码

适合存量系统不想调整表结构的场景，通过触发器替代外键实现约束校验。

触发器创建语句 #

-- 插入Main表前校验link_id合法性
CREATE TRIGGER check_main_link_id_insert
BEFORE INSERT ON `Main`
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM `Link` WHERE link_id = NEW.link_id)
BEGIN
    SELECT RAISE(ABORT, '非法link_id，Link表中不存在该记录');
END;

-- 更新Main表link_id字段前校验合法性
CREATE TRIGGER check_main_link_id_update
BEFORE UPDATE OF link_id ON `Main`
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM `Link` WHERE link_id = NEW.link_id)
BEGIN
    SELECT RAISE(ABORT, '非法link_id，Link表中不存在该记录');
END;

方案说明 #

不需要调整原有表结构和业务插入逻辑，触发器会自动校验link_id合法性，约束效果和外键一致，仅在数据量极大的场景下性能略低于原生外键。

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