1. Синтаксис

create-table-stmt: hide

2. CREATE TABLE

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

• Имя.

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

• Название каждой колонки в таблице.

• Заявленный тип каждой колонки в таблице.

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

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

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

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

• Произвольно ограничение generated column.

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

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

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

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

Обычно ошибка попытаться составить новую таблицу в базе данных, которая уже содержит таблицу , индекс или представление того же самого имени. Однако, если указать "IF NOT EXISTS" как часть CREATE TABLE и таблица или представление того же самого имени уже есть, команда 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 байт. Оба из этих пределов могут быть понижены во времени выполнения, используя 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, который не предоставляет явные значения для всех столбцов таблицы, значения, сохраненные в новой строке, определяются их значениями по умолчанию, следующим образом:

• Если значение по умолчанию колонки constant NULL, text, 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 version 3.31.0 (2020-01-22). См. здесь для получения дополнительной информации о возможностях и ограничениях произведенных колонок.

3.5. Параметр PRIMARY KEY

У каждой таблицы в SQLite может быть самое большее один PRIMARY KEY. Если ключевые слова PRIMARY KEY добавляется к определению столбца, то первичный ключ для таблицы состоит из того отдельного столбца. Или, если пункт PRIMARY KEY определяется как ограничение таблицы, то первичный ключ состоит из списка колонок, определенных как часть пункта PRIMARY KEY. Пункт PRIMARY KEY должен содержать только имена столбцов, использование выражений в indexed-column для PRIMARY KEY не поддерживается. Ошибка поднята, если больше, чем один пункт PRIMARY KEY появляется в CREATE TABLE. 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. В целях ограничений UNIQUE значения NULL считают отличными от всех других, включая другой NULL. Как с PRIMARY KEY, ограничение UNIQUE должен содержать только имена столбцов, использование выражений в indexed-column ограничения 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 с version 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" заставляют колонку первичного ключа вести себя как обычный столбец таблицы с близостью integer и уникальным индексом, а не как псевдоним для rowid.

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

• 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 пытается вставить значение blob, последовательность или действительное значение, которое не может быть без потерь преобразовано в целое число в первичный ключ целого числа или колонку rowid, ошибка "datatype mismatch" происходит, и запрос прерывается.

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

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