sql - 我应该暂时禁用外键约束吗？如何？

Question

我有两个表:

person:
    id serial primary key,
    name varchar(64) not null

task:
    tenant_id   integer not null references person (id) on delete cascade,
    customer_id integer not null references person (id) on delete restrict

(他们有更多的专栏，但其余的与问题无关。)

问题是，当租户 person 被删除时，我想级联删除一个 task。但是当租户和客户是同一个人时，customer_id外键约束会限制删除。

我的问题分为两部分:

1. 暂时禁用第二个外键是我唯一的选择吗？
2. 如果是这样，那么我该如何在 PostgreSQL 中做到这一点？

Answer 1

实际上，您创建了一个具有矛盾规则的竞争条件。

我的第一个冲动是检查是否有 DEFERRED约束会有所帮助。但它没有任何区别是有道理的。

我发现 CREATE TABLE 脚本中最先出现的 FK 约束是这场比赛的赢家。如果 ON DELETE CASCADE 先出现，则删除是级联的，如果 ON DELETE RESTRICT 先出现，则操作中止。

db<> fiddle here
<子>旧sqlfiddle

这似乎与目录表 pg_constraint 中较小的 oid 相关:

SELECT oid, * FROM pg_constraint WHERE conrelid = 'task'::regclass

但是您的反馈表明，这不是原因。也许pg_attribute.attnum决定比赛。无论哪种方式，只要它没有记录在案，您就不能指望它在下一个主要版本中保持这种状态。可能值得在 pgsql-general@postgresql.org 上发布问题。

独立于所有这些，您需要考虑其他行:即使 CASCADE 会通过 task 中同时具有 tenant_id 的行> 和 customer_id 指向一个 person，如果任何行只有 customer_id 引用 person，它仍然会被限制。

db<> fiddle here
<子>旧sqlfiddle

如何禁用约束？

最好的办法是删除并重新创建它。在事务内完成所有操作，以确保您不会破坏参照完整性。

BEGIN;

ALTER TABLE task DROP CONSTRAINT task_customer_id_fkey;

DELETE FROM person WHERE id = 3;

ALTER TABLE task ADD CONSTRAINT task_customer_id_fkey
FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT;

COMMIT;

这会以独占方式锁定表，不适合在多用户环境中进行常规使用。

我怎么知道约束的名称？如上所示，我从 pg_constraint 中获取了它。使用显式约束名称开头可能更容易:

CREATE TABLE task (
  customer_id integer NOT NULL
, tenant_id integer NOT NULL REFERENCES person (id) ON DELETE CASCADE
, CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id)
  REFERENCES person (id) ON DELETE RESTRICT
);

禁用触发器？

还有:

ALTER TABLE task DISABLE trigger ALL;

More in the manual here .但这会禁用所有 触发器。我没有运气尝试仅禁用系统创建的触发器来实现单个 FK 约束。未针对内部 触发器实现针对特定触发器。

其他替代方案是使用 triggers 实现您的制度或 rules .那会工作得很好，但这些并不像外键那样严格执行。