Иногда кажется, что SQL — это набор классических правил с жёсткой логикой, но вот приходит тема NULL, и оказывается, что ничего жёстко не работает. В этой статье мы разберёмся, почему SQL расценивает NULL весьма необычным образом (и ломает нам «обычную» логику уникальности). Будем опираться на опыт и примеры из поста “SQL nulls are weird”, где автор подробно рассказывает, как базы SQLite, Postgres и MySQL ведут себя, когда дело доходит до NULL.
В чём суть странности?
- ☕ NULL не равен NULL — если проверять NULL = NULL, результат будет не TRUE, не FALSE, а NULL. Кажется парадоксом, но в SQL-логике «два неизвестных значения не обязательно равны».
- ☕ Уникальные колонки (с UNIQUE) позволяют хранить несколько NULL-значений. Ведь каждый NULL в SQL, по умолчанию, считается «отдельной неизвестностью».
Простой пример
Если мы создаём таблицу вида:
CREATE TABLE sample (
id TEXT primary key,
email TEXT,
deleted_at TEXT,
UNIQUE(email, deleted_at)
);
и вставляем две строки:
INSERT INTO sample (id, email, deleted_at)
VALUES ('1', 'ray@mail.com', null);
INSERT INTO sample (id, email, deleted_at)
VALUES ('2', 'ray@mail.com', null);
— может казаться, что нарушается принцип «уникальности»: 'ray@mail.com' + null вставились дважды. Но никаких ошибок не будет: SQL предполагает, что (ray@mail.com, NULL) и (ray@mail.com, NULL) — это разные комбинации, так как NULL не приравнивается к NULL.
Почему так происходит?
- ☕ Логическая трактовка: NULL — это неизвестное/отсутствующее значение. Два неизвестных значения нельзя назвать равными, раз уж мы не знаем, чем они являются.
- ☕ Исторические корни: большинство SQL-движков (SQLite, Postgres, MySQL) реализуют этот подход, чтобы соответствовать «де-факто» модельной логике SQL. При этом в некоторых других частях (например, SELECT DISTINCT) NULL могут обрабатываться иначе.
Автор у себя в статье упоминает, что стандарты SQL (теоретически) предполагают, что NULL везде должен вести себя одинаково, но на практике в движках всё выходит иначе.
Как обеспечить реальную уникальность?
Если вам необходимо запретить дублирующиеся NULL для конкретного столбца, есть два основных подхода:
1) Генерируемое поле (generated column)
Добавляем в таблицу «искусственное» поле, где NULL заменяется на некий «опорный» признак:
CREATE TABLE sample (
id TEXT PRIMARY KEY,
email TEXT,
deleted_at TEXT,
_deleted_at_coalesced TEXT GENERATED ALWAYS
AS (COALESCE(deleted_at, '1970-01-01')) STORED,
UNIQUE(email, _deleted_at_coalesced)
) STRICT;
Теперь, если deleted_at = NULL, автоматически подставляется '1970-01-01'. Благодаря этому комбинации с NULLперестают быть «разными». Однако минус в том, что:
- ☕ Дополнительное поле расходует место и усложняет схему.
- ☕ При повторном «удалении» записи (если мы обновляем deleted_at ещё раз на тот же NULL) мы можем поймать конфликты, если не аккуратно управляем значением.
2) Частичный индекс (partial index)
Другой (и более элегантный) путь — создать частичный индекс, который применяется только к строкам, где deleted_at IS NULL. Например, в SQLite/Postgres:
CREATE UNIQUE INDEX idx_sample_email_deleted_at
ON sample(email) WHERE deleted_at IS NULL;
Теперь, если мы пытаемся вставить вторую запись с одинаковым email и deleted_at=NULL, сработает ошибка уникальности именно из-за частичного индекса. При этом значения, где deleted_at не NULL, не конфликтуют с этим ограничением. Это весьма удобно:
- ☕ Меньше «мусорных» полей — не добавляем лишнюю колонку.
- ☕ Уникальность сохраняется только для тех строк, где deleted_at (или любой другой столбец) действительно NULL.
Мой взгляд
Для многих разработчиков, особенно «подросших» на ORM, такая логика NULL может стать сюрпризом. Нам подсознательно кажется, что 'NULL' — это «одно и то же пустое значение». А SQL говорит: «Нет, это неизвестное, так что все они разные».
Важные уроки:
- ☕ При проектировании схемы будьте аккуратны с уникальными индексами, если допускаете NULL в поля.
- ☕ Если хотите «настоящей» уникальности (где NULL = NULL), применяйте либо генерацию полей через COALESCE(), либо частичные индексы.
- ☕ Будьте готовы, что некоторые «фокусы» с NULL вскроются только в реальных кейсах, когда ваша система пропустит «дубликаты» и вы удивитесь, почему нарушен бизнес-логика.
Ссылки на материалы
Вывод: NULL — действительно «особая» логика в SQL. И, если вы разрабатываете БД, нужно учитывать эти тонкости, чтобы не получить «дубликаты» там, где их не ждали. Лучше сделать «явное» решение (частичный индекс, генератор столбца) и спать спокойно, чем надеяться на «интуитивное» поведение баз данных — оно может очень отличаться от того, что вы привыкли видеть в обычных языках программирования.