Sqlite как изменить название столбца

1. Overview

alter-table-stmt:

SQLite supports a limited subset of ALTER TABLE.
The ALTER TABLE command in SQLite allows
these alterations of an existing table:
it can be renamed;
a column can be renamed;
a column can be added to it;
or
a column can be dropped from it.

2. ALTER TABLE RENAME

The RENAME TO syntax changes the name of table-name
to new-table-name.
This command
cannot be used to move a table between attached databases, only to rename
a table within the same database.
If the table being renamed has triggers or indices, then these remain
attached to the table after it has been renamed.

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.

Beginning with release 3.25.0 (2018-09-15), references to the table
within trigger bodies and view definitions are also renamed.

Prior to version 3.26.0 (2018-12-01), FOREIGN KEY references
to a table that is renamed were only edited if the
PRAGMA foreign_keys=ON, or in other words if
foreign key constraints were being enforced. With
PRAGMA foreign_keys=OFF, FOREIGN KEY constraints would not be changed
when the table that the foreign key referred to (the «parent table»)
was renamed. Beginning with version 3.26.0, FOREIGN KEY constraints
are always converted when a table is renamed, unless the
PRAGMA legacy_alter_table=ON setting is engaged. The following
table summarizes the difference:

PRAGMA foreign_keys	PRAGMA legacy_alter_table	Parent Table references are updated	SQLite version
Off	Off	No	< 3.26.0
Off	Off	Yes	>= 3.26.0
On	Off	Yes	all
Off	On	No	all
On	On	Yes	all

3. ALTER TABLE RENAME COLUMN

The RENAME COLUMN TO syntax changes the
column-name of table table-name
into new-column-name. The column name is changed both
within the table definition itself and also within all indexes, triggers,
and views that reference the column. If the column name change would
result in a semantic ambiguity in a trigger or view, then the RENAME
COLUMN fails with an error and no changes are applied.

4. ALTER TABLE ADD COLUMN

The ADD COLUMN syntax
is used to add a new column to an existing table.
The new column is always appended to the end of the list of existing columns.
The column-def rule defines the characteristics of the new column.
The new column may take any of the forms permissible in a CREATE TABLE
statement, with the following restrictions:

• The column may not have a PRIMARY KEY or UNIQUE constraint.
• The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
  CURRENT_TIMESTAMP, or an expression in parentheses.
• If a NOT NULL constraint is specified, then the column must have a
  default value other than NULL.
• If foreign key constraints are enabled and
  a column with a REFERENCES clause
  is added, the column must have a default value of NULL.
• The column may not be GENERATED ALWAYS … STORED,
  though VIRTUAL columns are allowed.

When adding a column with a CHECK constraint, or a NOT NULL constraint
on a generated column, the added constraints are tested against all
preexisting rows in the table and the ADD COLUMN fails
if any constraint fails. The testing of added constraints
against preexisting rows is a new enhancement as of SQLite version
3.37.0 (2021-11-27).

The ALTER TABLE command works by modifying the SQL text of the schema
stored in the sqlite_schema table.
No changes are made to table content for renames or column addition without
constraints.
Because of this,
the execution time of such ALTER TABLE commands is independent of
the amount of data in the table and such commands will
run as quickly on a table with 10 million rows as on a table with 1 row.
When adding new columns that have CHECK constraints, or adding generated
columns with NOT NULL constraints, or when deleting columns, then all
existing data in the table must be either read (to test new constraints
against existing rows) or written (to remove deleted columns). In those
cases, the ALTER TABLE command takes time that is proportional to the
amount of content in the table being altered.

After ADD COLUMN has been run on a database, that database will not
be readable by SQLite version 3.1.3 (2005-02-20) and earlier.

5. ALTER TABLE DROP COLUMN

The DROP COLUMN syntax
is used to remove an existing column from a table.
The DROP COLUMN command removes the named column from the table,
and rewrites its content to purge the data associated
with that column.
The DROP COLUMN command only works if the column is not referenced
by any other parts of the schema and is not a PRIMARY KEY and
does not have a UNIQUE constraint.
Possible reasons why the DROP COLUMN command can fail include:

• The column is a PRIMARY KEY or part of one.
• The column has a UNIQUE constraint.
• The column is indexed.
• The column is named in the WHERE clause of a partial index.
• The column is named in a table or column CHECK constraint
  not associated with the column being dropped.
