В системе аккаунт удаляется мягко, но адрес электронной почты должен снова стать доступным для регистрации....

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TIMESTAMP NULL,
    UNIQUE (email)
);

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

Обычное ограничение UNIQUE (email) проверяет все строки, включая мягко удалённые. В PostgreSQL его следует заменить на частичный уникальный индекс, который действует только для записей с deleted_at IS NULL.

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

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

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

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

При обычном UNIQUE (email) удалённая запись продолжает занимать адрес. Попытка создать новый активный аккаунт с тем же адресом завершится ошибкой уникальности, хотя с точки зрения бизнеса старый аккаунт уже не активен.

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

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

В PostgreSQL ограничение можно заменить частичным уникальным индексом:

CREATE UNIQUE INDEX accounts_active_email_uq
    ON accounts (email)
    WHERE deleted_at IS NULL;

Индекс содержит только активные строки. Для них база гарантирует уникальность email, а строки с ненулевым deleted_at в проверке не участвуют.

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

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

Нужно также определить правила нормализации адреса. Например, если бизнес считает адреса безразличными к регистру, простое текстовое ограничение может пропустить варианты вроде User@example.com и user@example.com. В таком случае нормализацию следует формализовать и применять одинаково до проверки уникальности.

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

Сервис регистрации хранил все аккаунты в одной таблице и после удаления выставлял deleted_at. Команда сначала предложила проверять отсутствие активного аккаунта запросом SELECT, а затем выполнять INSERT. Вариант был простым, но небезопасным: параллельные регистрации могли пройти проверку одновременно.

Второй вариант — полностью удалить старую строку. Он освобождал адрес, но разрушал историю, усложнял аудит и мог нарушить ссылки на аккаунт. Команда выбрала частичный уникальный индекс: история сохранилась, правило выполнялось атомарно, а активный адрес оставался единственным.

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

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

1. Достаточно ли проверять уникальность в приложении перед вставкой?

Нет. Проверка вида SELECT ... WHERE email = ... и последующая вставка — два отдельных действия. Между ними другой запрос может создать такую же запись. Надёжная гарантия требует уникального ограничения или индекса в базе; прикладная проверка может использоваться только для улучшения сообщения об ошибке.

2. Что произойдёт при восстановлении мягко удалённого аккаунта?

Операция восстановления должна снова установить deleted_at = NULL и пройти ту же проверку уникальности. Если адрес уже занял другой активный аккаунт, база отклонит восстановление. Система должна явно определить бизнес-правило: предложить сменить адрес, объединить записи или запретить восстановление.

3. Почему условие частичного индекса должно точно соответствовать понятию активной записи?

Если индекс фильтрует только по deleted_at IS NULL, но бизнес считает аккаунт неактивным также при статусе blocked, заблокированные записи всё ещё будут занимать адрес. Если же индекс исключит слишком много строк, появится возможность создать несколько активных с точки зрения бизнеса аккаунтов. Поэтому условие индекса должно быть согласовано с моделью состояний и изменяться вместе с соответствующими требованиями.