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

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

Что такое составной первичный ключ

Первичный ключ (primary key) - это атрибут или набор атрибутов, который однозначно идентифицирует каждую строку таблицы и не допускает повторов и пустых значений (NULL). Когда такой набор состоит из двух и более столбцов, ключ называют составным (composite key), или сложным.

Формально требование к первичному ключу одно: на множестве строк должна выполняться функциональная зависимость, при которой значение ключа однозначно определяет всю строку. Если обозначить ключ как $K$, а полный набор атрибутов отношения как $R$, то условие записывается так:

$$K \rightarrow R$$

Для составного ключа левая часть - это не один атрибут, а их сочетание $\{A_1, A_2, \dots, A_n\}$. Уникальность гарантирует именно комбинация: по отдельности каждый из этих столбцов может повторяться сколько угодно раз.

Рассчитать для своей задачи

Чем отличается от простого и суррогатного

Чтобы не путаться, разведём три понятия:

• Простой первичный ключ - один столбец, например id или номер_паспорта. Большинство таблиц обходятся им.
• Составной первичный ключ - два и более столбца, уникальных только в сочетании. Естественный кандидат там, где сама предметная область задаёт пару «сущность А плюс сущность Б».
• Суррогатный ключ - искусственный столбец без смысла в предметной области (обычно автоинкрементное число или UUID), добавленный специально как идентификатор.

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

Где составной ключ необходим: связь многие-ко-многим

Самое типичное место для составного ключа - связующая (junction) таблица для отношения «многие-ко-многим». Один студент записан на много курсов, на один курс записано много студентов. Прямой связи между таблицами students и courses нет - её разрывает третья таблица enrollments, где каждая строка фиксирует один факт записи.

Уникальным здесь должно быть именно сочетание «студент + курс»: один и тот же студент не может записаться на один и тот же курс дважды. Поэтому первичным ключом становится пара (student_id, course_id).

Рассчитать для своей задачи

Похожая схема возникает в любых таблицах-связках: «товар в заказе» с ключом (order_id, product_id), «роль пользователя» с ключом (user_id, role_id), «тег у статьи» с ключом (article_id, tag_id). Связующая таблица - это и есть способ выразить отношение «многие-ко-многим»; для сравнения с более простым случаем посмотрите разбор про связь один-ко-многим в базе данных.

Как объявить в SQL

В стандартном SQL составной ключ задаётся ограничением PRIMARY KEY на уровне таблицы (не рядом со столбцом - там можно указать только однополевой ключ):

CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    grade      INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

Ключевые моменты записи:

• Перечисление столбцов в PRIMARY KEY (...) задаёт порядок их следования в составном индексе. Порядок влияет на то, какие запросы этот индекс ускорит (см. ниже).
• Все столбцы ключа автоматически получают ограничение NOT NULL - первичный ключ не допускает NULL ни в одной части.
• Внешние ключи FOREIGN KEY на родительские таблицы добавляют ссылочную целостность: нельзя записать несуществующего студента или курс.

Порядок столбцов и индекс

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

Если ключ объявлен как (student_id, course_id), индекс ускорит поиск «все курсы студента X» (фильтр по student_id), но не ускорит «все студенты курса Y» (фильтр только по course_id). Если второй сценарий частый, ставят дополнительный индекс по (course_id, student_id) или меняют порядок столбцов в ключе под основной паттерн запросов.

Составной ключ и нормализация

Составные ключи тесно связаны с теорией нормализации. Вторая нормальная форма (2НФ) прямо говорит про них: каждый неключевой атрибут должен зависеть от всего составного ключа, а не от его части. Если в таблице enrollments появится столбец course_title (название курса), он будет зависеть только от course_id - части ключа. Это нарушение 2НФ: название курса нужно вынести в таблицу courses.

Понимание частичной зависимости - главный навык при работе с составными ключами. Атрибут, зависящий лишь от куска ключа, всегда сигнализирует, что таблица «сшила» вместе две разные сущности и её пора разбивать. Подробнее логика приведения таблиц к нормальным формам разобрана в материале про нормализацию баз данных и нормальные формы.

Частые ошибки

• Добавлять суррогатный id в связующую таблицу и забывать про уникальность пары. Суррогатный ключ сам по себе не запрещает дубль «студент + курс». Если уж берёте суррогатный ключ, обязательно навешивайте UNIQUE (student_id, course_id) - иначе появятся повторные записи.
• Класть смысловой атрибут в ключ. Если столбец может измениться (например, дата записи), он плохой кандидат в часть первичного ключа: изменение ключа каскадно ломает все внешние ссылки. Ключ должен быть стабильным.
• Игнорировать порядок столбцов. Поставили (course_id, student_id), а фильтруете чаще по студенту - индекс не работает, запросы тормозят.
• Допускать NULL в части ключа. Невозможно: первичный ключ запрещает NULL во всех своих столбцах. Если столбец иногда пустой, он не может входить в первичный ключ - нужен другой дизайн.
• Путать составной ключ с составным значением. Ключ из двух столбцов - это не один столбец со склеенной строкой "123-456". Склейка ломает индексацию, типизацию и внешние ключи.

FAQ

Можно ли ссылаться внешним ключом на составной первичный ключ? Да, но внешний ключ тоже должен быть составным и включать все столбцы родительского ключа в том же порядке. Это одна из причин, по которой на «тяжёлые» таблицы со множеством входящих ссылок чаще ставят суррогатный ключ - он даёт компактный однополевой внешний ключ.

Сколько столбцов может быть в составном ключе? Стандарт SQL ограничений по смыслу не накладывает, но конкретные СУБД имеют лимиты (часто 16–32 столбца) и предел на суммарную длину индекса. На практике ключи из более чем 3–4 столбцов почти всегда говорят о недонормализованной таблице.

Чем составной первичный ключ отличается от составного уникального ограничения? Первичный ключ один на таблицу, он запрещает NULL и обычно служит целью для внешних ссылок. Ограничение UNIQUE тоже гарантирует уникальность сочетания столбцов, но допускает NULL и может присутствовать в нескольких экземплярах. Часто их комбинируют: суррогатный первичный ключ плюс UNIQUE на естественную пару.

Коротко

Составной первичный ключ - это первичный ключ из двух и более столбцов, уникальный только в сочетании, тогда как по отдельности каждый столбец повторяется. Главное место его применения - связующие таблицы для отношения «многие-ко-многим», где уникальна именно пара идентификаторов. В SQL он объявляется ограничением PRIMARY KEY (col1, col2) на уровне таблицы; порядок столбцов задаёт составной индекс и влияет на производительность запросов. Альтернатива - суррогатный ключ: компактнее для внешних ссылок, но прячет естественную уникальность, которую тогда защищают через UNIQUE. По правилам 2НФ каждый неключевой атрибут должен зависеть от всего составного ключа целиком, иначе таблицу нужно разбивать.