1. Обзор

alter-table-stmt: hide

SQLite понимает ограниченное подмножество ALTER TABLE. ALTER TABLE в SQLite позволяет эти изменения существующей таблицы: переименование таблицы, переименование столбца, добавление/удаление столбца.

2. ALTER TABLE RENAME

RENAME TO меняет имя table-name на new-table-name. Эта команда не может использоваться, чтобы переместить таблицу между приложенными базами данных, только переименовать таблицу в той же самой базе данных. Если у переименовываемой таблицы есть триггеры или индексы, то они остаются приложенными к таблице после того, как это было переименовано.

Совместимость: поведение ALTER TABLE, переименовывая таблицу, было переделано в версиях 3.25.0 (2018-09-15) и 3.26.0 (2018-12-01), чтобы внести операцию переименования триггеров и обзоров. Это считают улучшением. Приложения, которые зависят от более старого (и возможно багованного) поведения, могут использовать PRAGMA legacy_alter_table=ON или параметр конфигурации SQLITE_DBCONFIG_LEGACY_ALTER_TABLE в sqlite3_db_config(), чтобы ALTER TABLE RENAME вел себя так, как это делалось до версии 3.25.0.

Начиная с 3.25.0 (2018-09-15), ссылки на таблицу в телах триггеров и определениях представлений также переименованы.

До version 3.26.0 (2018-12-01) ссылки FOREIGN KEY на переименовываемую таблицу были отредактированы только, если PRAGMA foreign_keys=ON или другими словами если ограничения внешнего ключа проводились в жизнь. С PRAGMA foreign_keys=OFF ограничения FOREIGN KEY не были бы изменены, когда таблица, в которой был упомянутый внешний ключ "parent table"), переименована. Начиная с version 3.26.0, ограничения FOREIGN KEY всегда преобразовываются, когда таблица переименована, если PRAGMA legacy_alter_table=ON . Следующая таблица суммирует различия:

PRAGMA foreign_keys	PRAGMA legacy_alter_table	Ссылки Parent Table обновлены	Версия SQLite
Off	Off	Нет	< 3.26.0
Off	Off	Да	>= 3.26.0
On	Off	Да	Все
Off	On	Нет	Все
On	On	Да	Все

3. ALTER TABLE RENAME COLUMN

RENAME COLUMN TO меняет column-name таблицы table-name на new-column-name. Имя столбца изменяется в рамках самого определения таблицы и также во всех индексах, триггерах и обзорах, которые на этот столбец ссылаются. Если изменение имени столбца привело бы к семантической двусмысленности в триггере или обзоре, RENAME COLUMN зканечивается ошибкой и никакие изменения не применяются.

4. ALTER TABLE ADD COLUMN

ADD COLUMN используется, чтобы добавить новую колонку к существующей таблице. Новая колонка всегда прилагается в конце списка существующих столбцов. Правило column-def определяет особенности новой колонки. Новая колонка может принять любую из форм, допустимых в CREATE TABLE, но:

• У колонки может не быть ограничения UNIQUE или PRIMARY KEY.
• У колонки может не быть значения по умолчанию CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP или выражения в круглых скобках.
• Если ограничение NOT NULL определяется, то у колонки должно быть значение по умолчанию кроме NULL.
• Если ограничения внешнего ключа enabled и добавляется колонка с REFERENCES, у колонки должно быть значение по умолчанию NULL.
• Колонка не может быть GENERATED ALWAYS ... STORED , хотя колонки VIRTUAL позволены.

Добавляя колонку с ограничением CHECK или NOT NULL в произведенной колонке, добавленные ограничения проверены против всех существующих ранее строк в таблице, и ADD COLUMN провалится, если какое-либо ограничение терпит неудачу. Тестирование добавленных ограничений против существующих ранее строк это новое улучшение с версии SQLite version 3.37.0 (2021-11-27).

ALTER TABLE работает, изменяя код SQL схемы, сохраненной в таблице sqlite_schema. Никакие изменения не внесены, чтобы составить содержание таблицы для переименования или дополнения колонки без ограничений. Из-за этого время выполнения таких команд ALTER TABLE независимо от объема данных в таблице. Добавляя новые колонки, у которых есть ограничения CHECK или добавление произведенных колонок с ограничениями NOT NULL, или удаляя колонки, тогда все существующие данные в таблице должны быть прочитаны (чтобы проверить новые ограничения против существующих строк) или написаны (чтобы удалить удаленные колонки). В таких случаях команда ALTER TABLE занимает время, которое пропорционально сумме содержания в измененной таблице.

После ADD COLUMN на базе данных, та база данных не будет читаемой версией SQLite version 3.1.3 (2005-02-20) и ранее.

