Уникальные индексы и NULL

Согласно стандарту ANSI SQL, NULL – это специальное значение или псевдозначение, которое используется для обозначения отсутствия в поле базы данных какого-либо значения. О таком поле можно сказать, что оно имеет неопределенное значение или то, что оно пустое. Стандарт регламентирует, что NULL не равен NULL даже для полей с одинаковым типом данных. Хотя при этом строки, содержащие в поле NULL, группируются вместе при использовании DISTINCT или GROUP BY. В определении уникального ограничения SQL-92 ни слова не говорит про NULL: "A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns". Вероятно, это означает, что оно должно рассматривать каждый NULL, как уникальное значение. Как это реализовано в различных СУБД?

По информации сайта modern SQL, в ограничении уникальности NULL является уникальным значением в Apache Derby, H2, MariaDB, MySQL, PostgreSQL и SQLite. А вот для таких лидеров рынка СУБД, как Oracle и MS SQL Server, NULL не является уникальным значением. Принятая в начале июня 2023 года новая версии стандарта SQL:2023 устранила неоднозначность реализации NULL в ограничении уникальности. Теперь по умолчанию NULL в ограничении уникальности – это уникальное значение и добавление второй строки с NULL приведет к ошибке, но это поведение можно изменить с помощью специального параметра.

Рассмотрим реализацию двух поведений ограничения уникальности при обработке NULL у представителей различного подхода к этому вопросу. Для этого в PostgreSQL и MS SQL Server создадим тестовую таблицу с одним полем и уникальным индексом по нему, а потом добавим в нее 4 строки, две из которых содержат NULL.

Сначала попробуем PostgreSQL, поведение которого соответствует требованиям стандарта SQL: Строки добавились, а значит каждый NULL у PostgreSQL в ограничении уникальности – это различное значение.

Теперь посмотрим на поведение MS SQL Server: Добавление второй строки с NULL привело к нарушению уникальности ключа, а значит каждый NULL у MS SQL Server в ограничении уникальности – это одинаковое значение.

Как в MS SQL Server создать уникальный индекс, который соответствует стандарту SQL? Для этого воспользуемся отфильтрованным (частичным) индексом с проверкой уникального поля на IS NOT NULL:

Обратный вопрос. Как создать уникальный индекс на PostgreSQL, чтобы можно было вставить в таблицу только одну строку с NULL? Создадим индекс по выражению и заменим NULL на значение, которое логически не может быть значением столбца. Например, ноль. Для соответствия готовящемуся стандарту SQL:2023 разработчики PostgreSQL 15 добавили в команду создания уникального индекса необязательный параметр "NULLS [ NOT ] DISTINCT". Значение, которого по умолчанию NULLS DISTINCT и все NULL, как и в предыдущих версиях СУБД, в столбце индекса считаются не равными друг другу. А вот значение NULLS NOT DISTINCT изменяет это правило и все NULL в столбце индекса считаются равными друг другу. Таким образом начиная с 15-й версии PostgreSQL обработка NULL в уникальном ограничении или индексе полностью соответствует стандарту SQL:2023.

В скриптах PostgreSQL для создания уникального индекса, в котором все значения NULL должны считаться равными друг другу, можно создавать его по-разному в зависимости от версии СУБД.

DO $$
BEGIN
  IF EXISTS ( SELECT * 
                FROM pg_settings 
               WHERE name = 'server_version_num' AND SUBSTR(setting, 1, 2)::INT >= 15 ) THEN
    execute 'create unique index UI_XYZ on XYZ (X) NULLS NOT DISTINCT';
  ELSE
    create unique index UI_XYZ on XYZ (COALESCE(X, 0));
  END IF;
END $$;