• The column is used in a foreign key constraint.
• The column is used in the expression of a generated column.
• The column appears in a trigger or view.

5.1. How It Works

SQLite stores the schema as plain text in the sqlite_schema table.
The DROP COLUMN command (and all of the other variations of ALTER TABLE
as well) modify that text and then attempt to reparse the entire schema.
The command is only successful if the schema is still valid after the
text has been modified. In the case of the DROP COLUMN command, the
only text modified is that the column definition is removed from the
CREATE TABLE statement. The DROP COLUMN command will fail if there
are any traces of the column in other parts of the schema that will
prevent the schema from parsing after the CREATE TABLE statement has
been modified.

6. Disable Error Checking Using PRAGMA writable_schema=ON

ALTER TABLE will normally fail and make no changes if it encounters
any entries in the sqlite_schema table that do not parse. For
example, if there is a malformed VIEW or TRIGGER associated with
table named «tbl1», then an attempt to rename «tbl1» to «tbl1neo» will
fail because the associated views and triggers could not be parsed.

Beginning with SQLite 3.38.0 (2022-02-22), this error checking
can be disabled by setting «PRAGMA writable_schema=ON;». When
the schema is writable, ALTER TABLE silently ignores any rows of the
sqlite_schema table that do not parse.

7. Making Other Kinds Of Table Schema Changes

The only schema altering commands directly supported by SQLite are the
«rename table», «rename column», «add column», «drop column»
commands shown above. However, applications
can make other arbitrary changes to the format of a table using a simple
sequence of operations.
The steps to make arbitrary changes to the schema design of some table X
are as follows:

1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.

2. Start a transaction.

3. Remember the format of all indexes, triggers, and views associated with table X.
   This information will be needed in step 8 below. One way to do this is
   to run a query like the following:
   SELECT type, sql FROM sqlite_schema WHERE tbl_name=’X’.

4. Use CREATE TABLE to construct a new table «new_X» that is in the desired
   revised format of table X. Make sure that the name «new_X» does not collide
   with any existing table name, of course.

5. Transfer content from X into new_X using a statement
   like: INSERT INTO new_X SELECT … FROM X.

6. Drop the old table X: DROP TABLE X.

7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

8. Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW
   to reconstruct indexes, triggers, and views
   associated with table X. Perhaps use the old format of the triggers,
   indexes, and views saved from step 3 above as a guide, making changes
   as appropriate for the alteration.

9. If any views refer to table X in a way that is affected by the
   schema change, then drop those views using DROP VIEW and recreate them
   with whatever changes are necessary to accommodate the schema change
   using CREATE VIEW.

10. If foreign key constraints were originally enabled
    then run PRAGMA foreign_key_check to verify that the schema
    change did not break any foreign key constraints.

11. Commit the transaction started in step 2.

12. If foreign keys constraints were originally enabled, reenable them now.

Caution:
Take care to follow the procedure above precisely. The boxes below
summarize two procedures for modifying a table definition. At first
glance, they both appear to accomplish the same thing. However, the
procedure on the right does not always work, especially with the
enhanced rename table capabilities added by versions 3.25.0 and
3.26.0. In the procedure on the right, the initial rename of the
table to a temporary name might corrupt references to that table in
triggers, views, and foreign key constraints. The safe procedure on
the left constructs the revised table definition using a new temporary
name, then renames the table into its final name, which does not break
links.

Create new table Copy data Drop old table Rename new into old	Rename old table Create new table Copy data Drop old table
↑ Correct	↑ Incorrect

The 12-step generalized ALTER TABLE procedure
above will work even if the
schema change causes the information stored in the table to change.
So the full 12-step procedure above is appropriate for dropping a column,
changing the order of columns, adding or removing a UNIQUE constraint
or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints,
or changing the datatype for a column, for example. However, a simpler
and faster procedure can optionally be used for
some changes that do no affect the on-disk content in any way.
The following simpler procedure is appropriate for removing
CHECK or FOREIGN KEY or NOT NULL constraints,
or adding, removing, or changing default values on
a column.

1. Start a transaction.

2. Run PRAGMA schema_version to determine the current schema
   version number. This number will be needed for step 6 below.

3. Activate schema editing using
   PRAGMA writable_schema=ON.

