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

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

Для какого отношения следует использовать каждое действие при удалении?

Действие при удалении задаётся для внешнего ключа в ссылающейся таблице. SET NULL очищает ссылку, когда исчезает указанная в ней строка; CASCADE удаляет ссылающуюся строку. В документации PostgreSQL SET NULL рекомендуется для необязательных отношений, а CASCADE — для зависимых компонентов. Ограничения

В этом примере подборка объединяет статьи, но статья может существовать и без подборки. Раздел принадлежит одной статье и не имеет самостоятельной роли. Это явные проектные решения для данного примера:

Внешний ключ Выбранное действие Предполагаемый результат
demo_articles.collection_id ссылается на demo_collections.collection_id ON DELETE SET NULL При удалении подборки статьи сохраняются, а ссылка на подборку у них очищается.
demo_sections.article_id ссылается на demo_articles.article_id ON DELETE CASCADE При удалении статьи удаляются её разделы.

Результат зависит от того, с какой строки начинается удаление. Удаление подборки изменяет collection_id статьи, но не удаляет саму статью. Поэтому ON DELETE CASCADE внешнего ключа раздела не срабатывает: статья не удаляется. При удалении самой статьи каскадное удаление срабатывает. Это следует из задокументированных определений двух действий. CREATE TABLE

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

Создайте полный пример на Mac

Вам понадобятся работающий сервер PostgreSQL, существующая база данных, к которой можно подключиться, и доступная в Terminal программа psql. В приведённых ниже командах используется существующая локальная база данных postgres; при необходимости укажите имя своей существующей базы. Установка и настройка сервера выходят за рамки этого руководства.

Сохраните следующий блок как текстовый файл setup.sql в рабочей папке. Все три таблицы временные: PostgreSQL удаляет временные таблицы после завершения сеанса. В этом примере временными являются также все таблицы, на которые есть ссылки: PostgreSQL не разрешает использовать внешние ключи между временными и постоянными таблицами. CREATE TABLE

CREATE TEMP TABLE demo_collections (
    collection_id integer PRIMARY KEY,
    title text NOT NULL
);

CREATE TEMP TABLE demo_articles (
    article_id integer PRIMARY KEY,
    collection_id integer,
    title text NOT NULL,
    CONSTRAINT demo_articles_collection_fk
        FOREIGN KEY (collection_id)
        REFERENCES demo_collections (collection_id)
        ON DELETE SET NULL
);

CREATE TEMP TABLE demo_sections (
    section_id integer PRIMARY KEY,
    article_id integer NOT NULL,
    heading text NOT NULL,
    CONSTRAINT demo_sections_article_fk
        FOREIGN KEY (article_id)
        REFERENCES demo_articles (article_id)
        ON DELETE CASCADE
);

CREATE INDEX demo_articles_collection_idx
    ON demo_articles (collection_id);
CREATE INDEX demo_sections_article_idx
    ON demo_sections (article_id);

INSERT INTO demo_collections (collection_id, title) VALUES
    (1, 'Layout Notes'),
    (2, 'Color Notes');

INSERT INTO demo_articles (article_id, collection_id, title) VALUES
    (101, 1, 'Grid Spacing'),
    (102, 2, 'Color Pairings');

INSERT INTO demo_sections (section_id, article_id, heading) VALUES
    (1001, 101, 'Overview'),
    (1002, 101, 'Examples'),
    (1003, 102, 'Overview');

Обратите внимание: collection_id допускает значение NULL, а article_id в таблице разделов является обязательным. Действие SET NULL должно удовлетворять и остальным ограничениям таблицы. Созданные явно индексы охватывают ссылающиеся столбцы; PostgreSQL не создаёт такие индексы автоматически при объявлении внешнего ключа. Ограничения

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

Сохраните следующий блок в файл verify.sql рядом с setup.sql. Это полный скрипт psql: \ir подключает файл настройки по пути относительно скрипта проверки. Запустите оба файла через этот скрипт, чтобы временные таблицы оставались в одном сеансе. Параметр -f выполняет файл, а ON_ERROR_STOP прекращает обработку скрипта при ошибке. psql

BEGIN;
\ir setup.sql

DELETE FROM demo_collections
WHERE collection_id = 1
RETURNING collection_id;

