Как сделать уникальный ключ выборочно по условию?

Уникальность по условию: стратегии и решения

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

Основные подходы

1. Частичные (условные) индексы

Наиболее эффективное и декларативное решение в современных СУБД — создание частичного индекса (partial index), который также называют условным (conditional) или фильтрованным (filtered).

-- PostgreSQL
CREATE UNIQUE INDEX idx_users_email_active 
ON users(email) 
WHERE deleted_at IS NULL;

-- SQL Server
CREATE UNIQUE INDEX idx_users_email_active 
ON users(email) 
WHERE deleted_at IS NULL;

-- MySQL (8.0+ поддерживает функциональные индексы)
CREATE UNIQUE INDEX idx_users_email_active 
ON users((CASE WHEN deleted_at IS NULL THEN email END));

В этом примере уникальность email гарантируется только для неудаленных пользователей. Удаленные пользователи (deleted_at IS NOT NULL) могут иметь дубликаты email.

2. Композитные ключи с флагом состояния

Альтернативный подход — расширить уникальный ключ, включив в него колонку состояния:

ALTER TABLE users 
ADD UNIQUE KEY uk_email_status (email, is_active);

Это работает, когда у вас есть четкое бинарное состояние (например, is_active = 1 для активных записей). Однако для мягкого удаления потребуется дополнительная логика, так как deleted_at может иметь много значений.

3. Триггеры для сложных условий

Для сложных условий, которые плохо ложатся на индексы, можно использовать триггеры:

DELIMITER //
CREATE TRIGGER check_unique_email_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.deleted_at IS NULL THEN
        IF EXISTS (
            SELECT 1 FROM users 
            WHERE email = NEW.email 
            AND deleted_at IS NULL
            AND id != NEW.id
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Email already exists for active user';
        END IF;
    END IF;
END //
DELIMITER ;

Реализация на уровне приложения (PHP)

В PHP-приложении можно реализовать дополнительную проверку:

class UserRepository
{
    public function create(array $data): User
    {
        // Проверяем уникальность только для активных пользователей
        if ($this->isDuplicateEmailForActiveUser($data['email'])) {
            throw new ValidationException('Email already exists for active user');
        }
        
        return User::create($data);
    }
    
    private function isDuplicateEmailForActiveUser(string $email): bool
    {
        return User::where('email', $email)
            ->whereNull('deleted_at')
            ->exists();
    }
}

Практические сценарии использования

1. Мягкое удаление (Soft Delete)

   • Активные записи должны быть уникальны
   • Удаленные записи могут иметь дубликаты

2. Версионность данных

   • Только последняя версия должна соблюдать уникальность
   • Исторические версии могут дублироваться

3. Мультитенантность

   • Уникальность в пределах одного тенанта
   • Разные тенанты могут иметь одинаковые значения

Рекомендации и ограничения

• Производительность: Частичные индексы обычно эффективнее триггеров и проверок на уровне приложения
• Переносимость: Синтаксис частичных индексов различается между СУБД
• Сложность условий: Чем сложнее условие, тем предпочтительнее триггеры или проверка в приложении
• Согласованность: Триггеры гарантируют целостность на уровне БД, проверка в приложении — только на уровне приложения

Пример комплексного решения

-- Создаем таблицу с мягким удалением
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMP NULL,
    -- Уникальность SKU только для неудаленных продуктов
    UNIQUE KEY uk_sku_active (sku, (IF(deleted_at IS NULL, 1, NULL)))
);

-- Или в PostgreSQL/SQL Server
CREATE TABLE products (
    id INT PRIMARY KEY IDENTITY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    deleted_at DATETIME NULL
);

CREATE UNIQUE INDEX idx_sku_active 
ON products(sku) 
WHERE deleted_at IS NULL;

Выбор подхода зависит от конкретной СУБД, сложности условий и требований к производительности. Для большинства случаев частичные индексы являются оптимальным решением, обеспечивая как уникальность по условию, так и высокую производительность запросов.