SET约束所有DEFERRED在PostgreSQL 9.3中没有按预期工作

If I define tables a and b as follows:

如果我按如下方式定义表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);

And then do the following:

然后执行以下操作：

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

It produces the error below. Why is SET CONSTRAINTS not having the desired effect?

它产生以下错误。为什么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".

错误：表“b”上的插入或更新违反外键约束“fkey_ij”SQL状态：23503详细信息：表（a）中不存在键（j）=（2）。

1 个解决方案

#1

For starters, only DEFERRABLE constraints can be deferred.

对于初学者，只能推迟DEFERRABLE约束。

But that won't help your case because, FK constraints cannot be bent this way at all. Per documentation:

但这对你的情况没有帮助，因为FK约束根本不能这样弯曲。每个文件：

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

即使约束被声明为可延迟，也不能延迟除NO ACTION检查以外的参照动作。

Reverse the sequence of your INSERT statements.

反转INSERT语句的顺序。

有关：

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
约束定义DEFERRABLE INTITIALLY IMMEDIATE仍然是DEFERRED？

#1