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.

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

В SQL есть несколько полезных инструкций, которые здорово облегчают жизнь программистам. Одни из них IF EXISTS и IF NOT EXISTS. Их можно применять как условие существования или отсутствия в разных командах, например в CREATE TABLE.

Когда мы будем рассматривать вопросы использования SQLite в PHP, то вы узнаете, что подключение этой базы (фактически файла) происходит всегда: если файл есть, то он законнетится, а если нет, то будет создан новый. Это удобно с точки зрения программирования, но создаёт проблему — нужно убедиться, что база новая и тогда нужно выполнить запросы по созданию таблиц.

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

Делается это путем добавления инструкции IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS table_name (...);

СУБД сама проверит наличие указанной таблицы. Если же условие IF NOT EXISTS убрать, то повторное выполнение запроса приведёт к ошибке «table table_name already exists».

Это же условие можно использовать в запросе CREATE INDEX:

CREATE INDEX IF NOT EXISTS index_name ON table_name (...);

Обратное условие IF EXISTS используется в командах DROP:

DROP TABLE IF EXISTS table_name;
DROP INDEX IF EXISTS index_name;

Команда ALTER TABLE

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

Переименовать таблицу можно с помощью RENAME TO:

ALTER TABLE t1 RENAME TO t2;

Если нужно переименовать какой-то столбец в таблице, то используется RENAME COLUMN:

ALTER TABLE t1 RENAME COLUMN name TO title;

Для добавления нового столбца используется ADD COLUMN:

ALTER TABLE t1 ADD COLUMN slug TEXT DEFAULT '';

В данном примере мы добавили новое поле slug типом TEXT и значением по умолчанию DEFAULT.

Ну и с помощью DROP COLUMN можно удалить столбец.

ALTER TABLE t1 DROP COLUMN slug;

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

Системные таблицы SQLite

В любой базе будут присутствовать несколько системных таблиц. Одна из них sqlite_master. Попробуйте выполнить запрос:

SELECT * FROM sqlite_master;
 
type   name   tbl_name  rootpage  sql
-------------------------------------
table  t1     t1        2         CREATE TABLE...
... и т.д.

Когда стоит задача узнать какие в базе есть таблицы, то достаточно сделать sql-запрос к sqlite_master.

Другая таблица — sqlite_sequence хранит последнее используемое значение автоинкрементов в таблицах (если они есть).

SELECT * FROM sqlite_sequence;
 
name    seq
-----------
pages   3
ststus  2
tb      5
tGr1    8

В редких случаях может понадобиться узнать следующий номер PRIMARY KEY. Проще всего это узнать в таблице sqlite_sequence.

Ещё одна системная таблица — sqlite_stat1 используется для сбора статистики по индексам и это может быть использовано для оптимизации запросов SQLite. Чтобы таблица появилась, нужно выполнить команду ANALYZE.

ANALYZE; -- для все базы
ANALYZE t1; -- только для определённой таблицы
SELECT * FROM sqlite_stat1; -- можно посмотреть данные

Результат работы ANALYZE используется планировщиком запросов SQLite, что позволяет ускорить их выполнение. Но есть нюанс — работать это будет, только если выполнить команду ANALYZE. Если её не сделать, то информация будет устаревшей или неточной. Я думаю, что ANALYZE будет полезна для профессиональных разработчиков баз данных, где большие объёмы критически сказываются на скорости выполнения запросов, да и сами запросы достаточно сложные.

Представление (VIEW)

Достаточно интересная возможность — это создание представления. Это некая виртуальная таблица, которая создаётся отдельным SELECT запросом. С помощью VIEW можно упростить использование сложных sql-запросов.

Например у нас есть запрос к таблице tSum (раньше мы её делали). Предположим, что нам нужно часто использовать какой-то сложный запрос. Делаем на его основе представление tSum_v1:

CREATE VIEW IF NOT EXISTS tSum_v1 AS
    SELECT a, b, a + b
    FROM tSum;

Теперь оно доступно как отдельная таблица:

SELECT * FROM tSum_v1;

Представления интересны в случаях, когда нужно предоставить базу с уже готовыми SQL-запросами. Например разработчик может заранее добавить в базу представления и передать её клиенту. То есть клиенту достаточно «запустить» представление, чтобы получить нужную выборку.

Внешние ключи FOREIGN KEY

Когда мы связывали несколько таблиц, то использовали какое-то поле, которое ссылается на другую таблицу. Фактически таблицы полностью самостоятельны, а связь мы организуем через sql-запрос. Но SQLite (и другие СУБД) позволяют организовать такую связь прямо при создании таблиц.

Я не буду приводить примеры, поскольку использование FOREIGN KEY в подавляющем большинстве случаев лишено смысла, но вы должны знать об этой возможности.

Когда создаётся FOREIGN KEY для таблиц, то возникают дополнительные условия хранения и изменения данных в связанных полях. Например, если мы попытаемся удалить запись из родительской таблицы, но при этом останется ссылка в дочерней, то это приведёт к ошибке — вначале нужно будет удалить данные из дочерней таблицы. Так проверяется целостность данных.

Таких ситуаций может быть много — они связаны и с добавлением данных, и удалением, и обновлением.

Для чего же тогда были придуманы FOREIGN KEY? В первую очередь для того, чтобы избавиться от «проблемных» данных и проверка на уровне БД позволяет гарантировать целостность и корректность данных. Если мы вручную вносим данные в базу, то логично было бы получить и проверку.

На практике же такие проверки выполняю готовые программы. Скажем при добавлении автора, php-скрипт сделает запрос, чтобы убедиться, что указанный автор действительно существует. Или при удалении пользователя, также вначале нужно убедится, что он есть, а потом скрипт удалит все данные пользователя во всех таблицах базы. Такой алгоритм работы объясняется безопасностью, а также тем правилом, что нельзя доверять любым входящим данным. Поэтому многочисленные проверки стали нормой, что делают не нужными проверки уровня FOREIGN KEY.