🧩 Исключение строк из уникальных индексов

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

Partial индекс позволяет задать условие (например, WHERE deleted_at IS NULL), чтобы включать только нужные строки и игнорировать удаленные. Эта функция доступна в PostgreSQL, где можно исключать строки с soft delete, сохраняя требуемую уникальность и оптимизируя размер индекса.

⚠️ Советуем сделать бэкап своей БД перед выполнением запросов во избежании потери данных

Реализация
В PostgreSQL добавление уникального индекса для активных пользователей выглядит так:

CREATE UNIQUE INDEX users_email_uniq ON users (
  email
) WHERE deleted_at IS NULL;

В этом случае строки, у которых deleted_at не задан, включаются в индекс, а остальные игнорируются, что делает индекс более компактным и эффективным.

Для MySQL требуется обходной путь, так как partial индексы в нем не поддерживаются. С использованием конструкции IF(deleted_at, NULL, 1) можно эмулировать аналогичное поведение. Пример индекса:

CREATE UNIQUE INDEX users_email_uniq ON users (
  email,
  (IF(deleted_at, NULL, 1))
);

Здесь NULL используется чтобы исключить удаленные строки из индекса: строки, помеченные как удаленные, получают значение NULL и не конфликтуют с другими значениями.

Как это работает?
Есть некое свойство базы данных, что значения NULL не рекомендуется сравнивать ни с чем, используя ANSI SQL стандартные операторы (=, !=, <> и т.д.), т.к. сравнение приведёт к unknown. Получится так, что два значения (example@example.com, null) в индексе могут спокойно существовать, в то время как два значения (example@example.com, 1) будут конфликтовать. Это свойство позволяет обойти ограничение MySQL и реализовать функциональность partial индекса. Например, строка с адресом foo@example.com, если пользователь помечен как удаленный, получит значение индекса (foo@example.com, NULL) и не создаст конфликта, даже если аналогичная строка существует. Такая стратегия помогает соблюсти уникальность данных, сохраняя логическую целостность таблицы и избегая лишних конфликтов в MySQL.

Преимущества частичных индексов
Кроме того, partial индексы сокращают объем данных, включаемых в индекс, тем самым экономя место и ускоряя операции поиска. PostgreSQL предлагает удобный и простой способ реализации частичных индексов, в то время как MySQL требует определенных обходных решений.