在 Postgres 中使用自引用外键删除

Question

使用带有自引用外键的表：

CREATE TABLE tree (  
id INTEGER,
parent_id INTEGER,
PRIMARY KEY (id)  
);
ALTER TABLE tree 
ADD CONSTRAINT fk_tree
FOREIGN KEY (parent_id) 
REFERENCES tree(id);
INSERT INTO tree (id, parent_id)
VALUES (1, null),
(2, 1),
(3, 1),
(4, 2),
(5, null),
(6, 5);

我希望通过递归遍历树来删除分支，因为我可能不会使用ON DELETE CASCADE.

WITH RECURSIVE branch (id, parent_id) AS (
SELECT id, parent_id
FROM tree
WHERE id = 1 -- Delete branch with root id = 1
UNION ALL SELECT c.id, c.parent_id
FROM tree c -- child
JOIN branch p -- parent
ON c.parent_id = p.id
)
DELETE FROM tree t
USING branch b
WHERE t.id = b.id;

在 Postgres 中使用公用表表达式执行此操作是否安全，或者我是否必须担心删除记录的顺序？Postgres 会删除所有行作为单个集合还是逐个删除？

如果答案取决于版本，则从哪个版本删除是安全的？

Answer 1

不，您不必担心选择中的顺序。

外键(与唯一约束不同(是按语句计算的，而不是按行计算的。公共表表达式仍然是单个语句，即使其中有多个 SELECT 和 DELETE。

因此，如果语句结束时所有约束仍然有效，则一切正常。

---

您可以通过以下简单的测试轻松看到这一点：

CREATE TABLE fk_test
(
id          integer PRIMARY KEY,
parent_id   integer,
FOREIGN KEY (parent_id) REFERENCES fk_test (id)
);
INSERT INTO fk_test (id, parent_id) 
VALUES 
(1, null),
(2, 1),
(3, 2),
(4, 1);

因此，即使以"错误"的顺序指定了 ID，以下内容显然有效：

DELETE FROM fk_test
WHERE id IN (1,2,3,4);

以下内容也有效 - 表明 CTE 仍然是单个语句：

with c1 as (
delete from fk_test where id = 1
), c2 as (
delete from fk_test where id = 2
), c3 as (
delete from fk_test where id = 3
)
delete from fk_test where id = 4;