Исполняемые доки SQLite: NOT NULL и DEFAULT

Q: Как сделать столбец обязательным в SQLite?

Допишите NOT NULL к описанию столбца: email TEXT NOT NULL. Теперь любой INSERT или UPDATE, оставляющий это поле в NULL, упадёт с ошибкой NOT NULL constraint failed. Если нужно, чтобы при отсутствии значения подставлялось что-то осмысленное, добавьте к нему ещё и DEFAULT.

Q: Как работают значения по умолчанию в SQLite?

Конструкция DEFAULT задаёт, что подставить, если в INSERT столбец не указан. В качестве значения можно использовать литерал (DEFAULT 0, DEFAULT 'pending'), NULL или выражение в скобках — например, DEFAULT (CURRENT_TIMESTAMP) или DEFAULT (lower(hex(randomblob(8)))). Выражения вычисляются заново при каждой вставке.

Q: Почему SQLite ругается «NOT NULL constraint failed» при INSERT?

Вы вставляете строку, не указав значение для столбца с NOT NULL, у которого нет DEFAULT. Решений три: перечислить столбец в INSERT, добавить ему DEFAULT или снять ограничение. И учтите: явная передача NULL тоже упадёт — NOT NULL отвергает любые null-значения, откуда бы они ни пришли.

Q: Можно ли добавить NOT NULL к существующему столбцу в SQLite?

Напрямую — нет, в SQLite нет ALTER TABLE ... ALTER COLUMN. Есть два пути: либо добавить новый столбец сразу с NOT NULL DEFAULT (без DEFAULT существующие строки нарушат ограничение), либо пересобрать таблицу — создать новую с нужным ограничением, перелить данные через INSERT ... SELECT, удалить старую и переименовать новую.

Два ограничения, которые реально окупаются

Большинство багов из-за небрежной схемы сводятся к двум вещам: либо в столбце оказывается NULL там, где его никто не ждал, либо в столбце нет значения, которое приложение считает само собой разумеющимся. NOT NULL и DEFAULT закрывают обе эти проблемы — и стоят почти ничего.

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonymous',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

Один столбец обязателен и не имеет значения по умолчанию. У двух других — есть. В INSERT достаточно было передать только email, всё остальное SQLite подставил сам. Вот, собственно, и вся фича в одном примере — дальше на странице разберём нюансы.

NOT NULL в SQLite: «никаких NULL, без исключений»

Ограничение NOT NULL работает ровно так, как звучит. Любая попытка записать NULL в такой столбец — хоть через INSERT без значения и без DEFAULT, хоть явным NULL в запросе — завершится ошибкой:

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Это завершится ошибкой: отсутствует title.
INSERT INTO posts (id) VALUES (1);

Ошибка выглядит так:

Runtime error: NOT NULL constraint failed: posts.title

Тот же результат получится, если передать NULL напрямую:

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Ошибка во время выполнения: NOT NULL constraint failed: posts.title

Это контракт. Если столбец логически обязателен, пометьте его как NOT NULL — и вы разом закроете целый класс багов: никакой код приложения уже не протащит NULL мимо базы.

DEFAULT подставляет значение, когда вызывающий его не указал

DEFAULT срабатывает только тогда, когда в INSERT столбец вообще не упомянут. Он не спасёт от явного NULL:

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Написать документацию');
INSERT INTO tasks (title, status) VALUES ('Отправить в продакшн', 'done');

SELECT * FROM tasks;

Первая вставка опирается на значение по умолчанию. Вторая его переопределяет. А вот если бы вы написали INSERT INTO tasks (title, status) VALUES ('x', NULL), то получили бы ошибку NOT NULL constraint failed — столбец указан явно, и DEFAULT уже не сработает.

Запомните эту простую модель: DEFAULT подставляется только тогда, когда столбец отсутствует в запросе. NOT NULL отбрасывает NULL-значения независимо от того, откуда они пришли. Это два независимых механизма, которые отлично работают вместе.

Выражения в DEFAULT

Чаще всего по умолчанию задают литерал (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), но SQLite позволяет указать в скобках и целое выражение. Именно так удобно проставлять время создания записи или генерировать случайный идентификатор:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('вход');
INSERT INTO events (name) VALUES ('выход');

SELECT * FROM events;

Несколько важных моментов:

Выражение вычисляется при каждой вставке, а не один раз при создании таблицы. Каждая строка получает свою метку времени и свой токен.
CURRENT_TIMESTAMP, CURRENT_DATE и CURRENT_TIME — это три специальных ключевых слова, которым не нужны скобки. Всему остальному — нужны.
В выражении нельзя ссылаться на другие столбцы или подзапросы — оно должно быть самодостаточным.

Хотите, чтобы столбец был необязательным, но автоматически заполнялся, когда значение есть? Уберите NOT NULL, оставьте default. Нужен и обязательный, и автозаполняемый — пишите оба.

DEFAULT NULL — это валидно (и иногда именно то, что нужно)

Запись DEFAULT NULL равносильна полному отсутствию значения по умолчанию: если вы не передаёте значение, столбец будет NULL. Такая запись пригодится, когда хочется явно зафиксировать в схеме, что «отсутствие значения» — это и есть исходное состояние:

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