4. Run an UPDATE statement to change the definition of table X
   in the sqlite_schema table:
   UPDATE sqlite_schema SET sql=… WHERE type=’table’ AND name=’X’;

   Caution: Making a change to the sqlite_schema table like this will
   render the database corrupt and unreadable if the change contains
   a syntax error. It is suggested that careful testing of the UPDATE
   statement be done on a separate blank database prior to using it on
   a database containing important data.

5. If the change to table X also affects other tables or indexes or
   triggers are views within schema, then run UPDATE statements to modify
   those other tables indexes and views too. For example, if the name of
   a column changes, all FOREIGN KEY constraints, triggers, indexes, and
   views that refer to that column must be modified.

   Caution: Once again, making changes to the sqlite_schema
   table like this will render the database corrupt and unreadable if the
   change contains an error. Carefully test this entire procedure
   on a separate test database prior to using it on
   a database containing important data and/or make backup copies of
   important databases prior to running this procedure.

6. Increment the schema version number using
   PRAGMA schema_version=X where X is one
   more than the old schema version number found in step 2 above.

7. Disable schema editing using
   PRAGMA writable_schema=OFF.

8. (Optional) Run PRAGMA integrity_check to verify that the
   schema changes did not damage the database.

9. Commit the transaction started on step 1 above.

If some future version of SQLite adds new ALTER TABLE capabilities,
those capabilities will very likely use one of the two procedures
outlined above.

8. Why ALTER TABLE is such a problem for SQLite

Most SQL database engines store the schema already parsed into
various system tables. On those database engines, ALTER TABLE merely
has to make modifications to the corresponding system tables.

SQLite is different in that it stores the schema
in the sqlite_schema table as the original text of the CREATE
statements that define the schema. Hence ALTER TABLE needs
to revise the text of the CREATE statement. Doing
so can be tricky for certain «creative» schema designs.

The SQLite approach of storing the schema as text has advantages
for an embedded relational database. For one, it means that the
schema takes up less space in the database file. This is important
since a common SQLite usage pattern is to have many small,
separate database files instead of putting everything in one
big global database file, which is the usual approach for client/server
database engines.
Since the schema is duplicated in each separate database file, it is
important to keep the schema representation compact.

Storing the schema as text rather than as parsed tables also
give flexibility to the implementation. Since the internal parse
of the schema is regenerated each time the database is opened, the
internal representation of the schema can change from one release
to the next. This is important, as sometimes new features require
enhancements to the internal schema representation. Changing the
internal schema representation would be much more difficult if the
schema representation was exposed in the database file. So, in other
words, storing the schema as text helps maintain backwards
compatibility, and helps ensure that older database files can be
read and written by newer versions of SQLite.

Storing the schema as text also makes the
SQLite database file format easier to define, document, and
understand. This helps make SQLite database files a
recommended storage format for long-term archiving of data.

The downside of storing schema a text is that it can make
the schema tricky to modify. And for that reason, the ALTER TABLE
support in SQLite has traditionally lagged behind other SQL
database engines that store their schemas as parsed system tables
that are easier to modify.

This page last modified on 2022-08-10 18:45:48 UTC

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи мы поговорим про модификацию таблиц в базах данных под управлением SQLite. На самом деле возможности SQLite по модификации таблиц: изначально можно только добавить новый столбец в таблицу базы данных и изменить имя таблицы в SQLite, но из этой записи вы узнаете об одном из приемов, который позволит вам делать любые модификации таблиц базы данных SQLite3.

Синтаксис модификации таблицы в базе данных SQLite

SQLite3 позволяет модифицировать и изменять таблицы в базах данных. К сожалению, синтаксис команды ALTER в SQLite не так богат, как в других СУБД. Вообще, для модификации таблиц в SQLite у нас есть две функции: добавить столбец в таблицу и переименовать таблицу. Общий синтаксис модификации таблиц в базах данных SQLite представлен на рисунке ниже.

Для того чтобы сказать SQLite о том, что мы хотим модифицировать таблицу, нам следует использовать команду ALTER TABLE, после чего мы указываем имя таблицы, которую хотим изменить или квалификатор, который говорит SQLite о том, какую таблицу в какой базе данных необходимо модифицировать. А дальше у нас два действия на выбор: мы можем добавить столбец к таблице SQLite3 и мы можем переименовать таблицу в SQLite.

Добавить столбец в таблицу SQLite3: ключевое слово ADD COLUMN

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

