CREATE TABLE

SQL As Understood By SQLite

[Top]

CREATE TABLE

create-table-stmt: hide

column-def: show

column-constraint: show

conflict-clause: show

expr: show

raise-function: show

foreign-key-clause: show

literal-value: show

signed-number: show

type-name: show

signed-number: show

select-stmt: show

common-table-expression: show

compound-operator: show

expr: show

literal-value: show

raise-function: show

type-name: show

signed-number: show

join-clause: show

join-constraint: show

join-operator: show

ordering-term: show

result-column: show

table-or-subquery: show

table-constraint: show

conflict-clause: show

expr: show

literal-value: show

raise-function: show

type-name: show

signed-number: show

foreign-key-clause: show

indexed-column: show

“CREATE TABLE”命令用于在SQLite数据库中创建一个新表。CREATE TABLE命令指定新表的以下属性：

• 新表的名称。

• 在其中创建新表的数据库。表可以在主数据库，临时数据库或任何附加数据库中创建。

• 表中每列的名称。

• 表中每列的声明类型。

• 表中每列的默认值或表达式。

• 用于每列的默认排序顺序。

• （可选）表格的PRIMARY KEY。支持单列和复合（多列）主键。

• 每个表的一组SQL约束。SQLite支持UNIQUE，NOT NULL，CHECK和FOREIGN KEY约束。

• 该表是否是WITHOUT ROWID表。

每个CREATE TABLE语句都必须为新表指定一个名称。以“sqlite_”开头的表名保留供内部使用。尝试创建名称以“sqlite_”开头的表是错误的。

如果指定了模式名称，则它必须是“main”，“temp”或附加数据库的名称。在这种情况下，新表将在指定的数据库中创建。如果“CREATE”和“TABLE”之间出现“TEMP”或“TEMPORARY”关键字，则在临时数据库中创建新表。指定模式名称和TEMP或TEMPORARY关键字是错误的，除非模式名称是“temp”。如果未指定模式名称并且TEMP关键字不存在，则在主数据库中创建该表。

尝试在已包含同名表，索引或视图的数据库中创建新表通常是错误的。但是，如果将“IF NOT EXISTS”子句指定为CREATE TABLE语句的一部分，并且已存在同名表或视图，则CREATE TABLE命令根本无效（并且不会返回错误消息）。如果由于存在索引而无法创建表，即使指定了“IF NOT EXISTS”子句，仍会返回错误。

创建一个与现有触发器具有相同名称的表并不是错误。

使用DROP TABLE语句删除表。

CREATE TABLE ... AS SELECT Statements

“CREATE TABLE ... AS SELECT”语句基于SELECT语句的结果创建并填充数据库表。该表具有与SELECT语句返回的行相同的列数。每列的名称与SELECT语句的结果集中相应列的名称相同。每个列的声明类型由SELECT语句的结果集中相应表达式的表达式亲和性决定，如下所示：

使用CREATE TABLE AS创建的表没有主键，也没有任何类型的约束。每列的默认值为NULL。新表的每列的默认排序顺序是BINARY。

使用CREATE TABLE AS创建的表最初由SELECT语句返回的数据行填充。行按照SELECT语句返回的顺序从1开始连续递增rowid值。

列定义

除非它是CREATE TABLE ... AS SELECT语句，否则CREATE TABLE包含一个或多个列定义，可选地后跟一个表约束列表。每个列定义由该列的名称组成，可选地紧接着列的声明类型，然后是一个或多个可选的列约束。为了上述语句的目的，在“列约束”的定义中包含COLLATE和DEFAULT子句，即使这些子句不限制表中可能包含的数据，也不是真正的约束。其他约束 - NOT NULL，CHECK，UNIQUE，PRIMARY KEY和FOREIGN KEY约束 - 对表数据施加限制，并在下面的SQL Data Constraints下描述。

与大多数SQL数据库不同，SQLite不会根据声明类型的列限制可能插入到列中的数据的类型。相反，SQLite使用动态类型。列的声明类型仅用于确定列的相关性。

如果用户在执行INSERT时没有明确提供值，则DEFAULT子句指定用于列的默认值。如果没有明确的DEFAULT子句附加到列定义，那么该列的默认值为NULL。明确的DEFAULT子句可以指定默认值为NULL，字符串常量，blob常量，有符号数或括号中的任何常量表达式。默认值也可以是特殊情况下独立的关键字CURRENT_TIME，CURRENT_DATE或CURRENT_TIMESTAMP之一。出于DEFAULT子句的目的，如果表达式不包含子查询，列或表引用，绑定参数或用双引号括起来的字符串文本而不是单引号，则认为表达式是常量。

每次通过不为所有表列提供显式值的INSERT语句将行插入表中时，存储在新行中的值都由其默认值确定，如下所示：

• 如果该列的默认值是一个常量NULL，text，blob或signed-number值，那么该值将直接用于新行。

• 如果列的默认值是括号中的表达式，则对于插入的每一行以及新行中使用的结果，将对该表达式计算一次。

• 如果列的默认值为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”。

