SET CONSTRAINTS ALL DEFERRED 未按预期工作 [英] SET CONSTRAINTS ALL DEFERRED not working as expected

问题描述

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

CREATE TABLE a(i integer);ALTER TABLE a 添加约束 pkey_a PRIMARY KEY (i);创建表 b(j 整数);ALTER TABLE b 添加约束 fkey_ij FOREIGN KEY (j)参考 a (i) 简单匹配ON UPDATE CASCADE ON DELETE CASCADE;插入 a(i) 值(1)；

然后执行以下操作:

开始交易；设置所有延迟的约束；插入 b(j) 值(2)；插入 a(i) 值(2)；犯罪;

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

<块引用>

错误:在表b"上插入或更新违反外键约束fkey_ij"SQL 状态:23503 详细信息:表a"中不存在键 (j)=(2).

解决方案

只有 DEFERRABLE 约束可以被延迟.

让我先提出更好的替代方案:

1.INSERT 按顺序

颠倒INSERT语句的顺序，无需延迟.最简单和最快的 - 如果可能的话.

2.单个命令

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

with ins1 AS (插入 b(j) 值(2))插入 a(i) 值(2)；

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

with ins1 AS (插入 b(j) 值(3)返回 j)插入 a(i)从 ins1 中选择 j；

但我需要延迟约束！(真的吗?)

ALTER TABLE b 添加约束 fkey_ij 外键 (j)参考 a (i) 简单匹配ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE;——！！！

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

db<>小提琴这里

相关:

• 约束定义 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED?

<小时>

我原来的回答引用了手册:

<块引用>

除了 NO ACTION 检查以外的参考动作不能被推迟，即使约束被声明为可延迟的.

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

In a PostgreSQL 9.3 database, 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?

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".

解决方案

Only DEFERRABLE constraints can be deferred.

Let me suggest superior alternatives first:

1. INSERT in order

Reverse the sequence of the INSERT statements and nothing needs to be deferred. Simplest and fastest - if at all possible.

2. Single command

Do it in a single command. Then still nothing needs to be deferred, as non-deferrable constraints are checked after each command and CTEs are considered to be part of single command:

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

While being at it, you can reuse the values for the first INSERT; safer / more convenient for certain cases or multi-row inserts:

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

But I need deferred constraints! (Really?)

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

Then your original code works (a bit slower, as deferred constraints add cost).

db<>fiddle here

Related:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

---

My original answer quoted the manual:

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

But that was misleading as it only applies to "referential actions", i.e. what happens ON UPDATE or ON DELETE to rows in the referenced table. The case at hand is not one of those - as @zer0hedge pointed out.

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