Первичные и внешние ключи: разбираем на живом примере

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

Исходная схема: покупатели и заказы

Представим две таблицы. В customers хранятся покупатели, в orders — их заказы. Имя покупателя не подходит на роль идентификатора: оно может повторяться и меняться. Поэтому заведём отдельное поле id.

Сначала создайте таблицы в чистой базе данных PostgreSQL 16:

CREATE TABLE customers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    order_total numeric(10, 2) NOT NULL CHECK (order_total >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT orders_customer_id_fkey
        FOREIGN KEY (customer_id)
        REFERENCES customers (id)
);

У каждой таблицы есть собственный первичный ключ: customers.id и orders.id. А orders.customer_id — внешний ключ, который ссылается на customers.id. Название ограничения здесь задано явно: так его проще найти в сообщениях об ошибках и при изменении схемы.

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

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

Добавляем данные и проверяем связь

Вставим покупателя и заказ. Поля id не указываем: PostgreSQL назначит значения автоматически. Чтобы получить идентификатор покупателя и сразу использовать его для заказа, можно выполнить запрос с общим табличным выражением:

WITH new_customer AS (
    INSERT INTO customers (full_name)
    VALUES ('Анна Смирнова')
    RETURNING id
)
INSERT INTO orders (customer_id, order_total)
SELECT id, 2490.00
FROM new_customer;

Проверьте результат запросом:

SELECT orders.id, customers.full_name, orders.order_total
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Запрос покажет заказ вместе с именем покупателя. Обратите внимание: поле customer_id само по себе не гарантирует наличие покупателя — это обеспечивает именно внешний ключ.

Попробуйте добавить заказ с несуществующим покупателем:

INSERT INTO orders (customer_id, order_total)
VALUES (999999999, 100.00);

Если такого идентификатора нет в customers, PostgreSQL отклонит вставку с ошибкой нарушения внешнего ключа. Это не сбой схемы, а ожидаемая защита: в базе не появится заказ, который нельзя связать с покупателем.

Что произойдёт при удалении покупателя

По умолчанию PostgreSQL не даст удалить строку из customers, если на неё ссылаются заказы. Это поведение NO ACTION. Удаление родительской записи не должно незаметно оставлять дочерние строки без владельца.

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

Чтобы выбрать SET NULL, поле customer_id должно допускать NULL; в исходной схеме стоит NOT NULL, поэтому одного изменения внешнего ключа недостаточно. CASCADE тоже не универсальное удобство: одно удаление может убрать много связанных строк. Для заказов это обычно нежелательно, если магазин обязан хранить историю операций.

Какой ключ выбрать и где ошибаются

Для этой схемы есть два распространённых подхода. Можно использовать искусственный числовой идентификатор или назначить первичным ключом уже существующее поле — например, адрес электронной почты. Второй вариант выглядит проще, пока значение не меняется.

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

Типичные ошибки

• Одинаковые названия полей, но разные типы. Поле ссылки должно быть совместимо с типом ключа, на который оно указывает. Здесь оба столбца имеют тип bigint.
• Внешний ключ не создан. Одинаковые значения в двух таблицах сами по себе не образуют связь и не мешают записать ошибочный идентификатор.
• Удаление настроено слишком широко. CASCADE может удалить дочерние данные автоматически. Перед его выбором проверьте всю цепочку связей и последствия для хранения истории.
• Ожидание, что внешний ключ ускорит любой запрос. Ограничение проверяет целостность, но не заменяет продуманную индексацию. PostgreSQL создаёт индекс для первичного ключа, но автоматически создавать индекс на стороне ссылающейся таблицы для каждого внешнего ключа не обязан.

Что проверить в своём проекте дальше

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

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

Сноска. «Родительская» таблица — та, на которую ссылаются; «дочерняя» содержит внешний ключ. В примере это соответственно customers и orders.