COLLATE子句指定用作列的默认排序顺序的排序顺序的名称。如果未指定COLLATE子句，则默认排序顺序为BINARY。

表中列的数量受SQLITE_MAX_COLUMN编译时参数的限制。表的一行不能存储超过SQLITE_MAX_LENGTH字节的数据。这两个限制可以在运行时使用sqlite3_limit()C/C++接口降低。

SQL数据约束

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表或列被声明为NOT NULL，否则SQLite允许在PRIMARY KEY列中使用NULL值。可以修复SQLite以符合标准，但这样做可能会破坏传统应用程序。因此，已经决定仅仅记录SQLite在大多数PRIMARY KEY列中允许NULL的事实。

一个UNIQUE约束类似于PRIMARY KEY约束，不同的是一个单一的表可以具有任意数量的UNIQUE约束。对于表上的每个UNIQUE约束，每行必须包含由UNIQUE约束标识的列中唯一值的组合。出于UNIQUE约束的目的，NULL值被认为与所有其他值不同，包括其他NULL。与PRIMARY KEY一样，UNIQUE表约束子句必须仅包含列名 - 不支持在UNIQUE表约束的索引列中使用表达式。

在大多数情况下，通过在数据库中创建唯一索引来实现UNIQUE和PRIMARY KEY约束。（例外是在WITHOUT ROWID表上的INTEGER PRIMARY KEY和PRIMARY KEY。）因此，以下模式在逻辑上是等效的：

• 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

CHECK约束可以被附接到一列定义或指定为表约束。在实践中，它没有任何区别。每当将新行插入到表中或更新现有行时，与每个CHECK约束关联的表达式都会被计算并转换为与CAST表达式相同的NUMERIC值。如果结果为零（整数值0或实际值0.0），则发生约束违规。如果CHECK表达式的计算结果为NULL或任何其他非零值，则不是违反约束条件。CHECK约束的表达式可能不包含子查询。

NOT NULL约束可以仅被连接到一个列定义，没有被指定为表约束。毫不奇怪，NOT NULL约束表明关联的列可能不包含NULL值。尝试在插入新行或更新现有行时将列值设置为NULL会导致违反约束。

约束冲突的处理方式由约束冲突解决算法决定。每个PRIMARY KEY，UNIQUE，NOT NULL和CHECK约束都具有默认的冲突解决算法。PRIMARY KEY，UNIQUE和NOT NULL约束可以通过在其定义中包含冲突子句来显式地分配默认的冲突解决算法。或者，如果约束定义不包含冲突子句或它是CHECK约束，则缺省冲突解决算法为ABORT。同一表中的不同约束可能具有不同的默认冲突解决算法。有关更多信息，请参阅标题为“conflict”的部分。

ROWIDs and the INTEGER PRIMARY KEY

除WITHOUT ROWID表之外，SQLite表中的所有行都有一个64位有符号整数键，用于唯一标识其表中的行。这个整数通常被称为“rowid”。可以使用特殊大小写无关名称“rowid”，“oid”或“_rowid_”替代列名称来访问rowid值。如果一个表包含名为“rowid”，“oid”或“_rowid_”的用户定义列，那么该名称始终引用明确声明的列，并且不能用于检索整数rowid值。

在WITHOUT ROWID表中省略rowid（和“oid”和“_rowid_”）。WITHOUT ROWID表仅在SQLite 版本3.8.2（2013-12-06）及更高版本中可用。缺少WITHOUT ROWID子句的表称为“rowid table”。

rowid表的数据存储为B-Tree结构，其中每个表行都包含一个条目，并使用rowid值作为键。这意味着通过rowid检索或排序记录的速度很快。搜索具有特定rowid的记录或具有指定范围内的rowid的所有记录的搜索速度是通过指定任何其他PRIMARY KEY或索引值而进行的类似搜索的两倍。

有一个例外如下所示，如果rowid表的主键由单个列组成，并且该列的声明类型为“INTEGER”，则为大小写混合，则该列成为rowid的别名。这样的列通常被称为“整数主键”。如果声明的类型名称完全是“INTEGER”，则PRIMARY KEY列只能成为整数主键。其他整数类型名称（如“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”，“oid”或“_rowid_”）或通过使用由别名创建的别名，使用UPDATE语句以与任何其他列值相同的方式修改Rowid值整数主键。同样，INSERT语句可能会提供一个值作为每个插入行的rowid。与普通SQLite列不同，整数主键或rowid列必须包含整数值。整数主键或rowid列不能保存浮点值，字符串，BLOB或NULL。

如果UPDATE语句试图将整数主键或rowid列设置为NULL或blob值，或者将字符串或实数值无法无损地转换为整数，则会发生“数据类型不匹配”错误，并且语句会中止。如果INSERT语句试图插入blob值或无法无损转换为整数的字符串或实数值到整数主键或rowid列中，则会发生“数据类型不匹配”错误，并且语句会中止。

如果INSERT语句试图将NULL值插入rowid或整数主键列，系统会自动选择一个整数值用作rowid。有关如何完成的详细说明单独提供。

外键约束的父键不允许使用rowid。父键只能使用命名列。