Мы создали таблицу со столбцом c0. Давайте теперь попробуем модфицировать таблицу средствами SQLite3.

Для добавления столбца в таблицу SQLite позволяет не писать ключевое слово COLUMN:

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

Обратите внимание: новый столбец SQLite добавляет всегда в конец списка столбцов. Так же заметим, что добавлять новые столбцы можно с полным описанием, как это мы делали при создании таблиц, но тут действуют некоторые ограничения:

• добавляемый столбец не может быть первичным ключом или иметь ограничение UNIQUE;
• столбец, который мы добавляем в таблицу SQLite3 не может иметь в качестве значения по умолчанию CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP или какое-либо составное выражение, которое записывается в скобках;
• если вы хотите задать ограничение NOT NULL, то столбцу, который вы хотите добавить с таблицу SQLite необходимо присвоить значение по умолчанию отличное от NULL;
• если в SQLite включены внешние ключи, то столбец, который вы хотите добавить в таблицу базы данных SQLite3 должен иметь значение по умолчанию.

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

По сути, это все изменения столбцов, которые может делать команда ALTER в SQLite. Давайте теперь убедимся, что столбцы были добавлены в таблицу SQLite и с ними можно работать. Для начала добавим данные в нашу таблицу при помощи команды INSERT:

А теперь получим данные из нашей таблицы при помощи команды SELECT:

Теперь мы убедились, что все изменения таблицы были сделаны, а новые столбцы были успешно добавлены.

Изменение имени таблицы в базе данных SQLite при помощи команды ALTER

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

Мы создали простую таблицу со столбцами a, b, c, d. Теперь давайте модифицируем таблицу командой ALTER:

Убедиться в том, что имя таблицы изменилось, можно SQLite командой .tables. Для изменения имя таблицы можно использовать не только ее имя, но и квалификатор: полное имя таблицы. Давайте изменим имя таблицы SQLite при помощи квалификатора:

Мы изменили имя таблицы, используя квалификатор, замечу, что при указании нового имени его использовать не стоит. На этом можно было бы закончить рассказ про переименование таблиц в SQLite, но есть здесь и несколько тонкостей:

1. Когда вы переименовываете таблицы в SQLite, то триггеры и индексы, которые были закреплены за таблицей за ней и остаются.
2. Но, если внутри триггера использовалось старое имя таблицы, то вам придется удалить старый триггер и создать новый с новым именем таблицы, который использовалось при переименовании.
3. Такая же участь будет и у представлений (просмотров, VIEW). Чтобы VIEW корректно работали после переименования таблиц в базах данных SQLite, вам необходимо их пересоздать.

Вот такие вот неудобства есть при переименовании таблиц в SQlite.

Но, на самом деле не все так печально в SQLite. Мы можем расширить механизм модификации таблиц в SQLite при помощи транзакций и других команд SQLite. Причем транзакции SQLite позволяют нам вносить не только изменения уровня таблицы, но и изменения уровня столбца. Другими словами: мы можем проводить различные модификации. Давайте это разберем.

Как сделать любую модификацию таблицы в базе данных SQLite

Давайте посмотрим на то, как мы можем вносить любые изменения в столбцы таблиц базы данных SQlite3: как можем поменять тип данных столбца в SQLite, как поменять значение по умолчанию для столбца в SQLite и как можно поменять ограничения столбца в SQLite, и даже как переназначить первичный ключ таблицы в SQLite. На самом деле, когда вы делаете такие изменения, вам следует быть осторожным, поскольку при таких изменениях может быть нарушена внутренняя логика и база данных перестанет корректно работать.

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

Второе, что необходимо для изменения столбцов в SQLite–использовать транзакции. Использование транзакций обезопасит вашу базу данных от различных сбоев. О транзакциях мы подробно поговорим в дальнейшем.

Давайте для примера создадим таблицу example:

Вы создали таблицу example, но не указали: ни типы данных для столбцов, ни ограничения, ни первичный ключ, нет у вас тут и индексов и столбцы как-то непонятно называются. Хорошо, что таблица пустая, ее можно просто удалить таблицу из базы данных и создать нормальную с подробным описанием. Однако бывают ситуации, когда в таблице уже несколько тысяч записей, а команда ALTER в SQLite не позволяет: переименовывать столбцы, добавлять индексы в таблицу, изменять первичный ключ, изменять тип данных столбцов, удалять столбцы. Всё, что может команда ALTER – добавить столбец и изменить имя таблицы. Но нам этого мало, поэтому смотрим пример, сначала приведу общий синтаксис.

