Удаление с саморегулирующимся внешним ключом в Postgres

С таблицей с самореферентным внешним ключом:

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 все строки как единый набор или одну за другой?

Если ответ зависит от версии, удаление из какой версии безопасно?

Примечание: в нетривиальных случаях действительно нужен индекс, поддерживающий FOREIGN KEY (parent_id) .

— 30.05.2018 15:49

sql postgresql common-table-expression recursive-query

30.05.2018 14:35

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

1 185

Перейти к ответу Данный вопрос помечен как решенный

Ответы 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);

Таким образом, очевидно, работает следующее, даже если идентификаторы указаны в «неправильном» порядке:

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;

Хороший. Фактически, оператор INSERT дерева OP может также пересылать ссылочные узлы дальше по списку, например (6, 5) может быть первым узлом в списке.

— 30.05.2018 14:55

Большой! Хорошие новости и отличное объяснение. Спасибо за разъяснение!

— 30.05.2018 15:08

30.05.2018 14:43