涉及多个表的外键约束

我在 Postgres 9.3 数据库中有以下场景：

• 表 B 和 C 参考表 A。
• 表 C 有一个引用表 B 的可选字段。

我想确保对于表 C 中引用表 B 的每一行，c.b.a = c.a。也就是说，如果 C 引用了 B，则两行应指向表 A 中的同一行。

• 我可以重构表 C，以便如果指定了 c.b，则 c.a 为 null，但这会使连接表 A 和 C 的查询变得尴尬。
• 我也许还可以使表 B 的主键包含其对表 A 的引用，然后使表 C 的外键对表 B 包含表 C 对表 A 的引用，但我认为这种调整太尴尬了，无法证明其好处。
• 我认为这可以通过在表 C 上插入/更新之前运行的触发器来完成，并拒绝违反指定约束的操作。

在这种情况下是否有更好的方法来强制数据完整性？

有一个非常简单、万无一失的解决方案。适用于 Postgres 9.3 - 当提出原始问题时。适用于当前的 Postgres 13 - 当添加赏金中的问题时：

希望了解是否可以在没有数据库触发器的情况下实现这一点

FOREIGN KEY限制条件可以跨越多列。只需将表 A 的 ID 包含在从表 C 到表 B 的 FK 约束中即可。这会强制 B 和 C 中的链接行始终指向 A 中的同一行。例如：

CREATE TABLE a (
  a_id int PRIMARY KEY
);

CREATE TABLE b (
  b_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, UNIQUE (a_id, b_id)  -- redundant, but required for FK
);

CREATE TABLE c (
  c_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, b_id int
, CONSTRAINT fk_simple_and_safe_solution
  FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, b_id)  -- THIS !
);

最小样本数据：

INSERT INTO a(a_id) VALUES
  (1)
, (2);

INSERT INTO b(b_id, a_id) VALUES
  (1, 1)
, (2, 2);

INSERT INTO c(c_id, a_id, b_id) VALUES
  (1, 1, NULL)  -- allowed
, (2, 2, 2);    -- allowed

按要求不允许：

INSERT INTO c(c_id, a_id, b_id) VALUES (3,2,1);

ERROR:  insert or update on table "c" violates foreign key constraint "fk_simple_and_safe_solution"
DETAIL:  Key (a_id, b_id)=(2, 1) is not present in table "b".

数据库小提琴here

默认MATCH SIMPLEFK 约束的行为是这样的（引用手册):

MATCH SIMPLE允许任何外键列为空；如果其中任何一个为空，则该行不需要在引用的表中具有匹配项。

所以 NULL 值c(b_id)仍然允许（根据要求：“可选字段”）。对于这种特殊情况，FK 约束被“禁用”。

我们需要逻辑上的冗余UNIQUE约束于b(a_id, b_id)以允许 FK 引用它。但通过让它成为现实(a_id, b_id)代替(b_id, a_id)，它本身也很有用，提供了有用的索引b(a_id)除其他外，还支持其他 FK 约束。看：

• 复合索引也适合第一个字段的查询吗？

（附加索引c(a_id)因此通常很有用。）

进一步阅读：

• MATCH FULL、MATCH SIMPLE 和 MATCH PARTIAL 之间的区别？
• 强制执行“两张桌子之外”的约束