PostgreSQL | Каскадное удаление

В этой публикации я хочу разобрать вопрос «каскадного удаления данных» из таблиц на простых примерах. Тема важная и её понимание избавляет от написания бесполезных проверок или функций.

Ситуация № 1 — Удаляя оригинал данных в одной таблице, мы хотим чтобы удалились все зависимые от этого оригинала данные в других таблицах PostgreSQL

Первичные и внешние ключи в таблицах созданы для того, чтобы поддерживать целостность данных. СУБД PostgreSQL уделяет этому приоритетное значение.

Когда мы создаём ограничение внешнего ключа, то мы не просто связываем две таблицы вместе. В этом случае мы говорим СУБД, что хотим получить дополнительный функционал на 4 базовые операции CRUD (Create, Read, Update, Delete). Только при установлении связей между таблицами, можно говорить о качественном манипулировании с данными.

Операция удаления данных на первый взгляд может показаться простой — выбираем таблицу, выбираем в ней ненужные записи и удаляем. Кажется всё просто. Но!

Но если где-то в других таблицах установлены связи многие ко многим, тогда при переплетении идентификаторов, для полной очистки всей базы данных, нужно удалить и сами зависимые данные.

Давайте говорить о вопросе на конкретных примерах.

Пример данных, таблиц и их связей в PostgreSQL

Есть 4 таблицы:

— Тестирование каскадного удаления

— Документация: https://postgrespro.ru/docs/postgresql/14/ddl-constraints

— Таблица № 1

— DROP TABLE IF EXISTS test.t1;

CREATE TABLE test.t1 (

id integer PRIMARY KEY NOT NULL,

b text NOT NULL

);

INSERT INTO test.t1 VALUES (1, ‘Один‘);

INSERT INTO test.t1 VALUES (2, ‘Два‘);

INSERT INTO test.t1 VALUES (3, ‘Три‘);

— Таблица № 2

— DROP TABLE IF EXISTS test.t2;

CREATE TABLE test.t2 (

id integer PRIMARY KEY NOT NULL,

b text NOT NULL

);

INSERT INTO test.t2 VALUES (1, ‘Олег‘);

INSERT INTO test.t2 VALUES (2, ‘Дима‘);

INSERT INTO test.t2 VALUES (3, ‘Тимур‘);

— Таблица № 3

— DROP TABLE IF EXISTS test.t3;

CREATE TABLE test.t3 (

id integer PRIMARY KEY NOT NULL,

b text NOT NULL

);

INSERT INTO test.t3 VALUES (1, ‘Оксана‘);

INSERT INTO test.t3 VALUES (2, ‘Дарья‘);

INSERT INTO test.t3 VALUES (3, ‘Татьяна‘);

— Связующая таблица (ПОДЧИНЁННАЯ) с ЧАСТИЧНЫМ УДАЛЕНИЕМ всех связанных данных из ГЛАВНЫХ таблиц

— DROP TABLE IF EXISTS test.t4;

CREATE TABLE test.t4 (

id integer PRIMARY KEY NOT NULL,

b1 integer NOT NULL, — ссылается на ‘Один’ или ‘Два’ или ‘Три’

b2 integer NOT NULL, — ссылается на ‘Олег’ или ‘Дима’ или ‘Тимур’

b3 integer NOT NULL, — ссылается на ‘Оксана’ или ‘Дарья’ или ‘Татьяна’

CONSTRAINT my_b1_fkey FOREIGN KEY (b1) — b1 делаем внешним ключём

REFERENCES test.t1 (id) — который ссылается на столбец id из таблицы t1

ON DELETE CASCADE — и в случае удаления записи в t1, удалится запись с id в t4

);

INSERT INTO test.t4 VALUES (1, 1, 1, 1);

INSERT INTO test.t4 VALUES (2, 2, 2, 2);

INSERT INTO test.t4 VALUES (3, 3, 3, 3);

INSERT INTO test.t4 VALUES (4, 1, 2, 3);

INSERT INTO test.t4 VALUES (5, 2, 3, 1);

INSERT INTO test.t4 VALUES (6, 3, 1, 2);

