Как обеспечить уникальность необязательного поля (например, рабочего телефона) с учётом NULL-значений?

Question

Для обеспечения уникальности необязательного поля с NULL нужно использовать уникальный индекс, который в большинстве СУБД позволяет несколько NULL значений. Альтернативно можно использовать default значение вместо NULL или частичный индекс только для NOT NULL значений. Также можно создать вычисляемую колонку или использовать триггеры для кастомной валидации. Самый простой способ — полагаться на поведение уникального индекса с NULL.

YeaHub · Accepted Answer

Проблема уникальности опциональных полей имеет несколько решений в зависимости от СУБД и требований.Стандартное решение — Unique Index:CREATE TABLE employees (
    id INT PRIMARY KEY,
    work_phone VARCHAR(20) NULL,
    UNIQUE KEY uk_work_phone (work_phone)
);Поведение в разных СУБД:MySQL: Multiple NULLs allowed in unique indexPostgreSQL: Multiple NULLs allowedSQL Server: Multiple NULLs allowed (можно изменить)Oracle: Multiple NULLs allowedАльтернативные подходы:Замена NULL на default значение:CREATE TABLE employees (
    work_phone VARCHAR(20) DEFAULT 'NOT_PROVIDED',
    UNIQUE KEY uk_work_phone (work_phone)
);Частичный/фильтрованный индекс (PostgreSQL, SQL Server):-- Только для PostgreSQL/SQL Server
CREATE UNIQUE INDEX idx_work_phone_not_null 
ON employees (work_phone) 
WHERE work_phone IS NOT NULL;Вычисляемая колонка (SQL Server):ALTER TABLE employees 
ADD work_phone_unique AS 
    CASE WHEN work_phone IS NULL THEN 'user_' + CAST(id AS VARCHAR) 
         ELSE work_phone 
    END;

CREATE UNIQUE INDEX uk_work_phone ON employees(work_phone_unique);Проверка на уровне приложения:class EmployeeService {
    public function create($data) {
        if ($data['work_phone'] && $this->phoneExists($data['work_phone'])) {
            throw new ValidationException('Phone already exists');
        }
        return Employee::create($data);
    }
}