UNIQUE и NULL: почему допускается несколько незаполненных кодов

Клиент получит внешний код после синхронизации, а до неё в поле стоит NULL. Нужно разрешить таких клиентов много, но не допускать повторения уже известных кодов. Уникальный индекс MySQL как раз различает эти ситуации. Проверим NULL, пустую строку и обычный код на небольшой таблице.

Неизвестный код разрешён многим

CREATE TABLE demo_external_codes (
    id integer PRIMARY KEY,
    external_code varchar(40) NULL,
    UNIQUE KEY uq_external_code (external_code)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;
INSERT INTO demo_external_codes VALUES (1, NULL), (2, NULL), (3, 'C-101');
SELECT id, external_code FROM demo_external_codes ORDER BY id;
-- Выполните отдельно: ожидается ошибка повторяющегося ключа.
INSERT INTO demo_external_codes VALUES (4, 'C-101');

Уникальный индекс допускает несколько NULL для допускающего пропуск столбца, но не одинаковые известные коды. В этом примере первые три строки сохраняются, четвёртая отклоняется. Это удобно, если отсутствие кода разрешено для любого количества клиентов. Если код обязателен уже при создании клиента, задайте NOT NULL и измените процесс регистрации соответствующим образом.

Пустая строка имеет другое значение

INSERT INTO demo_external_codes VALUES (5, '');
-- Отдельная команда: второй пустой код должен конфликтовать.
INSERT INTO demo_external_codes VALUES (6, '');
SELECT count(*) FROM demo_external_codes WHERE external_code IS NULL;
SELECT count(*) FROM demo_external_codes WHERE external_code = '';

Пустая строка является конкретным значением, поэтому вторая такая запись нарушает уникальность. Для поиска отсутствующего значения используется IS NULL. Сравнение через знак равенства с NULL не заменяет эту проверку. Приложение должно явно решать, превращать ли пустое поле формы в NULL, а не оставлять это на случайное поведение разных обработчиков.

Проверяем смысл равенства

Для текстового кода важны collation и правила нормализации входа. Если бизнес различает регистр символов, это требование нужно сопоставить с фактическим сравнением столбца. Не ограничивайтесь проверкой двух абсолютно одинаковых строк: добавьте варианты регистра и пробелов, соответствующие реальному формату кода. Отдельно проверьте импорт из файлов и обновление существующего клиента: они должны применять то же правило, что и форма создания. Иначе одинаковые входные данные будут получать разные представления в зависимости от пути записи.

Перед добавлением UNIQUE в существующую таблицу найдите повторяющиеся известные коды и решите, как их объединять. Удалять записи только ради успешного ALTER TABLE нельзя. Явно определите, когда код обязателен, как нормализуется и какие значения считаются одинаковыми. Затем проверьте эти правила вставками.

Источники и дальнейшее чтение

• MySQL 8.0: CREATE INDEX
• MySQL 8.0: ограничения некорректных данных