Deleting with self-referential foreign key in Postgres

Question

With a table with a self-referential foreign key:

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);

I wish to delete a branch by recursively traversing the tree as I may not use 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;

Is it safe to do it with a common table expression in Postgres, or do I have to worry about the order in which the records are deleted? Will Postgres delete all rows as a single set, or one by one?

If the answer is dependent on version, from which version is the deletion safe?

Note: in non-trivial cases, you really need an index supporting `FOREIGN KEY (parent_id) ` — wildplasser, May 30 '18 at 13:49

score 6 · Accepted Answer · 2018-05-30T13:01:51.570

No, you don't have to worry about the order in the select.

Foreign keys (unlike unique constraints) are evaluated per statement, not per row. And a common table expression is still a single statement, even if you have multiple SELECTs and DELETEs in it.

So if all constraints are still valid when the statement finishes, everything is OK.

You can easily see this with the following simple test:

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);

So the following obviously works, even if the IDs are specified in the "wrong" order:

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

The following also works - showing that a CTE is still a single statement:

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;

Nice. In fact, OP's tree `INSERT` statement can also forward reference nodes further down the list, e.g. `(6, 5)` could be the first node in the list. — StuartLC, May 30 '18 at 12:55
Great! Good news, and great explanation. Thanks for clarifying it! — ANisus, May 30 '18 at 13:08

Deleting with self-referential foreign key in Postgres

1 Answers1