Уникальный индекс на NULL поля в Postgres: подробное руководство

Быстрый ответ

Для того чтобы обеспечить условную уникальность для столбцов, которые могут включать NULL значения, вам нужно создать частичный индекс. Вот пример SQL-кода для PostgreSQL:

CREATE UNIQUE INDEX idx_uniq_conditional
ON YourTable (YourColumn)
WHERE YourColumn IS NOT NULL;

Данный индекс обеспечит уникальность в столбце YourColumn для значений, отличных от NULL, и позволит включать сколько угодно строк с NULL значением в этом столбце.

Возможности PostgreSQL 15: Уникальность NULL

PostgreSQL 15 облегчает работу с уникальными ограничениями, применяемыми к NULL, благодаря новому условию NULLS NOT DISTINCT. Таким образом, NULL теперь ведёт себя аналогично любому другому уникальному значению, что упрощает управление ограничениями:

ALTER TABLE your_table
ADD CONSTRAINT your_constraint UNIQUE NULLS NOT DISTINCT (column1, column2, ...);

На версиях PostgreSQL ниже 15-й необходимо использовать другие методы реализации подобного типа уникальности.

Стратегии для старых версий PostgreSQL

Функция COALESCE: Замещение NULL и обеспечение уникальности

Функцию COALESCE можно применить как средство замены NULL на уникальные значения:

CREATE UNIQUE INDEX idx_uniq_coalesce
ON your_table ((COALESCE(your_nullable_column, 'unique_placeholder')));

Рекомендуется использовать UUID в качестве заполнителя для предотвращения коллизий. Кроме того, рекомендуется включить ограничение CHECK для предотвращения нежелательных вставок значения заполнителя.

Подробнее об этом расскажет наш спикер на видео

Генератор: Создание индексов для производных столбцов

Вычисляемые столбцы позволяют создавать виртуальные столбцы, которые являются пригодными для индексации:

ALTER TABLE your_table
ADD your_generated_column AS (COALESCE(your_nullable_column, 'unique_placeholder'));
CREATE UNIQUE INDEX idx_uniq_generated
ON your_table (your_generated_column);

Таким образом, NULL значения преобразуются в уникальные значения для нужд индексации.

Частичный индекс с перекрытием

Когда требуется создать индекс для нескольких столбцов, которые могут содержать NULL, сложность увеличивается:

CREATE UNIQUE INDEX idx_uniq_partial
ON your_table (col1, col2)
WHERE col1 IS NOT NULL AND col2 IS NOT NULL;

Необходимо внимательно подходить к выбору стратегии и оценивать возможность убрать условие WHERE для повышения эффективности работы баз данных.

Визуализация

Создание уникальных ограничений для столбцов, пропускающих NULL, можно сравнить с игрой в тетрис: необходимо формировать уникальные комбинации фигур, среди которых есть поражающиеся:

| Фигура A | Фигура B | Фигура C   |
| -------- | -------- | ---------- |
| 🧩🔹      | 🧩🔹      | 🧩 (Пустота) |
| 🧩🔹      | 🧩 (Пустота) | 🧩🔹      |
| 🧩 (Пустота) | 🧩🔹      | 🧩🔹      |

Уникальное ограничение: В каждой строке уникальная комбинация пазлов без пропусков.

Правильно:   🧩🔹🧩🔹🧩      🧩🔹🧩 🧩🔹      🧩 🧩🔹🧩🔹
Неправильно: 🧩🔹🧩🔹🧩      🧩🔹🧩🔹🧩  // Повторение однотипного набора без пропусков

Это отлично иллюстрирует концепцию уникального ограничения SQL, при котором значения NULL игнорируются, позволяя остальным значениям уникально сочетаться.

Применение на практике

Удаление замещающих ключей: Последствия

При проектировании баз данных, замещающие ключи помогают связывать таблицы. Однако, когда уникальные ограничения применяются непосредственно к столбцам данных, важно осознавать последствия их удаления:

CREATE UNIQUE INDEX idx_uniq_user_recipe
ON menu(user_id, recipe_id)
WHERE MenuId IS NULL;

Возможно, для каждой комбинации пользователя и рецепта требуется всего одна запись, независимо от значения MenuId.

Минимальные определения таблиц

Минимальные определения таблиц помогают прозрачно видеть структуру и предотвращают смешивание данных, поддерживая при этом уникальные ограничения. Лучше концентрироваться на предотвращении дублирования пар пользователь-рецепт, вне зависимости от MenuId.

Внешние ключи: Острый двухсторонний меч

Использование внешних ключей обеспечивает целостность данных, однако их включение в уникальные ограничения требует балансировки, особенно при использовании частичных индексов.

Полезные материалы

1. SQL UNIQUE Constraint — учебный курс W3Schools на тему уникальности в SQL.
2. PostgreSQL: Documentation: 16: 11.8. Partial Indexes — руководство по условной уникальности от PostgreSQL.
3. Создание индексов с фильтрацией – SQL Server | Microsoft Learn — инструкция Microsoft по созданию индексов с фильтрацией, в том числе и для NULL.
4. Как определить блокировку в SQL Server — описание индексации столбцов для уникальности на MSSQLTips.
5. Stack Overflow: Уникальное ограничение на два столбца — обсуждение на Stack Overflow о уникальности для нескольких столбцов в MySQL и других системах.