CREATE TABLE

1. Синтаксис

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

2. Команда CREATE TABLE

Команда "CREATE TABLE" используется для создания новой таблицы в базе данных SQLite. Команда CREATE TABLE задаёт следующие атрибуты новой таблицы:

• Имя новой таблицы.

• База данных, в которой создаётся новая таблица. Таблицы можно создавать в основной базе данных, временной базе данных или в любой подключённой базе данных.

• Имя каждого столбца таблицы.

• Объявленный тип каждого столбца таблицы.

• Значение или выражение по умолчанию для каждого столбца таблицы.

• Последовательность сортировки по умолчанию для каждого столбца.

• Необязательный PRIMARY KEY для таблицы. Поддерживаются первичные ключи как для одного столбца, так и составные (для нескольких столбцов).

• Ноль или более ограничений на содержимое таблицы. SQLite поддерживает ограничения UNIQUE, NOT NULL, CHECK и FOREIGN KEY.

• Необязательное ограничение для генерируемого столбца.

• Является ли таблица таблицей WITHOUT ROWID.

• Подлежит ли таблица строгой проверке типов.

Каждый оператор CREATE TABLE должен задавать имя новой таблицы. Имена таблиц, начинающиеся с "sqlite_", зарезервированы для внутреннего использования. Попытка создать таблицу с именем, начинающимся с "sqlite_", приводит к ошибке.

Если задано имя-схемы, оно должно быть "main", "temp" или именем подключённой базы данных. В этом случае новая таблица создаётся в указанной базе данных. Если между ключевыми словами "CREATE" и "TABLE" указано ключевое слово "TEMP" или "TEMPORARY", новая таблица создаётся во временной базе данных. Указывать одновременно имя-схемы и ключевое слово TEMP или TEMPORARY нельзя, за исключением случая, когда имя-схемы равно "temp". Если имя схемы не указано и ключевое слово TEMP отсутствует, таблица создаётся в основной базе данных.

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

Создание таблицы с тем же именем, что и существующий триггер, не является ошибкой.

Таблицы удаляются с помощью оператора DROP TABLE.

2.1. Операторы CREATE TABLE ... AS SELECT

Оператор "CREATE TABLE ... AS SELECT" создаёт и заполняет таблицу базы данных на основе результатов оператора SELECT. Таблица содержит столько же столбцов, сколько возвращает оператор SELECT. Имя каждого столбца совпадает с именем соответствующего столбца в наборе результатов оператора SELECT. Объявленный тип каждого столбца определяется аффинностью выражения соответствующего выражения в наборе результатов оператора SELECT следующим образом:

У таблицы, созданной с помощью CREATE TABLE AS, нет PRIMARY KEY и нет ограничений любого вида. Значение по умолчанию для каждого столбца — NULL. Последовательность сортировки по умолчанию для каждого столбца новой таблицы — BINARY.

Таблицы, созданные с помощью CREATE TABLE AS, изначально заполняются строками данных, возвращёнными оператором SELECT. Строкам назначаются последовательные возрастающие значения rowid, начиная с 1, в том порядке, в котором их возвращает оператор SELECT.

3. Определения столбцов

За исключением оператора CREATE TABLE ... AS SELECT, оператор CREATE TABLE содержит одно или несколько определений столбцов, за которыми может следовать список ограничений таблицы. Каждое определение столбца состоит из имени столбца, за которым может следовать объявленный тип столбца, а затем одно или несколько необязательных ограничений столбца. Для целей предыдущего утверждения к определению «ограничений столбца» относятся предложения COLLATE и DEFAULT, хотя на самом деле они не являются ограничениями в том смысле, что не ограничивают данные, которые может содержать таблица. Остальные ограничения — NOT NULL, CHECK, UNIQUE, PRIMARY KEY и FOREIGN KEY — накладывают ограничения на данные таблицы.

Количество столбцов в таблице ограничено параметром компиляции SQLITE_MAX_COLUMN. Одна строка таблицы не может хранить более SQLITE_MAX_LENGTH байт данных. Оба этих ограничения можно уменьшить во время выполнения с помощью интерфейса C/C++ sqlite3_limit().

