Информатика · объяснение и практика
UNIQUE и NULL: одинаковые пустые значения зависят от договора СУБД
Почему SQLite допускает несколько NULL в UNIQUE: неизвестное значение, пустая строка и отдельное требование NOT NULL.

В теме Таблицы, базы данных и SQL · Учебная карта
В маршруте Запросы, ограничения и устойчивое чтение · 10 тем
Главная мысль
В SQLite ограничение UNIQUE допускает несколько NULL: неизвестные значения не считаются одинаковыми для этой проверки. Если поле должно быть и заполненным, и уникальным, нужны оба требования — NOT NULL и UNIQUE. Пустая строка имеет другой смысл.
Неизвестное значение не является готовым номером
Пусть у школьных проектов есть необязательный регистрационный код. Несколько новых проектов ещё не получили код, поэтому в соответствующей колонке хранится NULL. Он означает отсутствие известного значения, а не специальную строку, которую все проекты делят между собой.
В SQLite UNIQUE не считает два NULL совпадающими ключами. Поэтому несколько незаполненных кодов допустимы, а повтор одного настоящего кода вызовет нарушение. Это поведение нужно указывать с названием СУБД: правила обращения с NULL в уникальных ограничениях могут различаться у разных систем и вариантов индексов.
Отличаем NULL от пустого текста
Пустая строка — уже конкретное текстовое значение длины ноль. Если приложение вместо отсутствующего кода сохраняет пустую строку, две такие записи столкнутся по UNIQUE. Пробел также является текстом, хотя пользователь может видеть поле как пустое. Нормализация ввода и ограничения базы решают разные части задачи.
Для обязательного кода разумно отдельно запретить NULL, отдельно определить допустимый формат и отдельно потребовать уникальность. NOT NULL не запрещает пустой текст, а UNIQUE не проверяет длину. Поэтому выражение «поле уникальное» ещё не описывает полностью, какие данные приложение разрешает сохранять.
Пример
Для колонки code TEXT UNIQUE последовательность NULL, NULL, «R-16» разрешена. Повтор «R-16» нарушит уникальность; две пустые строки тоже совпадут.
NULL остаётся отсутствующим значением. Пустой текст и заполненный код являются определёнными строками, которые проверяются на повтор.
Составной ключ тоже требует понимания пропусков
Уникальность пары полей не равна уникальности каждого поля отдельно. Например, одинаковые номера проектов могут быть разрешены в разных учебных годах. Но когда одна часть такой пары допускает NULL, проверка уже не обеспечивает требование «единственная полностью определённая пара» для незаполненных данных.
Перед созданием ограничения полезно записать несколько ожидаемых вставок: два пропуска, два одинаковых кода, код с лишним пробелом, одинаковый номер в разных годах. Для каждого случая заранее определите разрешение или ошибку. Такая таблица решений помогает выбрать ограничения по смыслу данных, а не судить об их работе по одной удачной записи.
От чтения к действию
А теперь — вы
Частые вопросы
NOT NULL запрещает пустую строку?
Нет. Он запрещает SQL NULL. Для пустого текста требуется отдельное правило проверки.
Одинаково ли это работает во всех СУБД?
Нет. Материал описывает SQLite; поведение другой системы и конкретного индекса нужно проверять отдельно.
Источники и работа с материалом
Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.
Продолжить разбираться
CHECK и неизвестное значение: когда NULL не нарушает ограничение
CHECK и NULL в SQLite: почему условие score > 0 допускает пропуск и когда требуется отдельное NOT NULL.
Открыть темуNO ACTION и RESTRICT: когда проверяется ссылка в транзакции
Отложенный NO ACTION и немедленный RESTRICT в SQLite: две команды одной транзакции, проверка COMMIT и порядок перепривязки дочерней строки.
Открыть тему