СОЗДАНИЕ ТАБЛИЦЫ

1. Синтаксис

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

таблица-ограничение: показать

2. Команда CREATE TABLE

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

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

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

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

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

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

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

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

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

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

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

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

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

Если указано имя_схемы, оно должно быть либо «main», «temp», либо именем присоединённой базы данных. В этом случае новая таблица создаётся в указанной базе данных. Если ключевое слово «TEMP» или «TEMPORARY» встречается между «CREATE» и «TABLE», то новая таблица создаётся во временной базе данных. Ошибка возникает при указании и имя_схемы и ключевых слов 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, не имеет первичного ключа и никаких ограничений. Значение по умолчанию для каждого столбца – 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 формат значения — "ЧЧ:ММ:СС". Для CURRENT_DATE — "ГГГГ-ММ-ДД". Формат для CURRENT_TIMESTAMP — "ГГГГ-ММ-ДД ЧЧ:ММ:СС".

3.3. Оператор COLLATE

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

3.4. Оператор GENERATED ALWAYS AS

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

3.5. Основной ключ

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

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

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

Согласно стандарту SQL, PRIMARY KEY всегда подразумевает NOT NULL. К сожалению, из-за ошибки в некоторых ранних версиях это не так в SQLite. Если столбец не является целочисленным первичным ключом, таблица не является WITHOUT ROWID таблицей или столбец объявлен 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 должны содержаться только имена столбцов — использование выражений в индексированном столбце ограничения UNIQUE не поддерживается.

В большинстве случаев ограничения UNIQUE и 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 могут быть явно назначены другому алгоритму разрешения конфликтов по умолчанию, включив оператор конфликта в их определения. Или, если определение ограничения не включает оператор конфликта, алгоритмом разрешения конфликтов по умолчанию является ABORT. Алгоритм разрешения конфликтов для ограничений CHECK всегда ABORT. (Только для обеспечения обратной совместимости, ограничения CHECK таблиц могут иметь оператор разрешения конфликтов, но это не влияет на результат). Разные ограничения в одной таблице могут иметь разные алгоритмы разрешения конфликтов по умолчанию. Дополнительную информацию см. в разделе ON CONFLICT.

5. ROWID и целочисленный первичный ключ

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

За исключением одного случая, указанного ниже, если таблица 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, или в строку или вещественное значение, которое не может быть без потерь преобразовано в целое число, возникает ошибка «несоответствие типов», и оператор прерывается. Если оператор INSERT пытается вставить значение blob, или строку или вещественное значение, которое не может быть без потерь преобразовано в целое число, в столбец целочисленного первичного ключа или rowid, возникает ошибка «несоответствие типов», и оператор прерывается.

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

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

Эта страница была последним обновлением 19.09.2024 08:12:22 UTC