3.1. Типы данных столбцов

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

3.2. Предложение DEFAULT

Предложение DEFAULT задаёт значение по умолчанию для столбца, если пользователь явно не указал значение при выполнении команды INSERT. Если в определении столбца нет явного предложения DEFAULT, значением столбца по умолчанию является NULL. Явное предложение DEFAULT может указывать, что значением по умолчанию является NULL, строковая константа, константа BLOB, знаковое число или любое константное выражение, заключённое в скобки. Значением по умолчанию также может быть одно из специальных ключевых слов, не чувствительных к регистру: CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP. Для целей предложения DEFAULT выражение считается константным, если оно не содержит подзапросов, ссылок на столбцы или таблицы, связанных параметров или строковых литералов, заключённых в двойные кавычки вместо одинарных.

Каждый раз, когда оператор INSERT добавляет строку в таблицу, не указывая явные значения для всех столбцов таблицы, значения, сохраняемые в новой строке, определяются значениями по умолчанию следующим образом:

• Если значением столбца по умолчанию является константа NULL, текстовое значение, значение BLOB или знаковое число, это значение непосредственно используется в новой строке.

• Если значением столбца по умолчанию является выражение в скобках, оно вычисляется для каждой вставляемой строки, а полученные результаты используются в новой строке.

• Если значением столбца по умолчанию является CURRENT_TIME, CURRENT_DATE или CURRENT_TIMESTAMP, в новой строке используется текстовое представление текущей даты и/или времени UTC. Для CURRENT_TIME формат значения — "HH:MM:SS". Для CURRENT_DATE — "YYYY-MM-DD". Формат CURRENT_TIMESTAMP — "YYYY-MM-DD HH:MM:SS".

3.3. Предложение COLLATE

Предложение COLLATE задаёт имя последовательности сортировки, которая будет использоваться как последовательность сортировки по умолчанию для столбца. Если предложение COLLATE не указано, последовательностью сортировки по умолчанию является BINARY.

3.4. Предложение GENERATED ALWAYS AS

Столбец, содержащий предложение GENERATED ALWAYS AS, является генерируемым столбцом. Поддержка генерируемых столбцов появилась в SQLite версии 3.31.0 (2020-01-22). Подробные сведения о возможностях и ограничениях генерируемых столбцов см. в отдельной документации.

3.5. PRIMARY KEY

У каждой таблицы SQLite может быть не более одного PRIMARY KEY. Если ключевые слова PRIMARY KEY добавлены в определение столбца, первичный ключ таблицы состоит из этого единственного столбца. Если же предложение PRIMARY KEY указано как ограничение таблицы, первичный ключ таблицы состоит из списка столбцов, указанных в предложении PRIMARY KEY. Предложение PRIMARY KEY должно содержать только имена столбцов — использование выражений в индексируемом столбце PRIMARY KEY не поддерживается. Если в операторе CREATE TABLE встречается более одного предложения PRIMARY KEY, возникает ошибка. Для обычных таблиц PRIMARY KEY необязателен, но для таблиц WITHOUT ROWID он обязателен.

Если у таблицы есть первичный ключ из одного столбца, объявленный тип которого — "INTEGER", и таблица не является таблицей WITHOUT ROWID, такой столбец называется INTEGER PRIMARY KEY. Описание специальных свойств и поведения, связанных с INTEGER PRIMARY KEY, см. ниже.

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

Согласно стандарту SQL, PRIMARY KEY всегда должен подразумевать NOT NULL. К сожалению, из-за ошибки в некоторых ранних версиях SQLite это не так. Если только столбец не является INTEGER PRIMARY KEY, таблица не является таблицей WITHOUT ROWID или таблицей STRICT, либо для столбца не объявлено NOT NULL, SQLite разрешает значения NULL в столбце PRIMARY KEY. SQLite можно было бы исправить для соответствия стандарту, но это могло бы нарушить работу устаревших приложений. Поэтому было решено лишь задокументировать тот факт, что SQLite разрешает NULL в большинстве столбцов PRIMARY KEY.

3.6. Ограничения UNIQUE

