Soft delete с уникальными ограничениями проектируется через разделение физического удаления данных и логического скрытия записей. Это позволяет сохранить целостность референтных связей (индексов) и бизнес-логики, зависящей от существования записи. Ключевой принцип: физическая запись не удаляется из таблицы, а помечается флагом или перемещается в архивную таблицу, при этом уникальные ограничения настраиваются так, чтобы игнорировать этот флаг.
Архитектурные подходы к реализации
Существует два основных способа реализации soft delete: использование флагов в основной таблице и перемещение записей в отдельную таблицу архива. Выбор зависит от требований к производительности, объема данных и сложности запросов.
Первый подход предполагает добавление поля-флага (например, is_deleted или deleted_at) в основную таблицу. При удалении запись помечается этим флагом, но физически остается на месте. Уникальные ограничения настраиваются так, чтобы пропускать строки с установленным флагом удаления.
Второй подход предполагает перемещение записей в отдельную таблицу архива (например, users_archive). Это позволяет полностью очистить основную таблицу от удаленных данных, сохраняя при этом возможность восстановления. Однако этот метод требует более сложной логики синхронизации и может замедлять операции записи.
Для большинства задач рекомендуется использовать флаг в основной таблице, так как он проще в реализации и не требует дополнительных таблиц для хранения истории изменений.
Настройка уникальных ограничений с учетом soft delete
Стандартные уникальные ограничения (UNIQUE CONSTRAINT) проверяют все строки таблицы, включая помеченные на удаление. Чтобы обойти это ограничение, необходимо создать новое уникальное ограничение, которое игнорирует строки с установленным флагом удаления.
В PostgreSQL это можно реализовать с помощью функционального индекса или условного ограничения (CHECK constraint). Например, создание уникального индекса только для строк, где is_deleted = false.
В MySQL и MariaDB также поддерживаются условные индексы через выражения в определении индекса. Пример: CREATE UNIQUE INDEX idx_unique_email ON users(email) WHERE is_deleted = 0;
При использовании PostgreSQL можно создать функциональный индекс: CREATE UNIQUE INDEX idx_unique_email_active ON users(email) WHERE NOT is_deleted;
Пример реализации в SQL
Рассмотрим пример таблицы пользователей с уникальным email и флагом удаления:
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE, is_deleted BOOLEAN DEFAULT FALSE, deleted_at TIMESTAMP NULL);
Для реализации soft delete с сохранением уникальности создаем условный индекс:
CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE NOT is_deleted;
При удалении пользователя выполняется UPDATE вместо DELETE: UPDATE users SET is_deleted = TRUE, deleted_at = NOW() WHERE id = ? AND is_deleted = FALSE;
Важно убедиться, что все запросы к таблице учитывают флаг удаления. Это делается через добавление условия в WHERE-запрос или использование функции для фильтрации активных записей.
Оценка результата и тестирование
После реализации необходимо проверить, что уникальные ограничения работают корректно. Попробуйте вставить дублирующую запись с активным флагом — она должна быть принята, а попытка вставки активной копии той же записи должна вызвать ошибку.
Проверьте производительность запросов с фильтрацией по флагу удаления. Убедитесь, что условные индексы используются оптимизатором запросов для ускорения поиска активных записей.
Тестируйте восстановление данных из архива или обратное удаление (откат soft delete). Убедитесь, что при восстановлении уникальные ограничения снова работают корректно.
Ограничения и риски
Основное ограничение подхода с флагом — необходимость фильтрации всех запросов по умолчанию. Это может привести к увеличению нагрузки на базу данных при больших объемах удаленных записей.
Условные индексы могут быть менее эффективными, чем обычные, особенно если процент удаленных записей высок. Оптимизатор запросов может не использовать такие индексы для сортировки или группировки.
При миграции данных или изменении схемы необходимо убедиться, что новые таблицы и ограничения также учитывают флаг удаления. Иначе могут возникнуть конфликты при вставке дублирующих записей.
Практические рекомендации
Всегда используйте временную метку (deleted_at) вместе с флагом удаления. Это позволяет отсортировать записи по времени удаления и восстанавливать их в правильном порядке.
Регулярно очищайте таблицу архива или удаляйте старые записи, если они больше не нужны для восстановления. Это поможет сохранить производительность базы данных.
Документируйте логику soft delete в коде и комментариях к базе данных. Это упростит поддержку проекта и предотвратит ошибки при работе с удаленными записями.
Вопросы и ответы
Можно ли использовать soft delete без изменения структуры таблицы?
Нет, для реализации soft delete необходимо добавить поле-флаг или создать отдельную таблицу архива. Без этого невозможно логически отделить удаленные записи от активных.
Как восстановить удаленную запись?
Восстановление осуществляется через обратный UPDATE, который сбрасывает флаг удаления и возвращает текущее значение поля deleted_at в NULL. Если используется таблица архива, то восстановление требует перемещения записи обратно из архива.
Нужно ли удалять записи из таблицы архива?
Нет, удаление записей из таблицы архива не требуется. Они хранятся там до тех пор, пока не будут удалены вручную или автоматически по расписанию.
По теме
CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE NOT is_deleted;CREATE UNIQUE INDEX idx_users_email_active ON users(email) WHERE is_deleted = 0;
