1. 首页
  2. 问题
  3. SET CONSTRAINTS ALL DEFERRED无法正常工作
一尘不染

SET CONSTRAINTS ALL DEFERRED无法正常工作

sql

在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”.


阅读 155

收藏
2021-03-17

共1个答案

一尘不染

只能 DEFERRABLE 推迟约束。

首先让我提出更好的选择：

1.INSERT按顺序

颠倒 语句 的 顺序，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<>在这里拨弄

我的原始答案引用了手册：

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

但这具有误导性，因为它仅适用于“引用动作”，即发生的情况ON UPDATE或ON DELETE引用表中的行。@zer0hedge指出的情况并非其中一种。

2021-03-17