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

Несколько строк могут содержать NULL, потому что NULL означает неизвестное или отсутствующее значение, а не обычное значение, равное другому NULL. Ограничение UNIQUE обычно запрещает совпадение известных значений, но не считает два NULL одинаковыми. Однако точное поведение зависит от СУБД: например, PostgreSQL и Oracle обычно допускают несколько NULL, а SQL Server в типичном уникальном индексе допускает только один.

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

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

Ограничение UNIQUE предназначено для контроля повторов значений, но NULL не является обычным значением домена. Из-за этого правила уникальности для NULL пришлось отделять от правил для известных значений, а конкретные СУБД получили различия в реализации.

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

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

Ошибка особенно опасна при проектировании ключей. Nullable-столбец с UNIQUE не является полноценным первичным ключом: первичный ключ должен однозначно идентифицировать строку и не допускает NULL.

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

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

В составном ограничении UNIQUE действует та же идея: строки с полностью совпадающими известными компонентами запрещаются, но наличие NULL может позволить повтор. Например, пары «код клиента — NULL» могут повторяться в зависимости от правил конкретной СУБД.

Минимальный пример показывает типичную, но не универсальную семантику:

CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    external_id INTEGER UNIQUE
);

INSERT INTO account VALUES (1, NULL);
INSERT INTO account VALUES (2, NULL);
INSERT INTO account VALUES (3, 10);
INSERT INTO account VALUES (4, 10); -- ошибка уникальности

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

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

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

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

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

Вариант с запретом NULL упрощает контроль, но не подходит бизнес-процессу: черновик нельзя создать до появления номера. Вариант с обычным UNIQUE сохраняет удобство, однако требует принять несколько отсутствующих номеров как допустимое состояние.

Выбранное решение — оставить номер nullable, обеспечить уникальность известных номеров и отдельно проверять, что после перехода заказа в состояние «проведён» номер заполнен. Это разделяет два правила: обязательность зависит от состояния заказа, а уникальность — от наличия известного номера. В результате черновики не блокируются, а проведённые документы получают проверяемые уникальные номера.

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

1. Является ли nullable-столбец с UNIQUE кандидатным ключом?

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

2. Одинаково ли UNIQUE обрабатывает NULL во всех СУБД?

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

3. Что произойдёт, если заменить NULL пустой строкой или специальным числом?

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