Давайте разберемся с тем, как нам можно сделать любую модификацию таблицы базы данных в  SQLite (переименовывать столбцы, добавлять индексы в таблицу, изменять первичный ключ, изменять тип данных столбцы, удалять столбцы):

1. Отключаем внешние ключи, если они включены.
2. Запускаем транзакцию командой BEGIN TRANSACTION (кстати, это можно сделать и при помощи команды SAVEPOINT).
3. Изменяем имя существующей таблицы на любое другое.
4. Создаем новую таблицу с аналогичной, подобной, похожей структурой и даем столбцам нужные описания.
5. Добавляем данные в новую таблицу из старой при помощи команды INSERT с подзапросом SELECT.
6. Завершаем транзакцию командой COMMIT, обратите внимание: если вы используете команду ROLLBACK, то изменения не вступят в силу.
7. И затем опять включаем поддержку внешних ключей.

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

В этом учебном пособии вы узнаете, как использовать SQLite оператор ALTER TABLE чтобы добавить столбец, изменить столбец, удалить столбец, переименовать столбец или переименовать таблицу (с синтаксисом и примерами).

Описание

В этом руководстве по SQLite объясняется, как использовать SQLite оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).

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

Синтаксис

Синтаксис добавления столбца в таблицу в SQLite (используя ALTER TABLE):

ALTER TABLE table_name
ADD new_column_name column_definition;

table_name
Имя таблицы для изменения.

new_column_name
Имя нового столбца, добавляемого в таблицу.

column_definition
Тип данных и определение столбца (NULL или NOT NULL и т.д.).

Пример

Рассмотрим пример, который показывает, как добавить столбец в таблицу SQLite с помощью опертора ALTER TABLE.

Например:

Этот SQLite пример ALTER TABLE добавит столбец с именем status в таблицу employees. Он будет создан как столбец, который допускает значения NULL.

Изменить столбец в таблице

Вы не можете использовать оператор ALTER TABLE для изменения столбца в SQLite. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу и скопировать данные в новую таблицу.

Синтаксис

Синтаксис для изменения столбца в таблице в SQLite:

Пример

Давайте рассмотрим пример, который показывает, как изменить столбец в таблице SQLite.

Например, если у нас была таблица employees, в которой был столбец с именем last_name, который был определен как тип данных CHAR:

И мы хотели изменить тип данных поля last_name на VARCHAR, мы могли бы сделать следующее:

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с полем last_name, определенным как тип данных VARCHAR. Затем он вставит все данные из таблицы _employees_old в таблицу employees.

Удалить столбец из таблицы

Вы не можете использовать оператор ALTER TABLE для удаления столбца в таблице. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу и скопировать данные в новую таблицу.

Синтаксис

Синтаксис DROP A COLUMN в таблице в SQLite:

Пример

Давайте рассмотрим пример, который показывает, как удалить столбец в таблице SQLite.

Например, если у нас была таблица employees, которая была определена следующим образом:

И мы хотели удалить столбец с именем hire_date, мы могли бы сделать следующее:

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с удаленным полем hire_date. Затем он вставит все данные (исключая поле hire_date) из таблицы _employees_old в таблицу employees.

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

Вы не можете использовать оператор ALTER TABLE для переименования столбца в SQLite. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу и скопировать данные в новую таблицу.

Синтаксис

Синтаксис RENAME A COLUMN в таблице в SQLite:

Пример

Давайте рассмотрим пример, который показывает, как переименовать столбец в таблице SQLite.

Например, если у нас была таблица employees, которая была определена следующим образом:

И мы хотели переименовать поле hire_date в start_date, мы могли бы сделать следующее:

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с полем hire_date с именем start_date. Затем он вставит все данные из таблицы _employees_old в таблицу employees.

Переименовать таблицу

Синтаксис

Синтаксис SQLite для того, чтобы переименовать таблицу ALTER TABLE:

ALTER TABLE table_name
RENAME TO new_table_name;

table_name
Таблица для переименования.

new_table_name
Новое имя таблицы.

Пример

Давайте рассмотрим пример, который показывает, как переименовать таблицу в SQLite с помощью оператора ALTER TABLE.

