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

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

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

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

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

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

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

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

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

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

CREATE TABLE customer (
    id INTEGER PRIMARY KEY,
    email VARCHAR(200) UNIQUE
);

CREATE TABLE order_header (
    id INTEGER PRIMARY KEY,
    customer_email VARCHAR(200),
    FOREIGN KEY (customer_email) REFERENCES customer(email)
);

Здесь ссылка допустима, потому что customer.email уникален. Ссылка только на customer без уникального ограничения для email не задавала бы однозначного родителя.

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

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

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

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

Первый вариант — оставить ссылку на неуникальный адрес. Он прост, но не гарантирует однозначность клиента и создаёт проблемы при изменении адреса. Второй вариант — удалить дубликаты и объявить адрес уникальным; это корректно только после подтверждения бизнес-правила, что один адрес действительно может принадлежать лишь одному клиенту.

Выбранным решением стало добавление суррогатного идентификатора клиента и ссылка заказа на него. Уникальность email сохранили отдельным ограничением только после очистки данных и согласования правила. Это сделало ссылку однозначной, а изменение контактных данных — независимым от идентичности клиента.

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

1. Достаточно ли уникального индекса без ограничения UNIQUE?

Для переносимого решения следует использовать именно декларативное ограничение уникальности или первичный ключ. Один лишь индекс — это объект физической реализации, и его семантика для внешних ключей зависит от конкретной СУБД; стандартный SQL не следует подменять предположением о поведении конкретного продукта.

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

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

3. Можно ли ссылаться на столбцы, допускающие NULL, если они объявлены UNIQUE?

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