Ограничение UNIQUE похоже на ограничение PRIMARY KEY, за исключением того, что таблица может иметь любое количество ограничений UNIQUE. Для каждого ограничения UNIQUE в таблице каждая строка должна содержать уникальную комбинацию значений в столбцах, указанных в этом ограничении. Для ограничений UNIQUE значения NULL считаются отличными от всех остальных значений, включая другие значения NULL. Как и для PRIMARY KEY, предложение ограничения таблицы UNIQUE должно содержать только имена столбцов — использование выражений в индексируемом столбце ограничения UNIQUE для таблицы не поддерживается.

В большинстве случаев ограничения UNIQUE и PRIMARY KEY реализуются путём создания уникального индекса в базе данных. (Исключения — INTEGER PRIMARY KEY и PRIMARY KEY в таблицах WITHOUT ROWID.) Поэтому следующие схемы логически эквивалентны:

1. CREATE TABLE t1(a, b UNIQUE);

2. CREATE TABLE t1(a, b PRIMARY KEY);

3. CREATE TABLE t1(a, b);
   CREATE UNIQUE INDEX t1b ON t1(b);

3.7. Ограничения CHECK

Ограничение CHECK можно присоединить к определению столбца или указать как ограничение таблицы. На практике разницы нет. Каждый раз, когда в таблицу вставляется новая строка или обновляется существующая, выражение, связанное с каждым ограничением CHECK, вычисляется и преобразуется в значение NUMERIC так же, как выражение CAST. Если результат равен нулю (целое значение 0 или вещественное значение 0.0), происходит нарушение ограничения. Если выражение CHECK возвращает NULL или любое другое ненулевое значение, нарушения ограничения не происходит. Выражение ограничения CHECK не может содержать подзапрос.

Ограничения CHECK проверяются только при записи в таблицу, но не при чтении. Кроме того, проверку ограничений CHECK можно временно отключить с помощью оператора "PRAGMA ignore_check_constraints=ON;". Поэтому запрос может возвращать результаты, нарушающие ограничения CHECK.

3.8. Ограничения NOT NULL

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

4. Проверка ограничений

Ограничения проверяются во время выполнения команд INSERT и UPDATE, а также командами PRAGMA integrity_check и PRAGMA quick_check и иногда командой ALTER TABLE. Запросы и операторы DELETE обычно не проверяют ограничения. Поэтому, если файл базы данных повреждён (например, внешняя программа напрямую изменила файл базы данных, не используя библиотеку SQLite), запрос может вернуть данные, нарушающие ограничения. Например:

CREATE TABLE t1(x INT CHECK( x>3 ));
/* Insert a row with X less than 3 by directly writing into the
** database file using an external program */
PRAGMA integrity_check;  -- Reports row with x less than 3 as corrupt
INSERT INTO t1(x) VALUES(2);  -- Fails with SQLITE_CORRUPT
SELECT x FROM t1;  -- Returns an integer less than 3 in spite of the CHECK constraint

Проверку ограничений CHECK можно временно отключить с помощью оператора PRAGMA ignore_check_constraints=ON;.

4.1. Реакция на нарушения ограничений

Реакция на нарушение ограничения определяется алгоритмом разрешения конфликтов ограничений. Для каждого ограничения PRIMARY KEY, UNIQUE, NOT NULL и CHECK предусмотрен алгоритм разрешения конфликтов по умолчанию. Для ограничений PRIMARY KEY, UNIQUE и NOT NULL можно явно задать другой алгоритм разрешения конфликтов по умолчанию, включив в их определения предложение conflict-clause. Если определение ограничения не содержит предложения conflict-clause, по умолчанию используется алгоритм разрешения конфликтов ABORT. Для ограничений CHECK всегда используется алгоритм ABORT. (Только ради совместимости с историческим поведением ограничения CHECK таблицы могут содержать предложение разрешения конфликтов, но оно не оказывает никакого эффекта.) Для разных ограничений одной таблицы могут использоваться разные алгоритмы разрешения конфликтов по умолчанию. Дополнительные сведения см. в разделе ON CONFLICT.