Например:

Этот пример ALTER TABLE переименует таблицу employees в staff.

Узнайте как использовать оператор ALTER TABLE в распространенных БД:

• ALTER TABLE Oracle PL/SQL
• ALTER TABLE SQL Server
• ALTER TABLE MySQL
• ALTER TABLE PostgreSQL
• ALTER TABLE MariaDB

1. Overview

alter-table-stmt:

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

2. ALTER TABLE RENAME

Синтаксис RENAME TO изменяет имя таблицы на новое имя таблицы.Данная команда не может быть использована для перемещения таблицы между подключенными БД,а только для переименования таблицы внутри одной и той же БД.Если переименовываемая таблица имеет триггеры или индексы,то после переименования они остаются прикрепленными к таблице.

 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 version
Off	Off	No	< 3.26.0
Off	Off	Yes	>= 3.26.0
On	Off	Yes	all
Off	On	No	all
On	On	Yes	all

3.ИЗМЕНИТЬ ТАБЛИЦУ ПЕРЕИМЕНОВАТЬ СТОЛБЕЦ

Синтаксис RENAME COLUMN TO изменяет название столбца таблицы на новое название столбца.Имя столбца изменяется как внутри самого определения таблицы,так и внутри всех индексов,триггеров и представлений,на которые ссылается столбец.Если изменение имени столбца приведет к семантической неоднозначности в триггере или представлении,то ОГРАНИЧЕНИЕ РЕМЕНА не удается с ошибкой и никакие изменения не применяются.

4.ИЗМЕНИТЬ ТАБЛИЦУ ДОБАВИТЬ СТОЛБЕЦ

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

• Столбец может не иметь ограничения PRIMARY KEY или UNIQUE.
• Столбец может не иметь значения по умолчанию CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP или выражения в круглых скобках.
• Если указано ограничение NOT NULL,то столбец должен иметь значение по умолчанию,отличное от NULL.
• Если ограничения внешнего ключа являются включены и столбцом с пунктом ЛИТЕРАТУРЫ добавляются столбец должен иметь значение по умолчанию NULL.
• Столбец не может СОЗДАТЬ ВСЕГДА … ХРАНИТЬ , хотя ВИРТУАЛЬНЫЕ столбцы разрешены.

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

Команда ALTER TABLE работает путем изменения текста SQL схемы, хранящейся в таблице sqlite_schema . В содержимое таблицы не вносятся изменения для переименования или добавления столбцов без ограничений. Из-за этого время выполнения таких команд ALTER TABLE не зависит от количества данных в таблице, и такие команды будут выполняться так же быстро для таблицы с 10 миллионами строк, как и для таблицы с 1 строкой. При добавлении новых столбцов с ограничениями 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 . Команда 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 (22 февраля 2022 г.), эту проверку ошибок можно отключить, установив « 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,используя оператор like:INSERT INTO new_X SELECT …FROM X.

6. Отбросьте старые таблицы X: DROP TABLE X .

7. Измените имя new_X на X:ALTER ТАБЛИЦА 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 или изменения типа данных для столбца для пример. Однако для некоторых изменений, которые никоим образом не влияют на содержимое на диске, может быть использована более простая и быстрая процедура. Следующая более простая процедура подходит для удаления ограничений CHECK, FOREIGN KEY или NOT NULL, а также для добавления, удаления или изменения значений по умолчанию в столбце.

1. Начать сделку.

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

3. Активируйте редактирование схемы с помощью PRAGMAwritable_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. Отключите редактирование схемы с помощью PRAGMAwritable_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,которые хранят свои схемы в виде разобранных системных таблиц,которые легче модифицировать.

---

SQLite

3.40

• SQL как понимается SQLite

  SQLite понимает большинство стандартных языков.

• Встроенные агрегатные функции

  aggregate-function-invocation: hide expr: show literal-value: show over-clause: show frame-spec: show ordering-term: show raise-function: show select-stmt:

• ANALYZE

  analyze-stmt:hide Команда ANALYZE собирает статистику об индексах таблиц и хранит собранную информацию внутри базы данных,где оптимизатор запросов

• ATTACH DATABASE

  attach-stmt: hide expr: show filter-clause: show literal-value: show over-clause: show frame-spec: show ordering-term: show raise-function: show select-stmt: