Краткий ответ

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

Например, ключом позиции будет пара заказ — номер позиции. Отдельный искусственный идентификатор позиции возможен, но сам по себе он не выражает локальную уникальность номера и зависимость позиции от заказа.

Исторический контекст

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

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

Постановка проблемы

Номер позиции 1 может встречаться в каждом заказе, но не должен повторяться дважды в одном заказе. Поэтому глобальная уникальность одного только номера позиции была бы неверной: она искусственно запретила бы одинаковые номера в разных заказах.

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

Подробное решение

Схема должна содержать три логических элемента:

идентификатор заказа входит в состав первичного ключа позиции;
номер позиции входит в тот же первичный ключ;
идентификатор заказа в позиции ссылается на первичный ключ заказа.

CREATE TABLE order_item (
    order_id INTEGER NOT NULL,
    line_no INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, line_no),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE
);

Первичный ключ запрещает две строки с одной и той же парой order_id, line_no, но разрешает номер 1 в разных заказах. Внешний ключ гарантирует существование заказа для каждой позиции.

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

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

Ситуация из практики

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

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

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

Что кандидаты часто упускают

1. Достаточно ли внешнего ключа на заказ, если у позиции есть искусственный первичный ключ?

Нет. Внешний ключ проверяет только наличие родительского заказа. Он не запрещает две позиции с одинаковым номером в одном заказе. Для этого нужна уникальность пары идентификатора заказа и локального номера — через составной первичный ключ либо отдельное ограничение UNIQUE.

2. Обязательно ли использовать каскадное удаление для зависимой сущности?

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

3. Почему составной внешний ключ из другой таблицы может быть менее удобен?

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