Первичные и внешние ключи: связи между таблицами

Введение

Статья 393 представила ключи концептуально — в этой статье разберём их подробнее: разные подходы к выбору первичного ключа (естественный против суррогатного), и поведение внешних ключей при удалении/изменении связанных строк через ON DELETE/ON UPDATE.

Концепция

Первичный ключ может быть «естественным» (использующим реально значимый атрибут данных, например номер паспорта) или «суррогатным» (искусственно сгенерированным значением, не имеющим самостоятельного смысла, обычно автоинкрементное целое число или UUID) — суррогатные ключи на практике предпочтительны в большинстве случаев, поскольку защищены от изменения значения «естественного» атрибута в будущем (что потребовало бы каскадного изменения во всех связанных таблицах). Внешний ключ может определять поведение при удалении/изменении родительской строки — CASCADE (автоматически удалить/изменить и связанные дочерние строки), RESTRICT/NO ACTION (запретить удаление родителя, если есть связанные дочерние строки), SET NULL (обнулить ссылку в дочерних строках).

Пример кода: Первичные и внешние ключи

-- Суррогатный ключ (автоинкрементное целое число) — рекомендуемый подход для большинства случаев
CREATE TABLE products (
    id SERIAL PRIMARY KEY, -- SERIAL — автоинкремент в PostgreSQL; в других СУБД синтаксис отличается
    sku VARCHAR(50) UNIQUE NOT NULL, -- "естественный" атрибут, но НЕ используемый как сам первичный ключ
    name VARCHAR(200) NOT NULL
);

-- Поведение внешнего ключа при удалении родительской записи
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,   -- удаление заказа удаляет его позиции
    product_id INTEGER REFERENCES products(id) ON DELETE RESTRICT, -- товар нельзя удалить, если он есть в заказах
    quantity INTEGER NOT NULL CHECK (quantity > 0)
);

-- SET NULL — связь сохраняется как "неизвестная" вместо удаления дочерней записи
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees(id) ON DELETE SET NULL -- ссылка сама на себя!
    -- при удалении руководителя его подчинённые НЕ удаляются, просто manager_id становится NULL
);

Пояснения к коду

sku VARCHAR(50) UNIQUE NOT NULL рядом с суррогатным id SERIAL PRIMARY KEY показывает практичный компромисс — реально значимый, «естественный» идентификатор (артикул товара) сохраняется как отдельное уникальное ограничение (что всё ещё гарантирует отсутствие дублей и позволяет искать по нему), но именно искусственный, не имеющий самостоятельного бизнес-смысла id используется как первичный ключ и для связей с другими таблицами — это даёт гибкость изменить sku в будущем (например, при пересмотре системы артикулов) без необходимости менять связанные внешние ключи во всех таблицах, ссылающихся на товар. ON DELETE CASCADE против ON DELETE RESTRICT в одной и той же таблице order_items показывает осознанный, различный выбор поведения для двух разных связей — позиции заказа логически не имеют смысла без самого заказа (поэтому каскадное удаление оправдано), но товар не должен быть удалён, пока на него есть ссылки в истории заказов (поэтому ограничение, а не каскад).

Подводные камни

• Использование ON DELETE CASCADE без полного понимания цепочки всех связанных таблиц, которые могут быть затронуты — в сложной схеме с многоуровневыми связями (A ссылается на B, B ссылается на C, и так далее, всё с CASCADE) удаление одной записи на верхнем уровне может вызвать массовое, неожидаемо широкое каскадное удаление множества связанных записей по всей цепочке; перед применением CASCADE стоит явно продумать (и в идеале протестировать) полную цепочку последствий, а не применять его «по умолчанию» для всех внешних ключей без разбора.
• Выбор естественного ключа, который впоследствии оказывается не таким уникальным/стабильным, как изначально предполагалось (классический пример — номер социального страхования или номер паспорта, которые в реальности иногда дублируются из-за ошибок или переиздаются) — это типичная причина, по которой суррогатные ключи рекомендуются по умолчанию: они гарантированно уникальны и стабильны самой природой их генерации, не зависящей от внешних, потенциально несовершенных систем нумерации.
• Создание циклических ссылок между таблицами через внешние ключи без продуманной стратегии вставки/удаления — если таблица A ссылается на B, а B ссылается на A, порядок вставки/удаления записей становится нетривиальным (нельзя вставить запись A, ссылающуюся на ещё не существующую запись B, и наоборот) — для таких случаев нужна либо явная отложенная проверка ограничений (deferred constraints, поддерживаемая некоторыми СУБД), либо пересмотр самой структуры схемы, чтобы избежать прямого цикла.
• Отсутствие индекса на столбце внешнего ключа — хотя сам внешний ключ обеспечивает ссылочную целостность, многие СУБД не создают индекс на столбце внешнего ключа автоматически (в отличие от первичного ключа, для которого индекс создаётся автоматически почти всегда), и без явно созданного дополнительного индекса JOIN-запросы (статья 398) или проверка каскадных операций по этому столбцу могут быть значительно медленнее, чем ожидается, особенно на больших таблицах.