ALTER TABLE: изменение схемы

В прошлом уроке мы задавали таблице правила прямо в CREATE TABLE. Но таблицы редко остаются такими, какими их создали. Через месяц «Пенке» понадобится город поставщика, через год кто-то решит, что столбец назван неудачно. Пересоздавать таблицу с данными ради каждой правки страшно. Для этого есть ALTER TABLE.

Ремонт в работающей кофейне

Кофейню не закрывают ради новой полки. Повесить полку, сменить вывеску, убрать лишний стол можно, не выгоняя гостей. А вот перенести стойку в другой угол так не выйдет: придётся закрыться на день, вынести всё, поставить стойку заново и занести вещи обратно.

С таблицей так же. Мелкие правки схемы SQLite делает на месте командой ALTER TABLE. Крупные, вроде смены типа столбца, делают переездом: новая таблица, копия данных, старая на выброс.

Переименовать таблицу

Заведём таблицу поставщиков и сразу поймём, что «vendors» звучит привычнее:

sqlite> CREATE TABLE suppliers (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
sqlite> INSERT INTO suppliers (name) VALUES ('Зерновой двор'), ('Молочная ферма');
sqlite> ALTER TABLE suppliers RENAME TO vendors;
sqlite> .tables
customers     drinks     order_items     orders     staff     vendors

RENAME TO меняет имя таблицы, а строки остаются на месте. Если на таблицу ссылаются другие таблицы, индексы или представления, SQLite поправит имя и там.

Добавить столбец

ADD COLUMN дописывает столбец в конец таблицы. У строк, которые уже есть, в новом столбце окажется значение по умолчанию:

sqlite> ALTER TABLE vendors ADD COLUMN city TEXT;
sqlite> ALTER TABLE vendors ADD COLUMN rating INTEGER NOT NULL;
Error near line 2: Cannot add a NOT NULL column with default value NULL
sqlite> ALTER TABLE vendors ADD COLUMN rating INTEGER NOT NULL DEFAULT 5;
sqlite> SELECT * FROM vendors;
╭────┬────────────────┬──────┬────────╮
│ id │      name      │ city │ rating │
╞════╪════════════════╪══════╪════════╡
│  1 │ Зерновой двор  │ NULL │      5 │
│  2 │ Молочная ферма │ NULL │      5 │
╰────┴────────────────┴──────┴────────╯

Первый столбец добавился без вопросов, и у обоих поставщиков город стал NULL. Второй SQLite отклонил, и ошибка объясняет почему: столбец обязательный, а значения по умолчанию нет. Чем заполнить его у старых строк? Третья попытка с DEFAULT 5 проходит: старые поставщики получили рейтинг 5.

Переименовать и удалить столбец

RENAME COLUMN меняет имя столбца, DROP COLUMN убирает столбец вместе со всеми его значениями. Посмотрим, что получилось, командой .schema, она печатает CREATE TABLE таблицы в её нынешнем виде:

sqlite> ALTER TABLE vendors RENAME COLUMN city TO town;
sqlite> ALTER TABLE vendors DROP COLUMN rating;
sqlite> .schema vendors
CREATE TABLE "vendors" (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, town TEXT);

Заметь две детали. Имя таблицы в схеме теперь в кавычках: SQLite переписал текст CREATE TABLE при переименовании и взял имя в кавычки на всякий случай. И столбца rating больше нет, вместе с рейтингами, которые в нём лежали. Корзины у DROP COLUMN, как и у DELETE из урока 9, нет.

Чего ALTER TABLE не умеет

Удалить можно не любой столбец, а поменять можно не всё:

sqlite> ALTER TABLE vendors DROP COLUMN name;
Parse error near line 1: cannot drop UNIQUE column: "name"
sqlite> ALTER TABLE vendors ALTER COLUMN town SET NOT NULL;
Error near line 2: constraint failed
sqlite> ALTER TABLE vendors ALTER COLUMN town TYPE INTEGER;
Parse error near line 3: near "TYPE": syntax error
  ALTER TABLE vendors ALTER COLUMN town TYPE INTEGER;
                          error here ---^

Разберём три отказа:

• столбец с UNIQUE, первичным ключом или индексом удалить нельзя, SQLite сам говорит почему;
• ALTER COLUMN ... SET NOT NULL появился в SQLite 3.53, и он работает, но только если в столбце нет NULL. У наших поставщиков города пустые, отсюда constraint failed;
• сменить тип столбца, добавить ему CHECK или DEFAULT команда ALTER TABLE в SQLite не может вовсе, даже слова TYPE она не знает.

В PostgreSQL ALTER TABLE умеет больше, сравни с уроком про изменение таблиц. SQLite хранит схему как текст CREATE TABLE, и каждую правку ему приходится вносить прямо в этот текст, поэтому набор правок скромный.

Переезд: пересобрать таблицу

Всё, чего не умеет ALTER TABLE, делают пересборкой в четыре шага. Сделаем город обязательным, со значением по умолчанию «Москва»:

sqlite> CREATE TABLE vendors_new (
(x1...>   id INTEGER PRIMARY KEY,
(x1...>   name TEXT NOT NULL UNIQUE,
(x1...>   town TEXT NOT NULL DEFAULT 'Москва'
(x1...> );
sqlite> INSERT INTO vendors_new (id, name) SELECT id, name FROM vendors;
sqlite> DROP TABLE vendors;
sqlite> ALTER TABLE vendors_new RENAME TO vendors;
sqlite> .schema vendors
CREATE TABLE "vendors" (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  town TEXT NOT NULL DEFAULT 'Москва'
);
sqlite> SELECT * FROM vendors;
╭────┬────────────────┬────────╮
│ id │      name      │  town  │
╞════╪════════════════╪════════╡
│  1 │ Зерновой двор  │ Москва │
│  2 │ Молочная ферма │ Москва │
╰────┴────────────────┴────────╯

По шагам:

1. CREATE TABLE vendors_new с той схемой, которую хочешь получить.
2. INSERT ... SELECT копирует данные из старой таблицы, этот приём мы видели в уроке 9. Город мы не копировали, и все поставщики получили значение по умолчанию.
3. DROP TABLE удаляет старую таблицу.
4. RENAME TO отдаёт новой таблице старое имя.

В настоящей базе эти шаги оборачивают в транзакцию, чтобы при сбое посередине не остаться без таблицы, а индексы и триггеры старой таблицы создают заново. Полный рецепт с двенадцатью шагами есть в документации ALTER TABLE, а транзакции ждут тебя в уроке 23.

Попробуй сам

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

Запусти песочницу второй раз: ADD COLUMN упадёт, потому что столбец уже есть, а RENAME COLUMN не найдёт birthday. Схема пережила запуск, как пережила бы закрытие sqlite3. Кнопка «сбросить базу» вернёт таблицу к исходной.

Проверь себя

Задание

Дальше

У таблицы orders есть столбец customer_id, и в нём лежит номер гостя из customers. Но пока ничто не мешает записать туда номер 999, которого нет. В следующем уроке связываем таблицы внешними ключами и учим базу следить за такими ссылками.