5. ALTER TABLE DROP COLUMN

DROP COLUMN используется, чтобы удалить существующий столбец из таблицы. DROP COLUMN удаляет названную колонку из таблицы и переписывает содержание, чтобы произвести чистку данных, связанных с той колонкой. Команда DROP COLUMN работает работает если на колонку не ссылаются никакие другие части схемы, она не является PRIMARY KEY и нет ограничения UNIQUE. Возможные причины, почему DROP COLUMN провалилась:

• Колонка PRIMARY KEY или его часть.
• Есть ограничение UNIQUE.
• Колонка внесена в индекс.
• Колонку называют в операторе Where частичного индекса.
• Колонку называют в ограничении CHECK таблицы или колонки, не связанном с удаляемой колонкой.
• Колонка используется в ограничении внешнего ключа.
• Колонка используется в выражении произведенной колонки.
• Колонка появляется в триггере или обзоре.

5.1. Как это работает

SQLite хранит схему как простой текст в таблице sqlite_schema. DROP COLUMN (и все другие изменения ALTER TABLE также) изменяет тот текст и затем пытается повторно разобрать всю схему. Команда успешна только, если схема все еще действительна после того, как текст был изменен. В случае DROP COLUMN единственный измененный текст это то, что определение столбца удалено из CREATE TABLE. DROP COLUMN провалится, если будут какие-либо следы колонки в других частях схемы, которая будет препятствовать тому, чтобы схема разобралась после изменения CREATE TABLE.

6. Отключите проверку на ошибки, используя PRAGMA writable_schema=ON

ALTER TABLE будет обычно проваливаться и не вносить изменений, если он столкнется с какими-либо записями в таблице sqlite_schema, которые не разбираются. Например, если будет неправильный VIEW или TRIGGER, связанный с таблицей "tbl1", то попытка переименовать "tbl1" в "tbl1neo" потерпит неудачу, потому что связанные обзоры и триггеры не могли быть разобраны.

Начиная с SQLite 3.38.0 (2022-02-22), эта проверка на ошибки может быть отключена, установив "PRAGMA writable_schema=ON;". Когда схема перезаписываема, ALTER TABLE тихо игнорирует любые строки таблицы sqlite_schema, которые не разбираются.

7. Создание других видов изменений схемы

Единственные команды изменения схемы, непосредственно поддержанные SQLite, "rename table", "rename column", "add column", "drop column". Однако, запросы могут внести другие произвольные изменения в формат таблицы, используя простую последовательность операций. Шаги, чтобы внести произвольные изменения в дизайн схемы некоторой таблицы X следующие:

1. Если ограничения внешнего ключа позволены, выключите их, используя PRAGMA foreign_keys=OFF .

2. Начните транзакцию.

3. Помните формат всех индексов, триггеров и обзоров, связанных с таблицей X. Эта информация будет необходима в шаге 8 ниже. Один способ сделать это: выполнить такой запрос SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'.

4. Используйте CREATE TABLE, чтобы построить новую таблицу "new_X", которая находится в желаемом пересмотренном формате таблицы X. Удостоверьтесь, что имя "new_X" не сталкивается ни с каким существующим именем таблицы, конечно.

5. Скопируйте содержание X в new_X командой INSERT INTO new_X SELECT ... FROM X.

6. Удалите старую таблицу X: DROP TABLE X.

7. Поменяйте имя new_X на X: ALTER TABLE new_X RENAME TO X.

8. Используйте CREATE INDEX, CREATE TRIGGER и CREATE VIEW, чтобы восстановить индексы, триггеры и обзоры, связанные с таблицей X. Возможно, используйте старый формат триггеров, индексов и обзоровот шага 3 выше как гид, внося изменения как подходящие по смыслу.

9. Если какие-либо обзоры обращаются к таблице X способом, который затронут изменением схемы, то удалите эти обзоры, используя DROP VIEW и воссоздайте их с любыми изменениями, необходимыми, чтобы приспособить изменение схемы, используя CREATE VIEW.

10. Если ограничения внешнего ключа были первоначально позволены, выполните PRAGMA foreign_key_check, чтобы проверить, что изменение схемы не сломало ограничений внешнего ключа.

11. Передайте транзакцию, начатую в шаге 2.

12. Если ограничения внешних ключей были первоначально позволены, повторно включите их теперь.

Важно: надо выполнить процедуру выше точно. Схемы ниже суммируют две процедуры изменения определения таблицы. На первый взгляд они обе, кажется, достигают того же самого. Однако, процедура справа не всегда работает, особенно с расширенным rename table, добавленным в вресиях 3.25.0 и 3.26.0. В процедуре справа, начальный rename переименует таблицу к временному имени, мог бы испортить ссылки на эту таблицу. Безопасная процедура слева использует новое временное имя, затем переименовывает таблицу на свое заключительное имя, которое не разрывает связи.

