Исполняемые доки SQLite: DROP и ALTER TABLE

Q: Как удалить таблицу в SQLite?

Пишем DROP TABLE имя_таблицы;. Чтобы запрос не падал, если таблицы уже нет, добавьте IF EXISTS: DROP TABLE IF EXISTS users;. Вместе с таблицей улетают её индексы и триггеры. И учтите: если включены внешние ключи, удаление не пройдёт, пока на таблицу ссылаются другие.

Q: Что вообще умеет ALTER TABLE в SQLite?

Всего четыре операции: RENAME TO — переименовать таблицу, RENAME COLUMN ... TO ... — переименовать колонку, ADD COLUMN — добавить новый столбец и DROP COLUMN — удалить столбец (доступно с SQLite 3.35). И всё. Поменять тип колонки, изменить значение по умолчанию или навесить ограничение на уже существующий столбец нельзя.

Схемы меняются. SQLite позволяет их менять — почти всегда

Рано или поздно таблицу захочется переименовать, добавить в неё колонку, выкинуть лишнее или вообще переделать структуру с нуля. Для большинства таких случаев в SQLite есть DROP TABLE и ALTER TABLE, а на остальные — задокументированный обходной приём.

Загвоздка вот в чём: ALTER TABLE в SQLite заметно урезаннее, чем в Postgres или MySQL. Главный навык — понимать, что он умеет, чего не умеет, и как делать пересборку таблицы там, где напрямую не получится.

DROP TABLE — удалить таблицу sqlite вместе со всем содержимым

Команда DROP TABLE удаляет таблицу целиком: строки, индексы и все триггеры, которые на ней висят. Откатить это нельзя:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('временно'), ('тоже временно');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

Таблицы больше нет. Если сейчас попробовать к ней обратиться, получим ошибку no such table: scratch.

Когда вы не уверены, существует ли таблица (типичная ситуация в скриптах инициализации), добавьте IF EXISTS — тогда команда просто молча отработает, если таблицы нет:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'оба удаления выполнены успешно' AS result;

Без IF EXISTS второй DROP свалится с ошибкой. С ним оба запроса отработают спокойно.

Внешние ключи могут заблокировать DROP

Если включена проверка внешних ключей (PRAGMA foreign_keys = ON;) и какая-то другая таблица ссылается на ту, которую вы пытаетесь удалить, то drop table завершится ошибкой:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

Вариантов несколько: либо сначала удалить ссылающуюся таблицу, либо убрать строки, которые на неё ссылаются, либо при создании внешнего ключа сразу прописать ON DELETE CASCADE. SQLite не станет молча ломать ссылочную целостность за вас.

ALTER TABLE в SQLite: четыре доступные операции

В SQLite команда ALTER TABLE умеет ровно четыре вещи:

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

-- 1. Переименовать таблицу
ALTER TABLE posts RENAME TO articles;

