sql - SET CONSTRAINTS ALL DEFERRED 未按预期工作

Question

在 PostgreSQL 9.3 数据库中，如果我定义表 a 和 b 如下:

CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j)
      REFERENCES a (i) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE;
INSERT INTO a(i) VALUES(1);

然后执行以下操作:

START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO b(j) VALUES(2);
INSERT INTO a(i) VALUES(2);
COMMIT;

它会产生以下错误。为什么 SET CONSTRAINTS 没有达到预期的效果？

ERROR: insert or update on table "b" violates foreign key constraint "fkey_ij"
SQL state: 23503 Detail: Key (j)=(2) is not present in table "a".

Answer 1

只有 DEFERRABLE 约束可以延迟。

让我先推荐更好的选择:

1。 按顺序插入

反转 INSERT 语句的顺序，无需延迟任何内容。最简单和最快的 - 如果可能的话。

2。单个命令

在单个命令中完成。然后仍然不需要延迟，因为不可延迟的约束在每个命令之后被检查并且 CTE 被认为是单个命令的一部分:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(2)
   )
INSERT INTO a(i) VALUES(2);

同时，您可以重用第一个 INSERT 的值:对于某些情况或多行插入更安全/更方便:

WITH ins1 AS (
   INSERT INTO b(j) VALUES(3)
   RETURNING j
   )
INSERT INTO a(i)
SELECT j FROM ins1;

但我需要延迟约束! (真的吗？)

ALTER TABLE b ADD CONSTRAINT fkey_ij FOREIGN KEY (j)
   REFERENCES a (i) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;  -- !!!

然后您的原始代码就可以工作了(有点慢，因为延迟约束会增加成本)。

db<> fiddle here

相关:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

---

我原来的答案quoted the manual :

Referential actions other than the NO ACTION check cannot be deferred,even if the constraint is declared deferrable.

但这具有误导性，因为它仅适用于“引用操作”，即 ON UPDATE 或 ON DELETE 对引用表中的行发生的情况。手头的案例不是其中之一-如@zer0hedge pointed out .