ALTER TABLE

1. Обзор

alter-table-stmt: скрыть

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

2. ALTER TABLE RENAME

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

 Compatibility Note: The behavior of ALTER TABLE when renaming a table was enhanced in versions 3.25.0 (2018-09-15) and 3.26.0 (2018-12-01) in order to carry the rename operation forward into triggers and views that reference the renamed table. This is considered an improvement. Applications that depend on the older (and arguably buggy) behavior can use the PRAGMA legacy_alter_table=ON statement or the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE configuration parameter on sqlite3_db_config() interface to make ALTER TABLE RENAME behave as it did prior to version 3.25.0. 

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

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

PRAGMA foreign_keys	PRAGMA legacy_alter_table	Ссылки на родительскую таблицу обновляются	Версия SQLite
Выкл.	Выкл.	Нет	< 3.26.0
Выкл.	Выкл.	Да	>= 3.26.0
Вкл.	Выкл.	Да	все
Выкл.	Вкл.	Нет	все
Вкл.	Вкл.	Да	все

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, со следующими ограничениями:

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

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

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

После выполнения ADD COLUMN база данных не будет читаться SQLite версии 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. Все команды ALTER TABLE изменяют этот текст, а затем пытаются заново разобрать всю схему. Команда выполняется успешно только в том случае, если после изменения текста схема остаётся корректной. При выполнении команды DROP COLUMN изменяется только текст определения столбца: он удаляется из оператора CREATE TABLE. Команда DROP COLUMN завершается ошибкой, если в других частях схемы остаются упоминания столбца, из-за которых схема не сможет быть разобрана после изменения оператора CREATE TABLE.

6. Отключение проверки ошибок с помощью PRAGMA writable_schema=ON

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

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

7. Внесение других изменений в схему таблицы

SQLite напрямую поддерживает только приведённые выше команды изменения схемы: «переименовать таблицу», «переименовать столбец», «добавить столбец» и «удалить столбец». Однако приложения могут произвольно изменять формат таблицы, выполняя простую последовательность операций. Чтобы произвольно изменить схему таблицы 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. Если ограничения внешних ключей были включены изначально, снова включите их.

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

Приведённая выше универсальная процедура 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, триггеры, индексы и представления, ссылающиеся на этот столбец.

   Внимание: И снова: такие изменения таблицы sqlite_schema приведут к повреждению базы данных и сделают её нечитаемой, если изменение содержит ошибку. Тщательно проверьте всю эту процедуру на отдельной тестовой базе данных, прежде чем использовать её для базы данных с важными данными, и/или создайте резервные копии важных баз данных перед её выполнением.

6. Увеличьте номер версии схемы с помощью PRAGMA schema_version=X, где X на единицу больше прежнего номера версии схемы, полученного на шаге 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 являются рекомендуемым форматом хранения для долгосрочного архивирования данных.

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

Последнее обновление этой страницы: 2025-11-13 07:12:58Z