Ограничения и первичный ключ

В прошлом уроке мы вставляли, меняли и удаляли строки, и SQLite послушно выполнял всё подряд. Но таблица, которая принимает что угодно, быстро превращается в свалку: два напитка с одинаковым названием, кофе без категории, цена минус сто рублей. В этом уроке учим таблицу отказывать.

Охранник на входе

Представь склад кофейни с охранником на входе. У охранника короткий список правил: у каждой коробки должна быть наклейка с номером, номера не повторяются, без названия поставщика коробку не принимают, отрицательный вес не бывает. Охранник не спорит и не исправляет: если коробка нарушает правило, она просто не проходит.

В базе такие правила называют ограничениями. Их пишут в CREATE TABLE, и дальше база проверяет каждую вставку и каждое изменение. Посмотрим, как охраняет себя таблица drinks в «Пенке»:

sqlite> INSERT INTO drinks (id, name, category) VALUES (3, 'Ещё капучино', 'кофе');
Error near line 1: UNIQUE constraint failed: drinks.id
sqlite> INSERT INTO drinks (name, category) VALUES ('Латте', 'кофе');
Error near line 2: UNIQUE constraint failed: drinks.name
sqlite> INSERT INTO drinks (name) VALUES ('Загадочный напиток');
Error near line 3: NOT NULL constraint failed: drinks.category

Три вставки, три отказа. Номер 3 уже занят капучино, латте в меню уже есть, а напиток без категории таблица не принимает. Текст ошибки называет правило и столбец: UNIQUE constraint failed: drinks.name читается как «нарушено правило уникальности в столбце name таблицы drinks». Разберём каждое правило по очереди.

Первичный ключ

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

В SQLite у каждой обычной таблицы уже есть скрытый номер строки, rowid. Если объявить столбец как INTEGER PRIMARY KEY, он становится другим именем для rowid:

sqlite> CREATE TABLE cups (id INTEGER PRIMARY KEY, size TEXT);
sqlite> INSERT INTO cups (size) VALUES ('S'), ('M'), ('L');
sqlite> SELECT rowid, id, size FROM cups;
╭────┬────┬──────╮
│ id │ id │ size │
╞════╪════╪══════╡
│  1 │  1 │ S    │
│  2 │  2 │ M    │
│  3 │  3 │ L    │
╰────┴────┴──────╯

Мы спросили rowid, а заголовок столбца сказал id: это один и тот же столбец под двумя именами. Номера мы не указывали, SQLite выдал их сам. Так получил свой номер флэт уайт в прошлом уроке. Важно написать именно INTEGER: столбец INT PRIMARY KEY или BIGINT PRIMARY KEY псевдонимом rowid уже не станет.

Новый номер SQLite берёт на единицу больше самого большого. Если удалить последнюю строку, её номер может достаться следующей:

sqlite> DELETE FROM cups WHERE id = 3;
sqlite> INSERT INTO cups (size) VALUES ('XL');
sqlite> SELECT id, size FROM cups;
╭────┬──────╮
│ id │ size │
╞════╪══════╡
│  1 │ S    │
│  2 │ M    │
│  3 │ XL   │
╰────┴──────╯

Номер 3 раньше принадлежал размеру L, теперь XL. Обычно это не мешает. Если номера никогда не должны повторяться, даже после удаления, к ключу дописывают AUTOINCREMENT: id INTEGER PRIMARY KEY AUTOINCREMENT. Документация SQLite советует не ставить его без нужды, он немного замедляет вставку.

NOT NULL, UNIQUE, DEFAULT и CHECK

Остальные правила соберём в одной таблице: сорта кофейных зёрен, которые закупает «Пенка».

