如何确保SQLite中Inspection与关联Part引用同一Lot？

先贴出你的原表结构：

CREATE TABLE Lots (
    Id INTEGER PRIMARY KEY
);

CREATE TABLE Parts (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots
);

CREATE TABLE Inspections (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots,
    PartId INTEGER NULL REFERENCES Parts
);

目前的核心问题是：Inspection同时关联Lot和可选的Part，但没有约束机制保证这个Part属于Inspection对应的Lot。下面是两种工业界常用的标准处理方式：

方式一：复合外键约束（推荐） #

SQLite支持复合外键，但要求被引用的字段组合必须具备唯一性。因此需要先给Parts表添加复合唯一索引，再给Inspections表设置复合外键：

1. 修改Parts表，新增复合唯一约束：

CREATE TABLE Parts (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots,
    UNIQUE(LotId, Id) -- 让(LotId, Id)成为唯一标识，供外键引用
);

2. 修改Inspections表，添加复合外键：

CREATE TABLE Inspections (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots,
    PartId INTEGER NULL,
    -- 复合外键：同时关联Parts的LotId和Id，确保两者的Lot归属一致
    FOREIGN KEY (LotId, PartId) REFERENCES Parts(LotId, Id)
);

设置后，插入或更新Inspection时，只要指定了PartId，数据库会自动校验该Part的LotId是否和当前Inspection的LotId一致，不一致则直接报错拦截。

方式二：触发器校验（适合无法修改表结构的场景） #

如果不想改动现有表结构，可以通过触发器在数据变更前主动检查一致性：

-- 插入Inspection前的校验
CREATE TRIGGER CheckInspectionPartLot_Insert
BEFORE INSERT ON Inspections
FOR EACH ROW
WHEN NEW.PartId IS NOT NULL
BEGIN
    SELECT RAISE(ABORT, 'Inspection的LotId与关联Part的LotId不匹配')
    WHERE (SELECT LotId FROM Parts WHERE Id = NEW.PartId) != NEW.LotId;
END;

-- 更新Inspection前的校验
CREATE TRIGGER CheckInspectionPartLot_Update
BEFORE UPDATE ON Inspections
FOR EACH ROW
WHEN NEW.PartId IS NOT NULL
BEGIN
    SELECT RAISE(ABORT, 'Inspection的LotId与关联Part的LotId不匹配')
    WHERE (SELECT LotId FROM Parts WHERE Id = NEW.PartId) != NEW.LotId;
END;

触发器会在插入或更新操作触发时，自动验证关联Part的LotId是否和Inspection的LotId一致，不一致则终止操作并抛出错误提示。

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