Ответ.

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

История вопроса: В SQL стандартные UNIQUE CONSTRAINT не гарантируют уникальность, когда один из столбцов NULL — спецификация считает такие значения взаимоисключающимися.

Проблема: Для сложных ключей с NULL стандартные ограничения ведут к появлению дубликатов. Это особенно критично, когда целостность данных — необходимость: при импорте, миграции, массовых обновлениях.

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

Пример кода:

ALTER TABLE my_table
ADD computed_uniqueness AS (
   ISNULL(email, '') + '#' + ISNULL(phone, '')
);   
CREATE UNIQUE INDEX idx_my_table_computed_uniqueness ON my_table(computed_uniqueness);

Или (для PostgreSQL, где есть выражения в индексе и NULL рассматриваются как различающиеся):

CREATE UNIQUE INDEX idx_unique_email_phone ON my_table ((COALESCE(email, '##')),(COALESCE(phone, '##')));

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

• Явное управление правилом уникальности для комбинаций с NULL.
• Возможность автоматизации и поддержки на уровне БД.
• Поддержка даже для сложных и изменяемых схем.

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

Можно ли использовать обычный UNIQUE индекс для обеспечения уникальности, если часть столбцов допускает NULL?

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

В чем разница между использованием уникального индекса на выражении и использованием BEFORE INSERT триггера для проверки уникальности?

Уникальный индекс проще для поддержки и быстрее в работе, но не всегда может реализовать сложные бизнес-правила (например, исключения или кастомные комбинации). Триггер гибче, но медленнее и сложнее для сопровождения.

Пример:

CREATE OR REPLACE FUNCTION check_custom_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM my_table
    WHERE COALESCE(NEW.email, '##') = COALESCE(email, '##')
      AND COALESCE(NEW.phone, '##') = COALESCE(phone, '##')
  ) THEN
    RAISE EXCEPTION 'Duplicate found';
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_custom_unique
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_custom_unique();

Можно ли использовать DISTINCT в SELECT, чтобы на уровне приложения решать задачу уникальности?

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

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

• Попытки использовать UNIQUE CONSTRAINT там, где ключом служит комбинация с возможными NULL.
• Проверка уникальности только на уровне приложения (в обход БД).
• Избыточная сложность триггеров без явных индексов — потеря производительности.

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

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

Компания внедряет уникальность email+телефон через UNIQUE(email, phone). В базе появляются дубликаты по email, если phone=NULL.

Плюсы:

• Простота внедрения.

Минусы:

• Потеря целостности, дубликаты.
• Неявные ошибки, трудно отслеживаемые в аналитике.

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

Используется вычисляемый столбец (COALESCE(email, '##') + '#' + COALESCE(phone, '')), на него ставится уникальный индекс.

Плюсы:

• База исключает дубли, любые операции (UPDATE, INSERT) сразу проигрываются.

Минусы:

• Переиндексация может занимать время при больших объёмах.
• Влияет на размеры индексов.