代码之家 › 专栏 › 技术社区 › ANisus

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

recursive-query common-table-expression postgresql sql

ANisus · 技术社区 · 6 年前

对于具有自引用外键的表:

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是将所有行作为一个集合删除,还是逐个删除?

如果答案取决于版本,那么从哪个版本删除是安全的?

1 回复 | 直到 6 年前

a_horse_with_no_name 6 年前

不,您不必担心select中的顺序。

计算外键(与唯一约束不同) 按报表 ,不是每行。一个通用的表表达式仍然是 单一的 语句,即使其中有多个选择和删除。

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

通过以下简单的测试,您可以很容易地看到这一点:

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;

推荐文章

Sadeq Dousti · 相当于“嵌套删除”的执行性能SQL查询

2 年前

roca · 试图让CTE在PostgreSQL中工作

2 年前

joeCarpenter · 使用物理表和CTE创建多个联接

6 年前

gdanton · 用于查找所有依赖项的SQL Server递归CTE

7 年前

user2850751 · SQL-获取层次结构的单个分支

7 年前

Amir Farashah · 我需要在两张桌子上使用CTE

7 年前

JustPeachy · SQL使用DateRange和DATETIME列查找每天中午处于范围内的状态

7 年前

Logically_Sound · SQL CTE-由于子对象的子对象,递归速度较慢….:

7 年前

Nick77 · 递归CTE SQL获取所有级别

7 年前

John Frazer · (Postgres)SQL:如何提供所有缺失的对?

7 年前