sql - PostgreSQL 'Deferrable Delete' 仍然在删除时遇到约束

Question

我想从两个表中删除行，这两个表通过一组可延迟约束相互依赖。为了简化这篇文章，我模拟了一个简单的数据库架构。

我希望从 SQL 事务/数据库补丁中的某个表“delete_from_me”中删除条目。问题是，在我松开链接本身之前，我想根据第二个表“constraining_table”中的选择进行删除。

下面是对这两个表的描述:

tab-quarantine=> \d delete_from_me
       Table "public.delete_from_me"
  Column   |       Type        | Modifiers 
-----------+-------------------+-----------
 id        | character varying | not null
 extension | character varying | not null
Indexes:
    "delete_from_me_pkey" PRIMARY KEY, btree (id)

tab-quarantine=> \d constraining_table 
   Table "public.constraining_table"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 image  | character varying | not null
 type   | character varying | not null
Foreign-key constraints:
    "constraining_table_image_fkey" FOREIGN KEY (image) REFERENCES delete_from_me(id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT DEFERRABLE

这是我刚刚在其中发布的一些示例数据:

tab-quarantine=> SELECT * FROM delete_from_me;
     id     | extension 
------------+-----------
 12345abcde | png
(1 row)

tab-quarantine=> SELECT * FROM constraining_table;
   image    |   type   
------------+----------
 12345abcde | select_me
(1 row)

这是我的交易:

BEGIN;
\set ON_ERROR_STOP 1
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM delete_from_me WHERE id IN (
    SELECT image FROM constraining_table WHERE type = 'select_me'
);
DELETE FROM constraining_table WHERE type = 'select_me';
COMMIT;

此交易失败。当我单步执行并手动执行此操作时，我看到以下错误消息:

ERROR:  update or delete on table "delete_from_me" violates foreign key constraint "constraining_table_image_fkey" on table "constraining_table"
DETAIL:  Key (id)=(12345abcde) is still referenced from table "constraining_table".

这似乎是一个很好的临时表候选者，但是我想知道为什么我不能按这个顺序删除，因为约束到最后才有效交易的？

Answer 1

使用 ON DELETE NO ACTION DEFERRABLE 而不是 ON DELETE RESTRICT DEFERRABLE。使用 RESTRICT 而不是 NO ACTION 强制约束不可延迟，无论您是否应用 DEFERRABLE 修饰符。

这是在 manual page for CREATE TABLE 的细则中:

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

显然，上述警告包括RESTRICT。

紧跟在这句话之后的是 NO ACTION 和 RESTRICT 的定义:

NO ACTION

Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

RESTRICT

Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

如您所见，NO ACTION 的行为与 RESTRICT 相同，除了 NO ACTION 是可延迟的。这就是我推荐它的原因 - 我认为这正是您所要求的。