Визуально эти таблицы сейчас выглядят таким образом:

Схема из 4 таблиц для примера каскадного удаления в PostgreSQL

Для простоты понимания «каскадного удаления«, мы устанавливаем всего одно ограничение целостности для столбца b1 из таблицы t4:

CONSTRAINT my_b1_fkey FOREIGN KEY (b1)
REFERENCES test.t1 (id)
ON DELETE CASCADE

Мы записали это ограничение, как ограничение на таблицу.

Суть его работы русским языком такая:

Свяжи таблицы t1 и t4 таким образом, чтобы t1 была ГЛАВНАЯ, а t4 — ПОДЧИНЁННАЯ.
Установи ограничение на возможный идентификатор для столбца b1 из t4, чтобы его значение могло быть только из вариантов значений id в t1.
Если в b1 из t4 попытаться вставить то чего нет в id из t1, то будет ошибка.
Установи для события DELETE из t1 каскадное удаление в подчинённой таблице t4
Если удалить запись из t1, то в t4 будут удалены все записи, которые связаны с id в t1.

Делаем удаление записи из ГЛАВНОЙ таблицы t1

DELETE FROM test.t1 WHERE id = 1 RETURNING *;

Скрин из pgAdmin 4:

Удалили из t1 запись с ID равным 1 - PostgreSQL — Удалили из t1 запись с ID равным 1 — PostgreSQL

Удаление произошло успешно. Благодаря оператору RETURNING * нам вернулась удалённая запись. И вот с этого момента для нашей «Ситуации № 1» будут интересны такие моменты:

Что осталось в t4?
Что осталось в t1?
А что будет, если мы удалим запись напрямую из t4?
А что будет, если мы удалим запись в таблице t2?

Что осталось в t4?

Теперь смотрим на содержимое из ПОДЧИНЁННОЙ таблицы t4. Как оно изменилось?

SELECT * FROM test.t4;

Результат SELECT

При удалении записи из ГЛАВНОЙ таблицы, также были каскадно удалены все записи в ПОДЧИНЁННЫХ таблицах в PostgreSQL

Результат JOIN

Обновлённое содержимое таблицы t4 без двух записей после каскадного удаления в PostgreSQL

Мы видим, что из таблицы t4 пропали 2 записи. То есть после удаления одной записи из t1, удалились те записи из t4 в которых в столбце b1 присутствовало значение 1.

В общем исчезло всё, что содержало строковое ‘Один‘.

По итогу, сделав одно удаление, мы сделали три удаления. СУБД приняла на себя решение по поиску подчинённых таблиц и поиску соответствующих записей, для их каскадного удаления.

Если бы мы не прописывали эти правила, то нам самим бы пришлось контролировать удаление ненужной информации во всех местах, где только можно. В этом примере связаны всего 2 таблицы. А представьте ситуацию из связки хотя бы 4 таблиц.

Что осталось в t1?

Делаем запрос:

SELECT * FROM test.t1;

Скрин таблицы t1

Из t1 была удалена запись с ID равным 1 в PostgreSQL

В t1 осталось 2 записи. Слово ‘Один‘ исчезло.

А что будет, если мы удалим запись напрямую из t4?

Что случится, если мы напрямую удалим запись в подчинённой таблице, а не в главной? Для этого мы попробуем удалить запись из связующей таблицы, в которой находятся только идентификаторы.

DELETE FROM test.t4 WHERE id = 2 RETURNING *;

Скриншот:

Удалили одну запись из подчинённой таблицы в PostgreSQL

На этот раз никакого каскадного удаления не произойдёт, потому что мы удалили запись из ПОДЧИНЁННОЙ таблицы, а не из ГЛАВНОЙ. В этом заключается основная суть работы каскадного удаления. Если смотреть на него со стороны разработки, то по сути каскадное удаление имеет направление. В одну сторону удаляет всё на своём пути, а в другую удаляет только что-то ОДНО.

Напрямую удалили запись с ID 2 из t4 в PostgreSQL

JOIN после прямого удаления записи из t4 в PostgreSQL