bio и avatar ведут себя здесь одинаково. DEFAULT NULL у bio — это, по сути, комментарий, написанный кодом: он подсказывает любому, кто читает схему, что отсутствие биографии — нормальное состояние, а не недосмотр.

Как добавить NOT NULL к существующему столбцу в SQLite

Вот тут начинаются нюансы. ALTER TABLE в SQLite намеренно урезан — привычного ALTER COLUMN ... SET NOT NULL, как в Postgres, тут нет. Что именно можно сделать — зависит от того, существует ли столбец.

Для нового столбца ADD COLUMN ... NOT NULL работает, но обязательно нужно указать значение по умолчанию. Иначе в уже существующих строках мгновенно оказался бы NULL в NOT NULL-столбце, а это невозможно:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('виджет'), ('штуковина');

-- Работает: существующие строки получают значение по умолчанию.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

Если попробовать сделать то же самое без DEFAULT, получим ошибку:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Ошибка во время выполнения: невозможно добавить столбец NOT NULL со значением по умолчанию NULL

Если столбец уже существует, поменять его «на месте» не получится. Стандартный приём — танец с пересозданием: делаем новую таблицу с нужным ограничением, переливаем туда данные, удаляем старую, переименовываем новую. Подробно этот сценарий разбираем на странице drop-and-alter-table, а пока просто держите в голове, что ограничение реальное, и закладывайте это в схему заранее.

Боевая комбинация ограничений

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

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

Прочитайте эту схему сверху вниз — и вы поймёте, что делает приложение, даже не глядя на код. Поле customer обязательное и без значения по умолчанию: тот, кто вызывает вставку, обязан знать, для кого создаётся заказ. У суммы, валюты и статуса есть разумные значения по умолчанию, поэтому даже самый простой INSERT даст связную строку. Поле notes необязательное. А created_at проставляется самой базой — и это единственное место, где его и нужно проставлять.

Вот в этом и смысл ограничений столбцов SQLite — они превращают молчаливые договорённости в правила, которые база следит соблюдать сама.

Типичные грабли

Короткий список вещей, на которых обычно спотыкаются:

Явный NULL отменяет DEFAULT. Запрос INSERT INTO t (col) VALUES (NULL) не подставит значение по умолчанию sqlite. Чтобы оно сработало, столбец должен отсутствовать в списке колонок.
Выражение в DEFAULT оборачивается в скобки. DEFAULT CURRENT_TIMESTAMP работает (это одно из трёх специальных ключевых слов). А вот DEFAULT lower(hex(randomblob(8))) — нет, нужно так: DEFAULT (lower(hex(randomblob(8)))).
NOT NULL и пустая строка — это разные вещи. '' — вполне допустимое значение TEXT, и ограничение оно не нарушит. Если хочется запретить ещё и пустые строки, это уже работа для CHECK (как раз о нём дальше).
ALTER TABLE ... ADD COLUMN ... NOT NULL требует DEFAULT, причём не NULL. Иначе SQLite просто откажется добавлять столбец.

Дальше: ограничения CHECK

NOT NULL и DEFAULT закрывают сценарии «должно быть указано» и «подставь значение, если не передали». Для следующего уровня проверок — «должно быть положительным», «должно быть одним из перечисленных значений», «дата окончания позже даты начала» — в SQLite есть ограничения CHECK. Они позволяют написать произвольное булево выражение, которому обязана соответствовать каждая строка. Об этом — на следующей странице.

Часто задаваемые вопросы

Как сделать столбец обязательным в SQLite?

Допишите NOT NULL к описанию столбца: email TEXT NOT NULL. Теперь любой INSERT или UPDATE, оставляющий это поле в NULL, упадёт с ошибкой NOT NULL constraint failed. Если нужно, чтобы при отсутствии значения подставлялось что-то осмысленное, добавьте к нему ещё и DEFAULT.

Как работают значения по умолчанию в SQLite?

Конструкция DEFAULT <значение> задаёт, что подставить, если в INSERT столбец не указан. В качестве значения можно использовать литерал (DEFAULT 0, DEFAULT 'pending'), NULL или выражение в скобках — например, DEFAULT (CURRENT_TIMESTAMP) или DEFAULT (lower(hex(randomblob(8)))). Выражения вычисляются заново при каждой вставке.

Почему SQLite ругается «NOT NULL constraint failed» при INSERT?

Вы вставляете строку, не указав значение для столбца с NOT NULL, у которого нет DEFAULT. Решений три: перечислить столбец в INSERT, добавить ему DEFAULT или снять ограничение. И учтите: явная передача NULL тоже упадёт — NOT NULL отвергает любые null-значения, откуда бы они ни пришли.

Можно ли добавить NOT NULL к существующему столбцу в SQLite?

Напрямую — нет, в SQLite нет ALTER TABLE ... ALTER COLUMN. Есть два пути: либо добавить новый столбец сразу с NOT NULL DEFAULT <значение> (без DEFAULT существующие строки нарушат ограничение), либо пересобрать таблицу — создать новую с нужным ограничением, перелить данные через INSERT ... SELECT, удалить старую и переименовать новую.

Связанные концепции

SQLite NOT NULL и DEFAULT: ограничения столбцов