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

Обычное UNIQUE проверяет уникальность среди всех строк таблицы и не умеет учитывать условие активности. Для правила «уникально только при выполнении предиката» нужен механизм условной уникальности: например, частичный уникальный индекс в СУБД, которая его поддерживает, либо отдельное моделирование правила.

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

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

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

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

Пусть в таблице хранятся активные и архивные пользователи. Один и тот же email может принадлежать архивной записи и одновременно использоваться новым активным пользователем, но два активных пользователя с таким email должны быть запрещены.

Если объявить UNIQUE непосредственно на email, архивная запись тоже будет участвовать в проверке. Это приведёт к ложным конфликтам и заставит приложение самостоятельно удалять или изменять старые записи, что создаёт риск гонок между параллельными транзакциями.

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

В обычном UNIQUE нет фильтра: ограничение применяется ко всем строкам, для которых действует проверка уникальности. Оно не означает «значения уникальны среди строк, удовлетворяющих условию».

В СУБД с поддержкой частичных индексов правило можно выразить так:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(320) NOT NULL,
    active BOOLEAN NOT NULL
);

CREATE UNIQUE INDEX users_active_email_uq
    ON users (email)
    WHERE active;

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

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

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

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

Команда сначала создала UNIQUE на email. После внедрения архивации пользователи не могли зарегистрироваться с email, который встречался только в архивных данных. Удалять архивные строки было нельзя из-за аудита.

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

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

Почему проверка уникальности только в приложении ненадёжна?

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

Само по себе ограничение на пару email и active не подходит: оно запретит две активные записи с одним email, но также запретит две архивные записи с одинаковым email только при одинаковом значении active. Если сделать вспомогательный ключ, который содержит email только для активных строк, а для остальных имеет NULL, результат будет зависеть от правил обработки NULL в UNIQUE и от конкретной СУБД; такой приём менее очевиден и хуже переносится.
Чем частичный уникальный индекс отличается от обычного UNIQUE-ограничения?

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