postgresql - 涉及多个表的外键约束

Question

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

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

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

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

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

Answer 1

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

Would like information on if this is possible to achieve without database triggers

FOREIGN KEY constraints可以跨越多列。只需在从表 C 到表 B 的 FK 约束中包含表 A 的 ID。这会强制 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".

db<> fiddle here

默认MATCH SIMPLE FK 约束的行为是这样的(quoting the manual):

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

因此 c(b_id) 中的值为 NULL仍然允许(根据要求:“可选字段”)。对于这种特殊情况，FK 约束被“禁用”。

我们需要逻辑上冗余的 UNIQUE对 b(a_id, b_id) 的约束允许 FK 引用它。但是通过让它出现在 (a_id, b_id) 上而不是 (b_id, a_id) , 它本身也很有用，提供关于 b(a_id) 的有用索引支持其他 FK 约束等。见:

• Is a composite index also good for queries on the first field?

(c(a_id) 上的附加索引通常相应有用。)

进一步阅读:

• Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?
• Enforcing constraints “two tables away”