Обобщенная процедура ALTER TABLE с 12 шагами выше будет работать, даже если изменение схемы заставит информацию в таблице изменяться. Таким образом, полная процедура с 12 шагами выше подходит для удаления колонки, изменения порядка колонок, добавления или удаления ограничения UNIQUE или PRIMARY KEY, добавление ограничений CHECK, FOREIGN KEY или NOT NULL или изменения типа данных для колонки, например. Однако, более простая и более быстрая процедура может произвольно использоваться для некоторых изменений, которые не делают никакого влияния на содержание на диске ни в каком случае. Более простая процедура подходит для удаления ограничений CHECK, FOREIGN KEY или NOT NULL, добавления, удаления или изменения значений по умолчанию на колонке.

1. Начните транзакцию.

2. Выполните PRAGMA schema_version, чтобы определить текущий номер версии схемы. Это число будет необходимо для шага 6 ниже.

3. Активируйте редактирование схемы, используя PRAGMA writable_schema=ON .

4. Выполните UPDATE, чтобы изменить определение таблицы X в таблице sqlite_schema: UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';

   Важно: внесение изменения в таблицу sqlite_schema может выдать поврежденную и нечитабельную БД, если изменение будет содержать синтаксическую ошибку. Предложено, чтобы тщательное тестирование UPDATE было сделано на отдельной чистой базе данных до использования его на базе данных, содержащей важные данные.

5. Если изменение таблицы X также затрагивает другие таблицы индексы, триггеры или обзоры в рамках схемы, то нужны UPDATE, чтобы изменить те другие объекты также. Например, если название колонки изменяется, должны быть изменены все ограничения FOREIGN KEY, триггеры, обзоры и индексы, которые обращаются к той колонке.

6. Увеличьте номер версии схемы, используя PRAGMA schema_version=X, где X на 1 больше, чем старый номер версии схемы, найденный в шаге 2 выше.

7. Отключите редактирование схемы, используя PRAGMA writable_schema=OFF .

8. Необязательно. Выполните PRAGMA integrity_check, чтобы проверить, что изменения схемы не повредили базу данных.

9. Передайте транзакцию, начатую на шаге 1 выше.

Если некоторая будущая версия SQLite добавит новые возможности ALTER TABLE, те возможности будут очень вероятно использовать одну из этих двух процедур, обрисованных в общих чертах выше.

8. Почему ALTER TABLE такая проблема для SQLite

Большинство СУБД SQL хранит схему, уже размеченную, в различных системных таблицах. На тех ядрах базы данных ALTER TABLE просто должен сделать модификации к соответствующим системным таблицам.

SQLite отличается в этом, он хранит схему в таблице sqlite_schema как исходный текст CREATE, которые определяют схему. Следовательно, ALTER TABLE должен пересмотреть текст CREATE. Это может быть коварно для определенных "творческих" проектов схемы.

У подхода SQLite хранения схемы как текста есть преимущества для вложенной реляционной базы данных. Это означает, что схема занимает меньше места в файле базы данных. Это важно, так как у общего образца использования SQLite должно быть много маленьких, отдельных файлов базы данных вместо того, чтобы поместить все в один большой глобальный файл базы данных, что является обычным подходом для ядер базы данных клиент-сервер. Так как схема дублирована в каждом отдельном файле базы данных, важно сохранять представление схемы компактным.

Хранение схемы как текста, а не как таблицы также дает гибкость внедрению. Так как внутренний разбор схемы делается каждый раз, когда база данных открыта, внутреннее представление схемы может измениться между версиями. Это важно, поскольку иногда новые особенности требуют улучшений к внутреннему представлению схемы. Изменение внутреннего представления схемы было бы намного более трудным, если бы представление схемы было выставлено в файле базы данных. Так, другими словами храня схему как текст удобней поддержать совместимость и это помогает гарантировать, что более старые файлы базы данных могут быть прочитаны и написаны более новыми версиями SQLite.

Хранение схемы как текст также делает формат файла базы данных SQLite легче определить, зарегистрировать, и понять. Это помогает сделать файлы базы данных SQLite рекомендуемым форматом хранения для долгосрочного архивирования данных.

Оборотная сторона хранения схемы как текста в том, что это может сделать схему хитрой в изменении. И по этой причине поддержка ALTER TABLE в SQLite традиционно отстала от других СУБД SQL, которые хранят схемы как системные таблицы, которые легче изменить.