-- 2. Переименовать столбец
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Добавить столбец
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Удалить столбец (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Каждая из этих команд — это одно SQL-выражение. Первые две практически бесплатны: меняется только схема. ADD COLUMN тоже отрабатывает быстро — SQLite не переписывает таблицу, а просто фиксирует определение новой колонки в схеме. А вот DROP COLUMN уже тяжелее: SQLite приходится пройтись по всем строкам и физически удалить данные этой колонки.

ADD COLUMN со значением по умолчанию

Когда вы добавляете новую колонку в существующую таблицу, во всех строках там по умолчанию будет NULL — если, конечно, вы сами не зададите значение по умолчанию:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Обе существующие строки получат значение 'active'. Значение по умолчанию обязано быть константой — SQLite не позволит использовать CURRENT_TIMESTAMP или любое другое неконстантное выражение в качестве default при ADD COLUMN, потому что движку нужно одно значение, которое можно применить ко всем существующим строкам без вычислений для каждой.

Если нужен NOT NULL без значения по умолчанию, придётся сначала добавить колонку как nullable, заполнить её через UPDATE, а затем пересобрать таблицу, чтобы навесить ограничение. И тут мы плавно подходим к ограничениям.

Чего ALTER TABLE сделать не может

Вещи, которые работают в Postgres или MySQL, но не в SQLite:

Изменить тип колонки (ALTER COLUMN ... TYPE ...).
Изменить значение по умолчанию у существующей колонки.
Добавить или убрать NOT NULL, CHECK, UNIQUE или PRIMARY KEY у существующей колонки.
Добавить внешний ключ к существующей колонке.
Поменять порядок колонок.

Любая из этих попыток приведёт к синтаксической ошибке. В SQLite вообще нет конструкции ALTER COLUMN. Официальный ответ для всех таких случаев один: пересобрать таблицу.

Паттерн пересборки таблицы

Когда ALTER TABLE не справляется, схема меняется так: создаём новую таблицу с нужной структурой, переливаем в неё данные, удаляем старую, а новую переименовываем на её место. Всё это оборачиваем в транзакцию — чтобы либо прошло целиком, либо не прошло вовсе:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

Теперь users.age — это целое число с CHECK-ограничением, а email помечен как NOT NULL. Данные при этом никуда не делись — они переехали вместе со схемой.

Несколько моментов, о которых стоит помнить, когда будете делать это на боевой базе:

Отключайте внешние ключи на время операции. Если на вашу таблицу ссылаются другие, выполните PRAGMA foreign_keys = OFF; перед транзакцией и PRAGMA foreign_keys = ON; после. Иначе DROP TABLE упадёт с ошибкой. Учтите: эту прагму нельзя менять внутри транзакции, поэтому ставьте её снаружи.
Пересоздавайте индексы и триггеры. Когда вы удаляете старую таблицу, её индексы и триггеры исчезают вместе с ней. После переименования навесьте их заново на новую таблицу.
Проверьте представления (VIEW). Представления, которые ссылаются на таблицу, всё ещё хранят старое имя в своём SQL. Те из них, что зависят от изменённых колонок, придётся пересобрать.

Такой паттерн пересборки таблицы получается громоздким, зато надёжным. Именно так миграционные инструменты вроде Alembic и Rails работают «под капотом», когда им нужно изменить схему в SQLite.

Как удалить несколько таблиц SQLite

Отдельной команды для удаления нескольких таблиц сразу в SQLite нет — DROP TABLE придётся выполнить для каждой. Если хочется сгруппировать их в одну операцию, оберните всё в транзакцию:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Если завернуть всё это в транзакцию, то либо все три DROP пройдут, либо ни один — удобно, когда сносишь связанные таблицы и боишься застрять на полпути из-за внешних ключей.

Что стоит запомнить

DROP TABLE сносит таблицу вместе с её индексами и триггерами. Для идемпотентных скриптов добавляйте IF EXISTS.
ALTER TABLE в SQLite умеет ровно четыре вещи: переименовать таблицу, переименовать колонку, добавить колонку, удалить колонку.
Всё остальное — смена типа, новые ограничения, внешние ключи на уже существующих колонках — делается через пересборку таблицы внутри транзакции.
При пересборке не забывайте про внешние ключи, индексы, триггеры и представления. За данными они сами не переедут.

Дальше: наполняем таблицы данными

Целую главу мы возились с таблицами и ограничениями, которые задают им форму. Пора положить туда что-нибудь полезное — следующая глава начинается с INSERT: вставка нескольких строк за раз, значения по умолчанию и то, как SQLite ведёт себя, когда вставка конфликтует с вашими ограничениями.

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

Как удалить таблицу в SQLite?

Пишем DROP TABLE имя_таблицы;. Чтобы запрос не падал, если таблицы уже нет, добавьте IF EXISTS: DROP TABLE IF EXISTS users;. Вместе с таблицей улетают её индексы и триггеры. И учтите: если включены внешние ключи, удаление не пройдёт, пока на таблицу ссылаются другие.

Что вообще умеет ALTER TABLE в SQLite?

Всего четыре операции: RENAME TO — переименовать таблицу, RENAME COLUMN ... TO ... — переименовать колонку, ADD COLUMN — добавить новый столбец и DROP COLUMN — удалить столбец (доступно с SQLite 3.35). И всё. Поменять тип колонки, изменить значение по умолчанию или навесить ограничение на уже существующий столбец нельзя.

Как тогда поменять тип или ограничения колонки в SQLite?

Напрямую — никак, такой возможности просто нет. Стандартный приём — пересборка таблицы: создаём новую таблицу с нужной схемой, переливаем данные через INSERT INTO new SELECT ... FROM old, затем DROP TABLE old и ALTER TABLE new RENAME TO old. Всю эту последовательность оборачивайте в транзакцию, чтобы операция была атомарной.

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

SQLite DROP TABLE и ALTER TABLE: меняем схему