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

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

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

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

Из-за этого сравнение NULL с любым значением, включая другой NULL, не дает обычного TRUE. Ограничения уникальности проектировались с учетом такой семантики: отсутствие значения обычно не должно считаться конфликтом с другим отсутствующим значением.

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

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

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

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

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

Минимальный пример для требования «адрес обязателен и уникален»:

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    email VARCHAR(320) NOT NULL,
    UNIQUE (email)
);

INSERT INTO customer VALUES (1, 'a@example.com');
-- Вторая строка с тем же адресом будет отклонена

Если адрес необязателен, UNIQUE (email) обычно означает: все непустые адреса уникальны, NULL может быть несколько. Это отличается от требования «значение должно быть уникальным всегда», потому что для последнего сначала нужен NOT NULL.

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

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

Не следует подменять NULL искусственным значением вроде '<нет адреса>': это смешивает отсутствие данных с реальным текстовым значением, усложняет проверки и может создать конфликт при появлении такого адреса в будущем.

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

В системе клиентов поле external_id появляется только после связывания клиента с внешним сервисом. У нескольких еще не связанных клиентов это поле отсутствует, но после заполнения идентификатор должен быть уникальным.

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

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

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

Одинаковы ли NULL, пустая строка и ноль с точки зрения UNIQUE?

Нет. Пустая строка и ноль являются обычными значениями и обычно сравниваются как значения, поэтому повторная пустая строка или повторный ноль нарушают уникальность. NULL имеет специальную семантику отсутствующего или неизвестного значения. Исключение важно помнить для Oracle: для строковых типов пустая строка трактуется как NULL, поэтому перенос схемы между СУБД может изменить результат.
Гарантирует ли nullable-столбец с UNIQUE отсутствие дубликатов во всех смыслах?

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

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