5. ROWID и INTEGER PRIMARY KEY

За исключением таблиц WITHOUT ROWID, каждая строка таблиц SQLite имеет 64-разрядный знаковый целочисленный ключ, который уникально идентифицирует строку в таблице. Это целое число обычно называется "rowid". Доступ к значению rowid можно получить, используя одно из специальных имён, не чувствительных к регистру: "rowid", "oid" или "_rowid_", вместо имени столбца. Если в таблице есть определённый пользователем столбец с именем "rowid", "oid" или "_rowid_", это имя всегда относится к явно объявленному столбцу и не может использоваться для получения целочисленного значения rowid.

Значение rowid (а также "oid" и "_rowid_") отсутствует в таблицах WITHOUT ROWID. Таблицы WITHOUT ROWID доступны только в SQLite версии 3.8.2 (2013-12-06) и более поздних. Таблица без предложения WITHOUT ROWID называется "таблицей rowid".

Данные таблиц rowid хранятся в структуре B-дерева, содержащей по одной записи для каждой строки таблицы; в качестве ключа используется значение rowid. Это означает, что получение или сортировка записей по rowid выполняется быстро. Поиск записи с определённым rowid или всех записей со значениями rowid в заданном диапазоне выполняется примерно вдвое быстрее аналогичного поиска по любому другому PRIMARY KEY или индексируемому значению.

За исключением описанного ниже случая, если у таблицы rowid есть первичный ключ из одного столбца, объявленный тип которого — "INTEGER" в любом сочетании прописных и строчных букв, такой столбец становится псевдонимом rowid. Такой столбец обычно называют "целочисленным первичным ключом". Столбец PRIMARY KEY становится целочисленным первичным ключом только в том случае, если объявленное имя типа — в точности "INTEGER". Другие имена целочисленных типов, такие как "INT", "BIGINT", "SHORT INTEGER" или "UNSIGNED INTEGER", приводят к тому, что столбец первичного ключа ведёт себя как обычный столбец таблицы с целочисленной аффинностью и уникальным индексом, а не как псевдоним rowid.

Упомянутое выше исключение состоит в том, что если объявление столбца с объявленным типом "INTEGER" включает предложение "PRIMARY KEY DESC", он не становится псевдонимом rowid и не считается целочисленным первичным ключом. Это особенность не была задумана. Она возникла из-за ошибки в ранних версиях SQLite. Однако исправление ошибки могло бы привести к обратной несовместимости. Поэтому исходное поведение было сохранено (и задокументировано), поскольку странное поведение в особом случае гораздо предпочтительнее нарушения совместимости. Это означает, что все три следующих объявления таблицы делают столбец "x" псевдонимом rowid (целочисленным первичным ключом):

• CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
• CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

Но следующее объявление не делает "x" псевдонимом rowid:

• CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Значения rowid можно изменять с помощью оператора UPDATE так же, как значения любых других столбцов: используя один из встроенных псевдонимов ("rowid", "oid" или "_rowid_") либо псевдоним, созданный целочисленным первичным ключом. Аналогично, оператор INSERT может задавать значение rowid для каждой вставляемой строки. В отличие от обычных столбцов SQLite, целочисленный первичный ключ или столбец rowid должен содержать целочисленные значения. В столбцах целочисленного первичного ключа или rowid нельзя хранить числа с плавающей точкой, строки, значения BLOB или NULL.

Если оператор UPDATE пытается присвоить столбцу целочисленного первичного ключа или rowid значение NULL или BLOB, а также строковое или вещественное значение, которое невозможно преобразовать в целое число без потери данных, возникает ошибка "datatype mismatch", и оператор прерывается. Если оператор INSERT пытается вставить в столбец целочисленного первичного ключа или rowid значение BLOB, а также строковое или вещественное значение, которое невозможно преобразовать в целое число без потери данных, возникает ошибка "datatype mismatch", и оператор прерывается.

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

В качестве родительского ключа ограничения внешнего ключа нельзя использовать rowid. Родительский ключ должен использовать только именованные столбцы.

Последнее обновление этой страницы: 2025-04-30 20:02:34Z