Выберите политику удаления внешнего ключа в PostgreSQL, не теряя неправильные строки

Проследите изолированный пример

Этот иллюстративный скрипт использует три разные дочерние таблицы и три разные родительские строки, чтобы сохранить независимость политик. Он не прикрепляет три конфликтующие политики к одному отношению. Запускайте такие демонстрации только в сеансе, который можно удалить; транзакция завершается ROLLBACK и не утверждает, что проверяет вашу производственную схему.

Сравните три действия

При RESTRICT попытка удалить родительскую запись, на которую еще ссылается дочерняя, отвергается. При CASCADE удаление родительской записи удаляет ссылающиеся дочерние строки. При SET NULL дочерние записи остаются, но их столбцы ссылок становятся NULL. Другие ограничения все еще применяются и могут предотвратить операцию.

SET NULL подходит только тогда, когда затронутые столбцы и логика приложения принимают NULL. Ограничение NOT NULL может заставить удаление завершиться ошибкой. NO ACTION - это другая политика, но ее не следует описывать как идентичную RESTRICT во всех ситуациях: отложная проверка ограничений может сделать различие важным.

Поймите ожидаемый результат и отвергнутый случай

Ожидаемое количество строк при каскаде равно нулю, потому что дочерняя строка, связанная с родителем 2, удаляется. Строка с id 30 остается в demo_null со значением parent_id, равным NULL. Скрипт не удаляет родителя 1, поэтому его ограничивающая дочерняя строка остается. Эти результаты следуют из заявленных ограничений и примерных данных; они иллюстративны, а не измерения.

Попытка удалить родителя 1 до отката была бы отвергнута, потому что demo_restrict все еще ссылается на него. Это намеренно завершающееся ошибкой утверждение опущено из скрипта: ошибка может прервать транзакцию, и последующий SQL требует соответствующей обработки отката.

Проверьте область применения перед реальным удалением

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

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

BEGIN;
CREATE TEMP TABLE demo_parent (id integer PRIMARY KEY);
CREATE TEMP TABLE demo_restrict (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE RESTRICT
);
CREATE TEMP TABLE demo_cascade (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE CASCADE
);
CREATE TEMP TABLE demo_null (
    id integer PRIMARY KEY,
    parent_id integer REFERENCES demo_parent(id) ON DELETE SET NULL
);
INSERT INTO demo_parent VALUES (1), (2), (3);
INSERT INTO demo_restrict VALUES (10, 1);
INSERT INTO demo_cascade VALUES (20, 2);
INSERT INTO demo_null VALUES (30, 3);
DELETE FROM demo_parent WHERE id IN (2, 3);
SELECT count(*) AS remaining_cascade_rows FROM demo_cascade;
SELECT id, parent_id FROM demo_null ORDER BY id;
ROLLBACK;

Проверьте производительность, не утверждая гарантированную скорость

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

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

Отделяйте реляционные действия от очистки приложения

Ограничения базы данных действуют на объявленные отношения базы данных. Они не гарантируют очистку файлов, удаленных служб или событий, поддерживаемых вне транзакции. Учитывайте эти побочные эффекты явно.

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

Решите, может ли дочерняя запись существовать самостоятельно

Дочерняя строка, которая не имеет смысла без родительской, может подходить для каскадного удаления. Историческая запись, которая должна остаться, может требовать ограничения удаления или сохранения строки с необязательным отношением. Начните с этого бизнес-решения, а не с выбора действия, которое заставляет завершающийся DELETE завершаться успешно.

Внешний ключ защищает отношение, объявленное в его ограничении. Он не сам по себе обеспечивает политику сохранения, получает разрешение на удаление персональных данных или сохраняет внешние файлы, связанные с записью.

Изучите ограничение, которое фактически применяется

Найдите ссылаемую таблицу, ссылающиеся столбцы и настроенное действие ON DELETE в схеме базы данных или административном инструменте. Не выводите действие из имени столбца или из модели ORM, которая может не соответствовать развернутой базе данных.

CASCADE и SET NULL принадлежат после предложения REFERENCES в определении внешнего ключа. Команда вроде DELETE FROM parent WHERE id = 1 CASCADE не является синтаксисом DELETE PostgreSQL для выбора действия внешнего ключа.