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

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

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

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

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

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

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

Проверка в приложении ненадёжна: два параллельных запроса могут одновременно не найти профиль и оба попытаться его создать. Без ограничения на уровне базы данных возникнет нарушение правила «не более одного профиля».

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

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

CREATE TABLE employee (
    employee_id INTEGER PRIMARY KEY
);

CREATE TABLE work_profile (
    profile_id INTEGER PRIMARY KEY,
    employee_id INTEGER NOT NULL UNIQUE,
    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
);

Здесь внешний ключ отвечает за наличие сотрудника, а UNIQUE(employee_id) — за кардинальность связи. NOT NULL означает, что каждый профиль обязан быть связан с сотрудником; без него могли бы появиться профили без владельца.

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

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

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

В кадровой системе сначала создали таблицу профилей с внешним ключом на сотрудника без уникальности. Через некоторое время у части сотрудников появились дубликаты профилей из-за повторной отправки запроса и параллельной обработки событий.

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

Выбранное решение также включало NOT NULL и миграцию уже существующих данных. Сначала дубликаты объединили по бизнес-правилам, затем добавили уникальное ограничение. В результате правило стало enforced на уровне схемы, независимо от числа экземпляров приложения.

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

Достаточно ли внешнего ключа без UNIQUE для связи «один-к-одному»?

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

Что изменится, если внешний ключ сделать nullable?

У сотрудника по-прежнему не будет более одного профиля с конкретным ненулевым идентификатором, но могут появиться строки профиля без сотрудника. В обычной SQL-семантике несколько значений NULL обычно допускаются уникальным ограничением, потому что NULL не считается равным NULL. Если профиль обязан иметь сотрудника, столбец следует объявить NOT NULL.

Гарантирует ли уникальный внешний ключ, что каждый сотрудник имеет профиль?

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