Ответ.

История вопроса

Контроль уникальности бизнес-ключей (не только по ID, но и по «естественным» полям) давно стоит в корпоративных приложениях. Часто такие ключи бывают сложными (несколько столбцов) и допускают значения NULL. Стандартные средства SQL — UNIQUE CONSTRAINT или уникальные индексы — имеют ограничения: согласно стандарту SQL, несколько строк, у которых хотя бы один из компонентов UNIQUE-ключа равен NULL, считаются уникальными и не нарушают ограничение.

Проблема

Бизнес-логика может требовать, чтобы набор колонок (включая допускающие NULL) в совокупности был уникальным, а поведение стандарта SQL (NULL ≠ NULL) ломает этот сценарий. Например, у нас есть таблица с парой столбцов (passport_number, passport_series), и хотя бы один из них может быть NULL, но если значения совпадают, мы обязаны запрещать дубль.

Решение

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

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

На уровне логики приложения часто приходится дублировать проверку, чтобы не пропустить дубли на этапе вставки.

Ключевые особенности:

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

Вопросы с подвохом.

Можно ли быть уверенным, что уникальный индекс гарантирует невозможность вставки дубликатов, если среди столбцов есть nullable-колонки?

Нет, нельзя. В SQL поведение с NULL особенное: строки с одинаковым набором значений, где хотя бы один из них — NULL, индекс считает разными и разрешает их хранение одновременно.

Могут ли разные СУБД реализовывать NULL-уникальность по-разному?

Да, между ними есть отличия. К примеру, в Oracle уникальный индекс допускает несколько строк с NULL, а в MS SQL — одни. PostgreSQL может создавать частичный индекс через выражения.

Возможно ли обойти проблему только на уровне DDL и без триггеров?

В некоторых СУБД — да, через выражения, например COALESCE. Но не всегда, и если нужно сложное поведение (NULL должен считаться за дубликат), придётся использовать логические проверки или триггеры.

Типовые ошибки и анти-паттерны

Полагаться на стандартный UNIQUE, когда в столбцах допускается NULL
Не проверять дубликаты на уровне бизнес-логики
Создавать триггеры без учёта производительности

Пример из жизни

Негативный кейс

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

Плюсы:

Простой DDL

Минусы:

Неочевидные дубликаты, неконсистентные данные, ошибки отчетов

Позитивный кейс

В PostgreSQL бизнес-ключ с полями (номер паспорта, серия) реализовали через индекс по COALESCE, а дополнительно добавили проверку на стороне приложения.