SELECT a.article_id,
       a.title,
       a.collection_id,
       c.title AS collection_title,
       count(s.section_id) AS section_count
FROM demo_articles AS a
LEFT JOIN demo_collections AS c
    ON c.collection_id = a.collection_id
LEFT JOIN demo_sections AS s
    ON s.article_id = a.article_id
GROUP BY a.article_id, a.title, a.collection_id, c.title
ORDER BY a.article_id;

DELETE FROM demo_articles
WHERE article_id = 101
RETURNING article_id;

SELECT section_id, article_id, heading
FROM demo_sections
ORDER BY section_id;

ROLLBACK;

В Terminal перейдите в папку с файлами и выполните команду:

psql -X -v ON_ERROR_STOP=1 -d postgres -f verify.sql

Ожидаемые результаты первого запроса следуют из тестовых строк:

Статья ID подборки после удаления подборки Название подборки Количество разделов
101 NULL NULL 2
102 2 Color Notes 1

Статья 101 сохраняется вместе с двумя разделами. После второго удаления запрос разделов должен вернуть только раздел 1003, принадлежащий статье 102. Эти две проверки позволяют различить сохранение статьи и удаление статьи вместе с зависимыми от неё строками.

В запросе проверки намеренно используется LEFT JOIN. PostgreSQL сохраняет строку из левой таблицы, для которой нет совпадения, и подставляет NULL вместо значений из отсутствующих строк правой таблицы. Если соединить таблицу подборок оператором INNER JOIN, статья 101 исчезнет из результатов запроса после того, как её ссылка станет NULL, хотя сама статья сохранится. Табличные выражения

Предложения RETURNING показывают строки, удалённые из целевых таблиц. Они не предоставляют сводный список всех разделов, удалённых каскадно; этот результат проверяется отдельным запросом к таблице разделов. DELETE

Последний ROLLBACK отменяет изменения, внесённые для демонстрации. PostgreSQL также рассматривает отдельный оператор как транзакцию, поэтому явная транзакция полезна для объединения настройки, удаления, проверки и очистки. Транзакции

Выполните те же проверки с node-postgres

Для версии на JavaScript оставьте setup.sql без изменений. В этом примере используется Node.js 24; установите документированный пакет pg в рабочую папку: node-postgres

npm install pg

Сохраните следующую полную программу как verify.mjs рядом с setup.sql. Она читает те же синтетические тестовые данные, проверяет, что удаление подборки сохраняет обе статьи и все три раздела, а затем проверяет, что удаление статьи удаляет только соответствующие ей разделы.

На протяжении всей транзакции программа использует один подключённый клиент, как того требует node-postgres. Значения запросов передаются через документированный интерфейс массива параметров с использованием $1. Транзакции, Запросы

import pg from 'pg';
import { readFile } from 'node:fs/promises';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client();
const setup = await readFile(
  new URL('./setup.sql', import.meta.url),
  'utf8'
);

await client.connect();
try {
  await client.query('BEGIN');
  await client.query(setup);

  const removed = await client.query(
    'DELETE FROM demo_collections WHERE collection_id = $1 RETURNING collection_id',
    [1]
  );
  assert.deepEqual(removed.rows, [{ collection_id: 1 }]);

  const articles = await client.query(
    'SELECT article_id, collection_id FROM demo_articles ORDER BY article_id'
  );
  assert.deepEqual(articles.rows, [
    { article_id: 101, collection_id: null },
    { article_id: 102, collection_id: 2 }
  ]);

  const before = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(
    before.rows.map(row => row.section_id),
    [1001, 1002, 1003]
  );

  await client.query(
    'DELETE FROM demo_articles WHERE article_id = $1',
    [101]
  );
  const after = await client.query(
    'SELECT section_id FROM demo_sections ORDER BY section_id'
  );
  assert.deepEqual(after.rows, [{ section_id: 1003 }]);

  console.log('Expected SET NULL and CASCADE outcomes verified.');
  await client.query('ROLLBACK');
} catch (error) {
  try {
    await client.query('ROLLBACK');
  } catch (rollbackError) {
    console.error('Rollback failed:', rollbackError.message);
  }
  throw error;
} finally {
  await client.end();
}

