SQL 约束检查是否在另一个表中的条目

Question

如何根据一个整数 ID 来限制引用一个公用表的两个表不包含另一个表中已经存在的相同元素？

假设创建了一个基本的个人信息表:

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    info VARCHAR(10)
);

然后创建两个引用 person 的表:

CREATE TABLE special (
    id INTEGER PRIMARY KEY,
    skill VARCHAR(10),
    FOREIGN KEY (id) REFERENCES person(id)
);

CREATE TABLE snowflake (
    id INTEGER PRIMARY KEY,
    meltingpoint DECIMAL,
    FOREIGN KEY (id) REFERENCES person(id)
);

但是，我想限制两个表不包含同一个人。

所以我认为像这样的东西可以解决问题，但是 SQLite3 给出了语法错误(接近 CHECK)。

ALTER TABLE special ADD CHECK (
    (SELECT COUNT(*) FROM snowflake WHERE snowflake.id = special.id) = 0
);

或基于 this answer

ALTER TABLE special ADD CHECK (
    NOT EXISTS (SELECT 1 FROM snowflake WHERE snowflake.ID = special.ID)
);

如何实现？我是在做某事还是应该采取完全不同的方法？

Answer 1

documentation说:

The expression of a CHECK constraint may not contain a subquery.

所以你必须使用trigger小号:

CREATE TRIGGER no_snowflake_if_special
AFTER INSERT ON snowflake
WHEN EXISTS (SELECT * FROM special WHERE id = NEW.id)
BEGIN
    SELECT RAISE(FAIL, "a special with the same ID already exists");
END;

-- same for special