Referential integrity: внешние ключи и ON DELETE/UPDATE

Нормализованная OLTP-схема — это много таблиц, связанных между собой. Заказ ссылается на покупателя, позиция заказа — на заказ и на товар. Эти связи нужно не просто нарисовать на схеме, а защитить: база не должна допускать заказ, который ссылается на несуществующего покупателя. Механизм такой защиты — referential integrity (ссылочная целостность), а инструмент — foreign key constraint (ограничение внешнего ключа).

В этом уроке мы разберём, что такое referential integrity, как работает FK-constraint, какие действия можно назначить на ON DELETE и ON UPDATE, и что СУБД физически делает для поддержания целостности.

Проблема: «осиротевшие» строки

Возьмём две нормализованные таблицы:

CUSTOMERS(customer_id PK, name)
ORDERS(order_id PK, customer_id, total)

Столбец ORDERS.customer_id — это foreign key: он содержит значение, которое должно ссылаться на существующую строку в CUSTOMERS. Связь «у каждого заказа есть покупатель».

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

Сценарий 1 — вставка ссылки в пустоту. Кто-то вставляет заказ с customer_id = 999, а покупателя 999 не существует:

INSERT INTO orders (order_id, customer_id, total) VALUES (5001, 999, 1200);
-- покупателя 999 нет — заказ ссылается в никуда

Сценарий 2 — удаление родителя. Покупатель 42 есть, у него три заказа. Кто-то удаляет покупателя:

DELETE FROM customers WHERE customer_id = 42;
-- три заказа покупателя 42 остались, но ссылаются на удалённую строку

В обоих случаях появляются осиротевшие строки (orphan rows) — строки с внешним ключом, который ни на что не указывает. Заказ есть, покупателя нет. Это нарушение целостности: запрос «покажи имя покупателя по заказу» вернёт пустоту или упадёт, отчёты разъедутся, бизнес-логика сломается. И главное — без формального ограничения СУБД допускает такое состояние молча.

Foreign key constraint: формальная защита

Foreign key constraint — это объявленное в схеме правило: значение столбца(ов) одной таблицы обязано существовать как значение ключа в другой таблице (или быть NULL, если столбец необязателен). Объявляется при создании таблицы:

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total       NUMERIC NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

С этим ограничением оба опасных сценария становятся невозможны. Вставка заказа с customer_id = 999 будет отклонена СУБД — нет такой строки в customers. Удаление покупателя, у которого есть заказы, по умолчанию тоже будет отклонено — иначе появятся сироты. СУБД сама стережёт связь при каждой операции.

Введём терминологию. Таблица, на которую ссылаются (CUSTOMERS), — родительская (parent, referenced). Таблица, которая ссылается (ORDERS), — дочерняя (child, referencing). FK-constraint гарантирует referential integrity: каждое непустое значение внешнего ключа дочерней таблицы соответствует существующей строке родительской.

Действия ON DELETE и ON UPDATE

Простое «отклонять удаление родителя» — не всегда то, что нужно. Иногда при удалении покупателя его заказы тоже надо удалить; иногда — обнулить ссылку. Что СУБД делает с дочерними строками при удалении или изменении ключа родителя, задаётся действиями ON DELETE и ON UPDATE.

ON DELETE срабатывает, когда удаляют строку родителя, у которой есть дети. ON UPDATE — когда меняют значение ключа родителя, на который ссылаются дети. Возможные действия:

Разберём ключевые на примерах.

RESTRICT / NO ACTION — запретить. По умолчанию удалить покупателя с заказами нельзя:

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE RESTRICT
-- DELETE FROM customers WHERE customer_id = 42  ->  ОТКЛОНЕНО, у него есть заказы

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

CASCADE — удалить вместе. Подходит, когда дочерние строки не имеют смысла без родителя. Классика — ORDERS и ORDER_ITEMS: позиция заказа без заказа бессмысленна.

-- в таблице order_items:
FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE
-- DELETE FROM orders WHERE order_id = 5001  ->  заодно удалятся все его order_items

CASCADE мощный, но требует осторожности: удаление одной строки родителя может каскадно снести множество строк, а если каскады выстроены в цепочку — целое поддерево данных.

SET NULL — разорвать связь. Подходит, когда дочерняя строка может существовать и без родителя. Пример — EMPLOYEES.manager_id: уволили руководителя, но его подчинённые остаются, просто временно без менеджера.

