SET CONSTRAINTS ALL DEFERRED在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 
 ON UPDATE CASCADE ON DELETE CASCADE; 
插入（i）VALUES（1）; 
  

然后执行以下操作：

  START TRANSACTION; 
 SET CONSTRAINTS ALL DEFERRED; 
 INSERT INTO b（j）VALUES（2）; 
插入（i）VALUES（2）; 
 COMMIT; 
  

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

解决方案

对于初学者来说，只有 DEFERRABLE 推迟。

但是，这不会帮助你的情况，因为FK约束不能这样弯曲 。 ：

反转您的 INSERT 语句的顺序。
$ b

Related：

约束定义的可延迟初始即时仍然延迟

If I define tables a and b as follows:

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?

解决方案

For starters, only DEFERRABLE constraints can be deferred.

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

Reverse the sequence of your INSERT statements.

Related:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

这篇关于SET CONSTRAINTS ALL DEFERRED在PostgreSQL 9.3中无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！