Уникальный индекс при мягком удалении: решаем проблему дубликатов
Введение
Пользователь обращается в поддержку: при каждом входе он видит разную историю заказов. Иногда свои, иногда чужие, иногда пустой список. Причина — три учётных записи с одним e-mail. Система аутентификации выбирает одну из них наугад, и результаты непредсказуемы.
Классическое решение — уникальный индекс на столбец email. База данных отклоняет повторную вставку, проблема решена. Но затем появляется новое требование: пользователь должен иметь возможность удалить аккаунт и позже зарегистрироваться снова. Вы внедряете мягкое удаление — добавляете столбец deleted_at и помечаете запись датой удаления вместо физического стирания строки. В Laravel это делается трейтом SoftDeletes и вызовом softDeletes() в миграции. Мы уже разбирали этот механизм подробно в статье «Мягкое удаление (Soft Delete) в Laravel: полное руководство».
Проблема возникает при повторной регистрации. Пользователь вводит свой старый e-mail, но уникальный индекс блокирует вставку: мягко удалённая запись всё ещё хранит это значение. Стандартными средствами фреймворка эту коллизию не разрешить — нужно спускаться на уровень базы данных.
В этой статье мы разберём, как спроектировать ограничение уникальности, учитывающее только активные записи и игнорирующее мягко удалённые. Рассмотрим два механизма: частичные индексы в PostgreSQL и генерируемые столбцы в MySQL. Попутно затронем производительность, подводные камни восстановления записей и юридические нюансы вроде GDPR.
Материал рассчитан на тех, кто уже знаком с концепцией мягкого удаления и хочет обеспечить целостность данных на уровне схемы, а не только в коде приложения.
Как дубликаты проникают в базу и почему уникальный индекс перестаёт работать
Рассмотрим типичную таблицу users и регистрационный код, вставляющий e-mail и пароль:
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');Пока запрос один — проблем нет. Но пользователь может дважды нажать кнопку отправки формы, браузер может повторить запрос при обрыве соединения, а фоновый скрипт миграции — отработать дважды по одним и тем же данным. В результате:
-- Первый запрос
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');
-- Второй запрос (повторная отправка, двойной клик и т.д.)
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');Теперь в таблице два пользователя с одинаковым e-mail. Когда Алиса входит в систему, запрос на поиск учётной записи выглядит примерно так:
SELECT * FROM users WHERE email = 'alice@example.com' LIMIT 1;Без явного ORDER BY база данных возвращает первую попавшуюся строку. Какая именно — зависит от оптимизатора запросов, а его поведение может меняться между версиями СУБД, после обновления статистики или перестроения индексов. Результат: Алиса периодически попадает в разные аккаунты с разной историей заказов, сохранёнными предпочтениями и платёжными данными.
Реляционные базы данных справляются с этой ситуацией штатным механизмом: уникальное ограничение на столбце или группе столбцов не позволяет вставить дубликат. Достаточно выполнить:
ALTER TABLE users ADD UNIQUE (email);Теперь повторная вставка с тем же значением email завершается ошибкой нарушения ограничения, и дубликаты исключены на уровне схемы.
Проблема возвращается с введением мягкого удаления. При таком подходе запись не стирается из таблицы, а помечается флагом: столбец deleted_at получает временную метку, а у активных записей в нём остаётся NULL. В Laravel это выглядит как вызов $user->delete(), под капотом которого выполняется:
UPDATE users SET deleted_at = NOW() WHERE id = 1;Теперь пользователь решает зарегистрироваться заново с тем же e-mail. Приложение выполняет новую вставку, и в дело вступает уникальный индекс. Он видит старую, мягко удалённую запись с тем же значением email и отклоняет операцию. С точки зрения базы данных это правильно: индекс ничего не знает о бизнес-логике «удаления». Он честно следит за уникальностью значений во всей таблице.
Пользователь же оказывается в тупике: его e-mail занят аккаунтом, который он только что удалил. Поддержка получает очередное обращение, а разработчик — архитектурную задачу, для которой стандартных возможностей фреймворка уже недостаточно.
Решение для PostgreSQL: частичные индексы
PostgreSQL предлагает элегантное решение этой задачи — частичные индексы. В отличие от обычного индекса, который строится по всем строкам таблицы, частичный индекс включает строки, удовлетворяющие условию в WHERE.
CREATE UNIQUE INDEX users_email_unique
ON users (email)
WHERE deleted_at IS NULL;Индекс учитывает только активные записи — те, у которых deleted_at IS NULL. Как только запись мягко удаляется и в столбце deleted_at появляется временная метка, она автоматически исключается из индекса. Теперь уникальность проверяется только среди активных пользователей, а мягко удалённые записи в проверке не участвуют.
Решение работает атомарно на уровне базы данных. Никаких гонок между проверкой в коде и вставкой, никаких дополнительных запросов — ограничение целостности живёт там, где и положено, в схеме.
Код намеренно прост и прозрачен: разработчик, читающий схему, сразу понимает, что уникальность действует только для активных записей. Это важно в долгоживущих проектах, где над базой данных работает несколько человек и бизнес-логика со временем усложняется.
PostgreSQL поддерживает частичные индексы с произвольными предикатами, и сценарий «уникальность на подмножестве» — лишь один из возможных. В документации PostgreSQL приводится аналогичный пример: создание уникального индекса на таблице тестов, где только одна запись для заданной пары субъект-цель может быть успешной, а неуспешных попыток может быть сколько угодно. Та же идея применима и в нашем случае.
Единственное ограничение: предикат в запросе должен соответствовать предикату индекса. Если забудете добавить WHERE deleted_at IS NULL в запрос и попытаетесь найти удалённую запись по e-mail, индекс не будет задействован. Впрочем, при использовании Eloquent с трейтом SoftDeletes глобальный скоуп автоматически добавляет нужное условие ко всем запросам, так что расхождение маловероятно. Опасность возникает при ручных запросах через DB::raw() или фасад DB — в них ответственность за соответствие предикату лежит на разработчике.
Решение для MySQL: генерируемые столбцы
MySQL, в отличие от PostgreSQL, не поддерживает частичные индексы. Но задачу можно решить с помощью генерируемого столбца — колонки, значение которой вычисляется базой данных на основе выражения.
Нам нужен столбец, принимающий значение 1 для активных записей и NULL для мягко удалённых. Назовём его not_archived:
ALTER TABLE users
ADD COLUMN not_archived TINYINT
GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) STORED;Ключевое слово STORED означает, что значение физически хранится на диске и обновляется автоматически при вставке или изменении строки. Это обязательно: MySQL не позволяет индексировать виртуальные генерируемые столбцы, только хранимые. Подробнее о типах генерируемых столбцов и их ограничениях — в документации MySQL.
Теперь создаём уникальный индекс по паре (email, not_archived):
CREATE UNIQUE INDEX users_email_active_unique
ON users (email, not_archived);Вот как это работает. Для активного пользователя deleted_at IS NULL, выражение возвращает 1, и индекс фиксирует пару (alice@example.com, 1). При попытке создать второго активного пользователя с тем же e-mail база данных видит конфликт по индексу и отклоняет вставку. После мягкого удаления deleted_at получает временную метку, выражение возвращает NULL, и строка превращается в (alice@example.com, NULL).
Здесь вступает в действие особенность MySQL: уникальный индекс в этой СУБД допускает множественные значения NULL. С точки зрения MySQL, NULL — это не значение, а отсутствие значения. Два NULL не равны друг другу, поэтому несколько строк с (alice@example.com, NULL) не нарушают уникальность индекса.
Результат: активные пользователи защищены от дубликатов, а мягко удалённые записи с тем же e-mail не создают конфликтов и не мешают повторной регистрации.
Что нужно знать о STORED
Выбор STORED вместо VIRTUAL принципиален именно из-за индексации, но у него есть цена. Хранимый столбец занимает место на диске, и каждая вставка или обновление строки сопровождается вычислением и записью его значения. На практике для столбца TINYINT эти затраты пренебрежимо малы. Но если вы решите применить этот же приём для столбца с выражением, требующим сложных вычислений или работающим с большими объёмами данных, стоит заранее оценить влияние на производительность записи. Детальный разбор генерируемых столбцов и их сравнение с аксессорами Eloquent мы приводили в статье «Генерируемые столбцы и SQL-представления: практическое руководство для Laravel».
Два разных механизма решают одну и ту же задачу. PostgreSQL делает это декларативно и прозрачно — условие WHERE в определении индекса очевидно любому, кто читает схему. MySQL требует создать дополнительный столбец и положиться на неочевидное поведение уникального индекса с NULL. Оба подхода надёжны и работают на уровне базы данных, без дополнительных запросов и блокировок на стороне приложения.
Инженерная цена элегантности: что осталось за кадром
Оба решения, и с частичным индексом в PostgreSQL, и с генерируемым столбцом в MySQL, надёжно закрывают задачу уникальности среди активных записей. Но на практике всё немного сложнее, чем синтаксис CREATE INDEX. Прежде чем внедрять этот паттерн в продакшен, стоит понять, какие компромиссы вы принимаете и какие сценарии остаются не покрытыми.
Производительность под нагрузкой
Частичный индекс в PostgreSQL обычно быстрее и компактнее, чем комбинация «генерируемый столбец плюс составной индекс» в MySQL. Он хранит только активные строки, и оптимизатору не нужно обрабатывать дополнительный столбец при построении плана запроса. На таблицах с миллионами записей, где удалённые строки составляют значительную долю, разница в размере индекса и скорости поиска становится заметной.
В MySQL ситуация обратная: индекс строится по паре (email, not_archived), и для активных записей значение not_archived всегда равно 1. По сути, мы добавляем в индекс столбец, который не несёт полезной фильтрующей нагрузки для активных строк. На небольших объёмах это незаметно, но при активной вставке и частых мягких удалениях стоит мониторить размер индекса и время отклика запросов, которые на него опираются.
Восстановление удалённой записи
Предположим, пользователь удалил аккаунт, а через неделю решил восстановиться. Он вводит свой старый e-mail, и тут есть два варианта. Если никто не занял этот e-mail за время отсутствия пользователя, восстановление проходит гладко: restore() сбрасывает deleted_at в NULL, генерируемый столбец снова принимает значение 1, и запись возвращается в индекс.
Но что, если за эту неделю кто-то другой зарегистрировался с тем же e-mail? Уникальный индекс молча делает свою работу и не допустит появления двух активных записей с одинаковым адресом. Однако старая запись всё ещё существует, и при вызове restore() возникнет конфликт. Разработчику придётся решать, что делать: отклонить восстановление и попросить пользователя выбрать другой e-mail, автоматически заменить e-mail у восстанавливаемой записи или реализовать «карантинный период», в течение которого e-mail удалённого аккаунта резервируется и не доступен для новой регистрации.
Это не недостаток метода — это следствие бизнес-правила «e-mail должен быть уникален среди активных пользователей». Просто его нужно осознавать и проектировать логику восстановления соответственно.
GDPR и право на забвение
Мягкое удаление не равно анонимизации. Запись с deleted_at содержит всё те же персональные данные, что и активная: e-mail, имя, телефон, историю заказов. С точки зрения законодательства о защите данных простой флаг удаления ничего не меняет. Если пользователь из Евросоюза требует полного удаления своих данных, вы обязаны либо физически стереть строку через forceDelete(), либо заменить персональные данные на обезличенные заглушки.
Это не противоречит нашему паттерну — он решает задачу уникальности, а не соответствия законодательству. Но внедряя мягкое удаление, вы берёте на себя ответственность за полный жизненный цикл данных. В какой-то момент запись должна быть либо удалена окончательно, либо обезличена. В Laravel для автоматической очистки старых удалённых записей удобно использовать трейт Prunable, который мы разбирали в руководстве по Soft Delete.
За пределами deleted_at
Описанный подход не привязан жёстко к столбцу deleted_at. Та же логика работает для любой задачи, где уникальность должна соблюдаться только для подмножества строк. Например, у вас может быть таблица промокодов, где активным может быть только один код на заказ, а просроченных — сколько угодно. Или таблица бронирований, где уникальным должен быть только слот с определённым статусом. Частичный индекс в PostgreSQL и генерируемый столбец с NULL в MySQL — это универсальные инструменты для проектирования ограничений на подмножествах данных, и сфера их применения выходит далеко за пределы мягкого удаления.
Практическое применение в Laravel
Теория бесполезна без работающего кода. Перенесём оба решения в миграции Laravel и разберём, как они взаимодействуют с Eloquent.
Частичный индекс в PostgreSQL
Стандартный метод $table->unique() создаёт обычный уникальный индекс. Частичный индекс через него не сделать, поэтому пишем сырой SQL:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
DB::statement("
CREATE UNIQUE INDEX users_email_unique
ON users (email)
WHERE deleted_at IS NULL
");
}
public function down(): void
{
DB::statement('DROP INDEX IF EXISTS users_email_unique');
}
};Ничего сложного. Индекс прозрачен, условие читается прямо в миграции, при откате индекс удаляется.
Генерируемый столбец и индекс в MySQL
Здесь работы чуть больше. Сначала добавляем столбец, затем строим по нему индекс. Всё это — в одной миграции, поскольку Laravel позволяет комбинировать вызовы внутри Schema::table():
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::table('users', function (Blueprint $table) {
$table->unsignedTinyInteger('not_archived')
->storedAs('IF(deleted_at IS NULL, 1, NULL)');
$table->unique(['email', 'not_archived'], 'users_email_active_unique');
});
}
public function down(): void
{
Schema::table('users', function (Blueprint $table) {
$table->dropUnique('users_email_active_unique');
$table->dropColumn('not_archived');
});
}
};Метод ->storedAs() принимает SQL-выражение и создаёт хранимый генерируемый столбец. Имя индекса users_email_active_unique задано явно, чтобы метод down() мог сослаться на него при удалении. Порядок операций в down() важен: сначала снимаем индекс, потом удаляем столбец, на который он ссылается.
Если вы добавляете индекс к существующей таблице, в которой уже есть данные, убедитесь, что среди активных записей нет дубликатов. Иначе миграция упадёт с ошибкой нарушения уникальности. Подробнее о безопасном добавлении индексов мы писали в статье «Laravel Миграции: Как добавить индекс, если он существует».
Взаимодействие с Eloquent
Трейт SoftDeletes добавляет глобальный скоуп, который автоматически подставляет WHERE deleted_at IS NULL ко всем запросам Eloquent. Это означает, что и частичный индекс в PostgreSQL, и составной индекс (email, not_archived) в MySQL будут корректно задействованы оптимизатором: условие скоупа совпадает с предикатом индекса.
Но как только вы переходите на DB::table() или DB::raw(), глобальный скоуп не применяется. Запрос вроде:
$user = DB::table('users')->where('email', $email)->first();вернёт первую попавшуюся запись, включая мягко удалённую. Уникальный индекс такой запрос не использует, потому что условия не совпадают. Ответственность за добавление ->whereNull('deleted_at') в таких случаях лежит на разработчике. Не пренебрегайте этим — иначе целостность, которую вы так тщательно выстраивали на уровне схемы, обходится одним неаккуратным запросом.
Тестирование ограничения
Уникальный индекс — это часть схемы данных, и его поведение стоит проверять интеграционными тестами, а не только модульными. Убедитесь, что база данных действительно отклоняет дубликат активного e-mail, но пропускает повторную вставку мягко удалённой записи с тем же значением.
Пример теста для Laravel:
public function test_active_users_must_have_unique_email(): void
{
$alice = User::factory()->create(['email' => 'alice@example.com']);
$this->expectException(QueryException::class);
User::factory()->create(['email' => 'alice@example.com']);
}
public function test_soft_deleted_users_can_share_email(): void
{
$alice = User::factory()->create(['email' => 'alice@example.com']);
$alice->delete();
User::factory()->create(['email' => 'alice@example.com']);
$this->assertDatabaseCount('users', 2);
}Первый тест подтверждает, что ограничение работает для активных записей. Второй — что мягко удалённая запись не препятствует созданию новой с тем же e-mail. Оба теста опираются на реальную базу данных и проверяют поведение схемы, а не только кода приложения.
Заключение
Мягкое удаление — это не только трейт в модели и столбец deleted_at в таблице. Это архитектурное решение, меняющее поведение ограничений целостности на уровне базы данных. Уникальный индекс, защищавший от дубликатов, перестаёт справляться, как только в таблице появляются строки, которые «не видны, но всё ещё там».
Мы разобрали два способа вернуть контроль над уникальностью.
Частичный индекс в PostgreSQL — декларативное решение. Условие WHERE deleted_at IS NULL в определении индекса не оставляет пространства для неоднозначности. Разработчик, читающий схему, сразу понимает правило: e-mail уникален среди активных пользователей.
Генерируемый столбец в MySQL — обходной манёвр, опирающийся на семантику NULL в уникальных индексах. Он решает ту же задачу, но добавляет в таблицу дополнительную колонку и требует понимания разницы между VIRTUAL и STORED.
Выбор между ними определяется скорее СУБД, чем архитектурными предпочтениями. Но в обоих случаях принцип один: ограничение целостности должно жить на уровне схемы, а не только в коде приложения. Только так вы получаете атомарную гарантию, не зависящую от гонок между запросами, забытого условия whereNull или особенностей конкретной версии ORM.
Понимание того, как работают индексы базы данных — частичные, составные, с NULL-семантикой или без неё — это и есть та граница, которая отделяет разработчика, полагающегося на фреймворк, от инженера, проектирующего надёжные системы.
Эта статья основана на материале «Advanced Unique Index Patterns for Soft Deletes (MySQL and PostgreSQL)», опубликованном в журнале PHP Architect. Мы переработали и расширили оригинал: добавили разбор инженерных компромиссов, готовые миграции для Laravel, примеры тестов и перекрёстные ссылки на материалы блога «Заметки разработчика», чтобы статья органично вписалась в контекст блога.