ИЗМЕНЕНИЕ ТАБЛИЦЫ

1. Обзор

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

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

2. Переименование таблицы

Синтаксис 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 не используется. Следующая таблица обобщает разницу:

...

3. Переименование столбца

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

4. Добавление столбца

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

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

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

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

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

5. Удаление столбца

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

• Столбец является первичным ключом или частью первичного ключа.
• Столбец имеет ограничение 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, которые не могут быть проанализированы. Например, если существует неправильно сформированное представление или триггер, связанный с таблицей под названием "tbl1", то попытка переименования "tbl1" в "tbl1neo" завершится неудачей, так как связанные представления и триггеры не могут быть проанализированы.

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

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

Единственными командами изменения схемы, напрямую поддерживаемыми SQLite, являются команды "переименование таблицы", "переименование столбца", "добавление столбца", "удаление столбца", показанные выше. Однако приложения могут вносить другие произвольные изменения в формат таблицы с помощью простого набора операций.

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. В процедуре справа начальное переименование таблицы во временное имя может привести к повреждению ссылок на эту таблицу в триггерах, представлениях и ограничениях внешнего ключа. Безопасная процедура слева создает переработанное определение таблицы с помощью нового временного имени, затем переименовывает таблицу в её конечное имя, что не приводит к разрыву связей.

12-шаговая обобщённая процедура ALTER TABLE выше будет работать даже если изменение схемы приводит к изменению данных, хранящихся в таблице. Поэтому полная 12-шаговая процедура выше подходит для удаления столбца, изменения порядка столбцов, добавления или удаления ограничения UNIQUE или PRIMARY KEY, добавления ограничений 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, чтобы изменить эти другие таблицы индексы и представления тоже. Например, если имя столбца изменяется, все ограничения внешнего ключа, триггеры, индексы и представления, которые ссылаются на этот столбец, должны быть изменены.

   Внимание: Ещё раз, внесение изменений в таблицу 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

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

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

Подход SQLite к хранению схемы в виде текста имеет преимущества для встроенной реляционной базы данных. Во-первых, это означает, что схема занимает меньше места в файле базы данных. Это важно, поскольку распространённым шаблоном использования SQLite является наличие многих небольших отдельных файлов базы данных вместо размещения всего в одном большом глобальном файле базы данных, что является обычным подходом для СУБД клиент-сервер.

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

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

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

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