После прямого удаления (второго) в t1 останется всё тоже самое, что и было после первого удаления.

Содержимое t1 не изменилось так как каскадное удаление в PostgreSQL работает в одну сторону

Подвох тут только в восприятии. Мы вроде бы настраивали это ограничение целостности у ПОДЧИНЁННОЙ таблицы со связями, а не у ГЛАВНОЙ, а получается что каскадно удаляться будет только с ГЛАВНОЙ. В нашем мышлении нужно думать о том месте, где хранится ОРИГИНАЛ данных, а не о том, где он связывается.

В t1 по-прежнему будет ‘Два‘, ‘Три‘.

Как запомнить, куда прописывать каскадное удаление? Получается что синтаксис «каскадного удаления» нужно прописывать в той таблице, где присутствуют связи ключей (внешнего и первичного). Выглядит так, что таблица со связями t4 подписана на события таблицы t1 и при каждом её изменении отслеживает состояние в связанных идентификаторах и столбцах. И если есть событие «Удаления» и пересечение по связям, то t4 удаляет всё, что было связано с t1 по нужному id.

А что будет, если мы удалим запись в таблице t2?

Напомню, что у нас таблицы t2 и t4 никак не связаны т. к. нет никакого ограничения целостности. Мы просто вручную искусственно вставили данные, которые похожи на реальные.

Столбец b2 из t4 никак не связан со столбцом id из t2.

Сейчас состав t2 такой:

Таблица 2 - 3 мужских имени — Таблица 2 — 3 мужских имени

Она такая же, как была в самом начале.

Удаляем запись с id равным 3

DELETE FROM test.t2 WHERE id = 3 RETURNING *;

Скриншот:

Удалили запись 3 из несвязанной таблицы t2 в PostgreSQL

Смотрим на состав t4:

Состав записей в t4 не изменился в PostgreSQL

Состав таблицы t4 никак не изменился

А теперь делаем соединение JOIN, чтобы увидеть изменения.

JOIN вернул только 1 запись из соединения после удаления записи из t2 в PostgreSQL

А вот JOIN на этот раз вернулся нам не полноценный — количество записей в нём не соответствует количеству записей в t4. Почему так?

Обычный JOIN (он же INNER JOIN). Он ищет все ПЕРЕСЕЧЕНИЯ между таблицами в случае, если выражения в ON даёт ИСТИНУ.

Если ON даёт ЛОЖЬ, тогда такой вариант выбрасывается. Давайте по шагам посмотрим как меняется соединение, ведь у нас в запросе аж 3 операции JOIN.

Первый JOIN:

Тут всё ок. Все три записи из t4 попали в выдачу

Второй JOIN:

И вот уже на втором уровне соединения мы получаем всего один результат.

Ситуация могла бы выглядеть более наглядной, если бы мы применили LEFT JOIN:

LEFT JOIN отобразил нам дырки в данных из-за отсутствия связей для каскадного удаления в PostgreSQL

Только с «Левым Соединением» (LEFT JOIN) наглядно видно появление null‘ов. Это подтверждает тот факт, что таблица t2 не связана с t4, а значит не поддерживает целостность данных. Визуально это похоже на «дырки» в таблицах. Это значит, что при удалении записи из t2 не происходит каскадного удаления в «не подчинённой» таблице t4.

Мы специально рассмотрели такой пример из четырёх таблиц, чтобы наглядно понять проблемность ситуации, когда нарушена целостность данных и когда не описаны дополнительные действия для связанных логикой ключей между таблицами.

При проектировании базы данных всегда нужно учитывать поведение связей при ОБНОВЛЕНИИ или УДАЛЕНИИ! Но делать это надо очень осторожно и вдумчиво. Лучше предварительно тестировать логику на суррогатных таблицах, как мы в этой статье, прежде чем внедрять это на боевую базу.

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Операция создания таблицы «CREATE TABLE» — https://postgrespro.ru/docs/postgresql/14/sql-createtable

Раздел «5.4. Ограничения» — https://postgrespro.ru/docs/postgresql/14/ddl-constraints — Именованное ограничение «CONSTRAINT«