Запустите программу для той же существующей локальной базы данных:

PGDATABASE=postgres node verify.mjs

new Client() считывает переменные окружения для подключения к PostgreSQL. Сохраните все параметры подключения, которые уже нужны вашему локальному серверу; изменение одного лишь имени базы данных не переключит вас на другой сервер. Подключение

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

Синтаксис JavaScript был проверен при подготовке. В исследовательской среде не было исполняемого файла PostgreSQL, поэтому примеры SQL сверялись с документацией, а не выполнялись; в таблицах выше указаны ожидаемые результаты.

Что меняется при составных внешних ключах?

В основном примере используются внешние ключи с одним столбцом. Составной внешний ключ требует ещё одного решения: при удалении очищать все ссылающиеся столбцы или только необязательный? В PostgreSQL 15 для действий ON DELETE SET во внешних ключах появились списки столбцов; в более ранних версиях действие затрагивало все столбцы внешнего ключа. Примечания к выпуску PostgreSQL 15

Например, составная ссылка на статью может включать обязательный идентификатор издания и необязательный идентификатор связанной статьи. В PostgreSQL 15 или более поздней версии ON DELETE SET NULL (related_article_id) позволяет сохранить идентификатор издания, очистив ссылку на необязательную статью. Без списка столбцов действие SET NULL затрагивает оба столбца. Этот синтаксис является расширением PostgreSQL и доступен для ON DELETE, но не для ON UPDATE. CREATE TABLE

Для частичного присваивания NULL также требуется совместимое правило сопоставления. Значение по умолчанию MATCH SIMPLE допускает составной внешний ключ с NULL; MATCH FULL запрещает сочетание значений NULL и не NULL в ссылающихся столбцах. Поэтому сохранение обязательного идентификатора издания при обнулении только идентификатора связанной статьи несовместимо с MATCH FULL. Ограничения

В PostgreSQL 14 используйте схему с отдельной необязательной ссылкой на один столбец, как в основном примере, если она точно отражает отношение. Если составную связь необходимо сохранить, перед удалением указанной строки вручную очистите необязательную ссылку в рамках одной транзакции. Это последовательность действий, управляемая приложением, а не неподдерживаемый синтаксис со списком столбцов.

В PostgreSQL 18 есть и другое ограничение, указанное в документации: временные внешние ключи с PERIOD не поддерживают CASCADE или SET NULL. Если связь не требует охвата временных интервалов, для этих действий при удалении подойдут обычные внешние ключи, показанные здесь. CREATE TABLE

Как диагностировать неожиданный результат?

Используйте путь удаления как последовательность шагов для диагностики:

  1. Проверьте ссылающуюся таблицу. В сеансе, содержащем таблицы, изучите \d demo_articles и \d demo_sections. Проверьте фактические действия внешнего ключа, а не делайте выводы по именам столбцов. В документации PostgreSQL указано, что \d позволяет просматривать сведения о таблицах и имена ограничений. Изменение таблиц
  2. Проверьте допустимость NULL и остальные ограничения. Если удаление подборки завершается ошибкой, убедитесь, что очищаемый столбец допускает NULL и что результирующая строка удовлетворяет остальным ограничениям. SET NULL их не обходит. Ограничения
  3. Проверьте все относящиеся к делу внешние ключи. У дополнительной связи может быть другое действие. Если действие не указано, по умолчанию используется NO ACTION, которое может запретить удаление, пока остаются ссылки. CREATE TABLE
  4. Прежде чем разбираться с результатами соединения, запросите базовую таблицу. Если статья всё ещё в ней есть, проверьте соединения и фильтры, которые могут скрывать её новую ссылку со значением NULL. Табличные выражения
  5. Выполняйте операторы транзакции на одном клиенте. В приложении с пулом подключений используйте один клиент для всей транзакции. В документации node-postgres прямо рекомендуется не использовать pool.query() для операторов транзакции. Транзакции

Для этой модели статей критерии проверки конкретны: удаление подборки 1 оставляет статью 101 и её разделы нетронутыми, а после удаления статьи 101 остаётся только раздел статьи 102. При адаптации схемы выполняйте обе проверки вместе, чтобы намеренное поведение при сохранении и удалении оставалось очевидным.

Источники