-- в таблице employees, self-referencing FK:
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
    ON DELETE SET NULL
-- удалили руководителя  ->  у подчинённых manager_id становится NULL

Выбор действия — это часть модели данных, а не настройка «по умолчанию пойдёт». Он отражает бизнес-смысл связи: «позиция не существует без заказа» -> CASCADE; «заказ нельзя терять при удалении покупателя» -> RESTRICT; «подчинённый переживает увольнение менеджера» -> SET NULL.

Self-referencing FK и порядок операций

Внешний ключ может ссылаться на ту же таблицу, в которой он объявлен, — это self-referencing (рекурсивная) связь. Классический пример — иерархия сотрудников: у каждого сотрудника есть руководитель, который тоже сотрудник.

CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       TEXT NOT NULL,
    manager_id INT REFERENCES employees(emp_id)   -- ссылка на ту же таблицу
);

manager_id ссылается на emp_id той же таблицы. Это позволяет хранить дерево произвольной глубины в одной таблице: у рядового сотрудника manager_id указывает на его начальника, а у самого верхнего руководителя manager_id равен NULL — он ни на кого не ссылается. Заметьте, почему manager_id обязан допускать NULL: иначе вершину иерархии негде было бы разместить, а первого сотрудника вообще нельзя было бы вставить — он не может ссылаться на ещё не существующего руководителя.

Self-referencing FK обнажает важное общее правило — порядок операций при наличии внешних ключей. FK-constraint требует, чтобы родитель существовал в момент вставки дочерней строки. Значит:

• При вставке строки сначала должен существовать родитель, потом — ребёнок. Нельзя вставить заказ раньше покупателя; нельзя вставить сотрудника раньше его руководителя.
• При удалении — наоборот: сначала дети (или их обработка через CASCADE), потом родитель.

Для обычных связей это означает порядок между таблицами: наполняем customers, затем orders, затем order_items. Для self-referencing связи порядок возникает внутри одной таблицы: руководителей вставляем раньше подчинённых. Этот порядок — часть модели: проектируя схему со связями, вы одновременно задаёте, в какой последовательности данные можно загружать.

Что СУБД физически делает для целостности

FK-constraint не бесплатен — за ним стоит реальная работа СУБД. Понимать её важно, потому что это влияет на производительность OLTP.

При INSERT/UPDATE дочерней строки СУБД проверяет существование родителя: берёт значение внешнего ключа и ищет строку с таким ключом в родительской таблице. Это поиск — и он должен быть быстрым, иначе каждая вставка заказа тормозит. Поиск по первичному ключу родителя быстр: PK всегда проиндексирован.

При DELETE/UPDATE строки родителя СУБД должна найти все дочерние строки, которые на неё ссылаются, — чтобы либо отклонить операцию (RESTRICT), либо обработать детей (CASCADE/SET NULL). А вот это — поиск в дочерней таблице по столбцу внешнего ключа. И здесь критичный нюанс:

-- ОБЯЗАТЕЛЬНО для производительности: индекс на столбец FK
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Кроме того, FK-проверки взаимодействуют с конкурентностью: чтобы родитель не исчез ровно между проверкой и завершением дочерней транзакции, СУБД берёт на родительскую строку лёгкую блокировку. В высоконагруженном OLTP это нужно учитывать — но это нормальная цена за гарантию целостности.

Попробуй сам

Дана схема блог-платформы:

USERS(user_id PK, name)
POSTS(post_id PK, user_id, title)        -- автор поста
COMMENTS(comment_id PK, post_id, user_id, text)   -- комментарий: к посту, от пользователя

Выполните на бумаге:

1. Выпишите все внешние ключи этой схемы. Для каждого укажите дочернюю и родительскую таблицу.
2. Для FK COMMENTS.post_id выберите действие ON DELETE и обоснуйте: что должно произойти с комментариями при удалении поста?
3. Для FK POSTS.user_id и COMMENTS.user_id подумайте: что разумнее при удалении пользователя — CASCADE, RESTRICT или SET NULL? Учтите, что удалять автора, у которого есть посты, скорее всего нельзя просто так.
4. Напишите CREATE INDEX для всех столбцов внешних ключей и объясните на примере удаления пользователя, почему без этих индексов операция была бы медленной.