Когда работаешь с SQLite, рано или поздно сталкиваешься с тем, что структуру таблицы нужно изменить — переименовать, добавить столбец или убрать лишнее поле. Я разберу все три сценария и покажу обходной путь для операций, которые SQLite не поддерживает напрямую. Возможности ALTER TABLE здесь намеренно ограничены: движок допускает только переименование таблиц, добавление столбцов и переименование столбцов
С помощью оператора SQLite ALTER TABLE можно выполнить три действия:
- Переименовать таблицу
- Добавить новый столбец в таблицу
- Переименовать столбец (поддержка добавлена в версии 3.20.0)
Переименование таблицы через ALTER TABLE
Чтобы переименовать таблицу, используется оператор ALTER TABLE RENAME TO:
ALTER TABLE existing_table RENAME TO new_table;Перед переименованием таблицы стоит учесть несколько важных моментов:
ALTER TABLEпереименовывает таблицу только внутри базы данных — этот оператор нельзя использовать для перемещения таблицы между подключёнными базами данных- Объекты базы данных, такие как индексы и триггеры, связанные с таблицей, будут автоматически привязаны к новой таблице
- Если на таблицу ссылаются представления (views) или операторы в триггерах, необходимо вручную изменить определения этих представлений и триггеров
Рассмотрим пример переименования таблицы
Во-первых, создадим таблицу с именем devices, содержащую три столбца: name, model, serial, и вставим новую строку:
CREATE TABLE devices ( name TEXT NOT NULL, model TEXT NOT NULL, Serial INTEGER NOT NULL UNIQUE
);
INSERT INTO devices (name, model, serial)
VALUES ('HP ZBook 17 G3 Mobile Workstation', 'ZBook', 'SN-2015');Во-вторых, используем оператор ALTER TABLE RENAME TO, чтобы переименовать таблицу devices в equipment:
ALTER TABLE devices RENAME TO equipment;В-третьих, выполним запрос к таблице equipment, чтобы проверить результат операции RENAME:
SELECT name, model, serial FROM equipment;По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы
Добавление нового столбца через ALTER TABLE
Добавление нового столбца производится с помощью оператора ALTER TABLE, и новый столбец всегда оказывается в конце списка. Он не может иметь ограничение UNIQUE или PRIMARY KEY — это стоит учитывать при проектировании схемы данных
Синтаксис оператора ALTER TABLE ADD COLUMN:
ALTER TABLE table_name
ADD COLUMN column_definition;На новый столбец распространяется ряд ограничений:
- Новый столбец не может иметь ограничение
UNIQUEилиPRIMARY KEY - Если новый столбец имеет ограничение
NOT NULL, необходимо указать для него значение по умолчанию, отличное отNULL - Новый столбец не может иметь значение по умолчанию
CURRENT_TIMESTAMP,CURRENT_DATE,CURRENT_TIMEили выражение - Если новый столбец является внешним ключом (foreign key) и проверка ограничения внешнего ключа включена, новый столбец должен принимать значение по умолчанию
NULL
Например, добавим новый столбец location в таблицу equipment:
ALTER TABLE equipment
ADD COLUMN location TEXT;На моём опыте чаще всего ошибки возникают именно при добавлении столбца с NOT NULL без значения по умолчанию — проверяйте это заранее, чтобы не получить ошибку во время выполнения
Переименование столбца через ALTER TABLE
Поддержка переименования столбца с помощью оператора ALTER TABLE RENAME COLUMN появилась в версии 3.20.0
Синтаксис оператора ALTER TABLE RENAME COLUMN:
ALTER TABLE table_name
RENAME COLUMN current_name TO new_name;Если у вас старая версия SQLite, эта функция недоступна, и потребуется воспользоваться обходным путём через пересоздание таблицы
Удаление столбца в SQLite: обходной путь
Если нужно выполнить действия, которые ALTER TABLE напрямую не поддерживает — например, удалить столбец — используется следующий обходной путь:
- Отключить проверку ограничения внешнего ключа
- Начать новую транзакцию
- Создать новую таблицу с нужной структурой
- Скопировать данные из старой таблицы в новую
- Удалить старую таблицу
- Переименовать новую таблицу в имя старой
- Зафиксировать транзакцию
- Включить проверку ограничения внешнего ключа
Пример: удаление столбца через пересоздание таблицы
SQLite не поддерживает оператор ALTER TABLE DROP COLUMN напрямую. Чтобы удалить столбец, необходимо выполнить шаги, описанные выше
Следующий скрипт создаёт две таблицы — users и favorites — и вставляет данные в них:
CREATE TABLE users ( UserId INTEGER PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL, Email TEXT NOT NULL, Phone TEXT NOT NULL
);
CREATE TABLE favorites ( UserId INTEGER, PlaylistId INTEGER, FOREIGN KEY (UserId) REFERENCES users(UserId), FOREIGN KEY (PlaylistId) REFERENCES playlists(PlaylistId)
);
INSERT INTO users (FirstName, LastName, Email, Phone)
VALUES ('John', 'Doe', 'john.doe@example.com', '408-234-3456');
INSERT INTO favorites (UserId, PlaylistId)
VALUES (1, 1);Следующий оператор возвращает данные из таблицы users:
SELECT * FROM users;А следующий оператор возвращает данные из таблицы favorites:
SELECT * FROM favorites;Предположим, нужно удалить столбец phone из таблицы users
Во-первых, отключим проверку ограничения внешнего ключа:
PRAGMA foreign_keys = off;Во-вторых, начнём новую транзакцию:
BEGIN TRANSACTION;В-третьих, создадим новую таблицу для хранения данных таблицы users без столбца phone:
CREATE TABLE IF NOT EXISTS persons ( UserId INTEGER PRIMARY KEY, FirstName TEXT NOT NULL, LastName TEXT NOT NULL, Email TEXT NOT NULL
);В-четвёртых, скопируем данные из таблицы users в таблицу persons:
INSERT INTO persons (UserId, FirstName, LastName, Email)
SELECT UserId, FirstName, LastName, Email
FROM users;В-пятых, удалим таблицу users:
DROP TABLE users;В-шестых, переименуем таблицу persons в users:
ALTER TABLE persons RENAME TO users;В-седьмых, зафиксируем транзакцию:
COMMIT;В-восьмых, включим проверку ограничения внешнего ключа:
PRAGMA foreign_keys = on;После выполнения этих шагов таблица users будет содержать все исходные данные, но уже без столбца phone. На практике я всегда оборачиваю подобные операции в транзакцию — это позволяет откатить изменения, если что-то пойдёт не так. Мой совет — дополнительно делать резервную копию базы перед любыми структурными изменениями такого рода
- Используйте оператор
ALTER TABLEдля изменения структуры существующей таблицы- Используйте
ALTER TABLE table_name RENAME TO new_nameдля переименования таблицы- Используйте
ALTER TABLE table_name ADD COLUMN column_definitionдля добавления столбца в таблицу- Используйте
ALTER TABLE table_name RENAME COLUMN current_name TO new_nameдля переименования столбца- Для удаления столбца используйте обходной путь: создайте новую таблицу, скопируйте данные, удалите старую таблицу и переименуйте новую
Ответы на эти вопросы могут быть для вас полезными
Можно ли удалить столбец в SQLite с помощью ALTER TABLE? Нет, SQLite не поддерживает ALTER TABLE DROP COLUMN напрямую. Для удаления столбца нужно создать новую таблицу с нужной структурой, скопировать данные, удалить старую таблицу и переименовать новую
Можно ли переименовать столбец в старых версиях SQLite? Нет. Оператор ALTER TABLE RENAME COLUMN появился только в версии 3.20.0. В более ранних версиях переименование столбца выполняется через пересоздание таблицы
Что происходит с индексами и триггерами при переименовании таблицы? Индексы и триггеры, связанные с таблицей, автоматически привязываются к новому имени. Однако представления (views) и ссылки внутри триггеров нужно обновлять вручную
Можно ли добавить столбец с ограничением NOT NULL без значения по умолчанию? Нет. Если новый столбец имеет ограничение NOT NULL, необходимо указать значение по умолчанию, отличное от NULL, иначе SQLite вернёт ошибку
Зачем отключать проверку внешних ключей при пересоздании таблицы? При удалении старой таблицы и переименовании новой SQLite может временно нарушить целостность ссылок. Отключение PRAGMA foreign_keys на время операции позволяет избежать ошибок, а после завершения всех шагов проверку нужно снова включить