sqlite> CREATE TABLE beans (
(x1...>   id INTEGER PRIMARY KEY,
(x1...>   name TEXT NOT NULL UNIQUE,
(x1...>   roast TEXT NOT NULL DEFAULT 'средняя',
(x1...>   price INTEGER CHECK (price > 0)
(x1...> );
sqlite> INSERT INTO beans (name, price) VALUES ('Эфиопия Иргачефф', 900);
sqlite> INSERT INTO beans (name, roast, price) VALUES ('Бразилия Сантос', 'тёмная', 650);
sqlite> SELECT * FROM beans;
╭────┬──────────────────┬─────────┬───────╮
│ id │       name       │  roast  │ price │
╞════╪══════════════════╪═════════╪═══════╡
│  1 │ Эфиопия Иргачефф │ средняя │   900 │
│  2 │ Бразилия Сантос  │ тёмная  │   650 │
╰────┴──────────────────┴─────────┴───────╯

Приглашение (x1...> значит, что утилита ждёт продолжения: открыта одна скобка. Что делает каждое правило:

• NOT NULL: значение обязательно, NULL не пройдёт;
• UNIQUE: значение не повторяется в других строках;
• DEFAULT: значение, если INSERT столбец не назвал; у эфиопского сорта обжарка стала «средняя» сама;
• CHECK: условие, которое должно выполняться для каждой строки.

Теперь попробуем нарушить их по очереди:

sqlite> INSERT INTO beans (name, price) VALUES ('Бразилия Сантос', 700);
Error near line 1: UNIQUE constraint failed: beans.name
sqlite> INSERT INTO beans (price) VALUES (500);
Error near line 2: NOT NULL constraint failed: beans.name
sqlite> INSERT INTO beans (name, price) VALUES ('Колумбия', -100);
Error near line 3: CHECK constraint failed: price > 0
sqlite> INSERT INTO beans (name, price) VALUES ('Кения', NULL);
sqlite> SELECT name, price FROM beans;
╭──────────────────┬───────╮
│       name       │ price │
╞══════════════════╪═══════╡
│ Эфиопия Иргачефф │   900 │
│ Бразилия Сантос  │   650 │
│ Кения            │ NULL  │
╰──────────────────┴───────╯

Первые три вставки отклонены, и каждая ошибка называет нарушенное правило. Четвёртая прошла, хотя цены нет. Дело в том, что CHECK отклоняет строку, только когда условие ложно. NULL > 0 даёт не «ложь», а «неизвестно», как мы видели в уроке про NULL, и строка проходит. Если цена обязательна, к CHECK нужно добавить NOT NULL.

Ключ, который пускает NULL

В SQLite есть странность, которой нет в других базах. Первичный ключ не целого типа принимает NULL, и сколько угодно раз:

sqlite> CREATE TABLE promo (code TEXT PRIMARY KEY, title TEXT);
sqlite> INSERT INTO promo VALUES (NULL, 'первая'), (NULL, 'вторая');
sqlite> SELECT code, title FROM promo;
╭──────┬────────╮
│ code │ title  │
╞══════╪════════╡
│ NULL │ первая │
│ NULL │ вторая │
╰──────┴────────╯

Две строки без ключа: найти каждую однозначно уже нельзя. Это старая ошибка ранних версий SQLite, которую оставили ради совместимости со старыми программами. Лечится она просто: у такого ключа всегда пиши NOT NULL явно, code TEXT PRIMARY KEY NOT NULL. В PostgreSQL первичный ключ сам по себе запрещает NULL, сравни с уроком про ограничения.

Попробуй сам

Песочница работает на свежей базе «Пенки». Создай таблицу сиропов и проверь, какие вставки пройдут. Две строки нарочно нарушают правила:

Поменяй 100 на 40 и запусти снова. Таблица останется от прошлого запуска, поэтому CREATE TABLE теперь упадёт с ошибкой: убери его или нажми «сбросить базу».

Проверь себя

Задание

Дальше

Правила мы пока задавали только при создании таблицы. А если таблица уже полна данных и в неё нужно добавить столбец или переименовать старый? В следующем уроке меняем схему через ALTER TABLE.