PostgreSQL: выбор ON DELETE RESTRICT, CASCADE или SET NULL без потери связанных записей

Рассмотрите политику удаления как решение о сохранении данных

Каждый внешний ключ содержит неявный ответ на бизнес-вопрос: что должно произойти со строками, указывающими на него, когда ссылкаемая строка исчезает? ON DELETE RESTRICT отвечает, что зависимые записи должны выжить, и родитель не может быть удален, пока эти зависимые записи не будут обработаны явно. ON DELETE CASCADE отвечает, что зависимые записи являются неотъемлемыми частями родителя и должны исчезать вместе с ним. ON DELETE SET NULL отвечает, что ссылка является необязательной, и зависимая строка может продолжать существовать без нее. Это не взаимозаменяемые варианты синтаксиса; они кодируют правила сохранения данных, которые влияют на то, какие данные останутся доступными для запросов после удаления.

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

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

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);
CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text);
CREATE TABLE order_items (
  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)
);

Изучите поведение по умолчанию NO ACTION перед его изменением

Если вы пишете внешний ключ без предложения ON DELETE, PostgreSQL применяет ON DELETE NO ACTION. Документация объясняет, что это означает, что удаление в ссылается таблице разрешено, но ограничение внешнего ключа все равно должно быть удовлетворено, поэтому операция обычно завершается ошибкой. Ключевое отличие от RESTRICT заключается во времени проверки и возможности отложения. NO ACTION проверяет ограничение в конце инструкции или в конце транзакции, если ограничение можно отложить, давая другим командам шанс исправить ситуацию до срабатывания проверки. RESTRICT проверяет немедленно и не может быть отложен.

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

-- These two are NOT equivalent:
-- product_no integer REFERENCES products  -- defaults to NO ACTION, deferrable
-- product_no integer REFERENCES products ON DELETE RESTRICT  -- immediate, non-deferrable

Выбирайте RESTRICT, когда ссылается строки должны выжить

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

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

-- Attempting this with RESTRICT on product_no will raise an error:
DELETE FROM products WHERE product_no = 7;
-- ERROR: update or delete on table "products" violates
--        foreign key constraint on table "order_items"

Выбирайте CASCADE только для зависимых строк жизненного цикла

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

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

-- Deleting an order cascades to its line items:
DELETE FROM orders WHERE order_id = 42;
-- All rows in order_items with order_id = 42 are removed automatically.

Выбирайте SET NULL только для необязательных ссылок

ON DELETE SET NULL сохраняет ссылочную строку, но устанавливает столбец внешнего ключа в NULL. Это уместно, когда отношение представляет собой необязательную информацию. В документации приводится пример ссылки на менеджера продукта: если запись менеджера продукта удаляется, установка значения NULL для поля менеджера продукта может быть полезным.

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

-- Column-list form for composite foreign keys:
CREATE TABLE posts (
  tenant_id integer REFERENCES tenants ON DELETE CASCADE,
  post_id integer NOT NULL,
  author_id integer,
  PRIMARY KEY (tenant_id, post_id),
  FOREIGN KEY (tenant_id, author_id) REFERENCES users
    ON DELETE SET NULL (author_id)
);
-- Without the column list, tenant_id would also be set to NULL,
-- violating the primary key.

Сравните три действия на одной небольшой схеме

Рассмотрите схему products, orders и order_items из документации. Таблица order_items имеет два внешних ключа с разными действиями: product_no использует RESTRICT, а order_id использует CASCADE. Если вы попытаетесь выполнить DELETE FROM products WHERE product_no = 7, и этот продукт присутствует в любой строке order_items, инструкция немедленно завершится ошибкой нарушения внешнего ключа. Ни одна строка не будет удалена ни из одной таблицы.

Если вы попытаетесь выполнить DELETE FROM orders WHERE order_id = 42, действие CASCADE удалит каждую строку order_items, ссылающуюся на этот заказ, и удаление пройдет успешно. Одна и та же схема демонстрирует как защитное, так и автоматическое поведение в зависимости от того, какой родитель выбран целью.

SET NULL применился бы, если бы, например, в таблице orders был необязательный столбец assigned_shipper, ссылающийся на таблицу shipper. Удаление перевозчика оставило бы заказ нетронутым с assigned_shipper, установленным в NULL, сохраняя историю заказов, но удаляя теперь недействительную ссылку.

-- Outcome table for the example schema:
-- DELETE FROM products WHERE product_no = 7
--   -> ERROR (RESTRICT blocks it, dependents survive)
-- DELETE FROM orders WHERE order_id = 42
--   -> SUCCESS (CASCADE removes matching order_items)
-- DELETE FROM shippers WHERE shipper_id = 3
--   -> SUCCESS (SET NULL clears orders.assigned_shipper)

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

Перед изменением ограничения в производственной среде проверьте текущее действие с помощью запроса к pg_constraint или чтения схемы с помощью \d в psql. Определите, сколько зависимых строк существует для родительских строк, которые вы планируете удалить. Документация предупреждает, что запись DELETE FROM products без предложения WHERE удаляет все строки, поэтому всегда ограничивайте область ваших тестовых удалений.

Выполните предполагаемый DELETE внутри транзакции, которую вы откатываете. Это позволит вам наблюдать, вызывает ли RESTRICT ошибку, удаляет ли CASCADE ожидаемое количество дочерних элементов или оставляет ли SET NULL строки с ссылками NULL, и все это без фиксации изменений. Только после подтверждения поведения следует выполнять ALTER TABLE для изменения действия ограничения в производственной среде.

BEGIN;
DELETE FROM orders WHERE order_id = 42;
SELECT count(*) FROM order_items WHERE order_id = 42;
-- Expect 0 if CASCADE is correct.
ROLLBACK;
-- No data was permanently removed.

Знайте, когда политика недостаточна

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

В документации также отмечается, что ON UPDATE имеет связанное, но не идентичное поведение, и что списки столбцов не могут быть указаны для SET NULL и SET DEFAULT при ON UPDATE. Политика, работающая для удалений, может не переноситься напрямую на обновления. Наконец, неограниченная инструкция DELETE в сочетании с CASCADE может удалить большие объемы данных в одной транзакции, поэтому меры предосторожности на уровне приложения и явные предложения WHERE остаются необходимыми независимо от действия ограничения.

-- This single statement could cascade through many tables:
DELETE FROM products;  -- no WHERE clause, caveat programmer
-- If CASCADE is set on multiple levels, this can be destructive.