SQLite中文网
创建表
► 目录

1.语法

创建表 stmt：

CREATE TEMP TEMPORARY TABLE IF NOT EXISTS schema-name . table-name ( column-def table-constraint , ) table-options , AS select-stmt

列定义：

选择stmt：

表约束：

表选项：

2.创建表命令

“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语句删除表。

2.1. CREATE TABLE ... AS SELECT 语句

“CREATE TABLE ... AS SELECT”语句根据 SELECT 语句的结果创建并填充数据库表。该表的列数与 SELECT 语句返回的行数相同。每列的名称与 SELECT 语句的结果集中相应列的名称相同。每一列的声明类型由SELECT语句结果集中对应表达式的表达式亲和关系决定，如下：

Expression Affinity Column Declared Type
TEXT "TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL "REAL"
BLOB (a.k.a "NONE") "" (empty string)

使用 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字节的数据。使用sqlite3_limit() C/C++ 接口可以在运行时降低这两个限制。

3.1. 列数据类型

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

3.2. 默认子句

如果用户在执行INSERT时没有明确提供值，则 DEFAULT 子句指定要用于该列的默认值。如果没有明确的 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 ALWAY AS 子句的列是生成列。从 SQLite verison 3.31.0 (2020-01-22) 开始支持生成的列。有关生成列的功能和限制的详细信息， 请参阅单独的文档。

3.5. 主键

SQLite 中的每个表最多只能有一个 PRIMARY KEY。如果将关键字 PRIMARY KEY 添加到列定义中，则表的主键由该单个列组成。或者，如果将 PRIMARY KEY 子句指定为table-constraint，则表的主键由指定为 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 允许 PRIMARY KEY 列中的 NULL 值。可以修复 SQLite 以符合标准，但这样做可能会破坏遗留应用程序。因此，决定仅记录 SQLite 在大多数 PRIMARY KEY 列中允许 NULL 的事实。

3.6. 唯一约束

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

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

  1. 创建表 t1（a，b 唯一）；

  2. 创建表 t1(a, b 主键);

  3. 创建表 t1(a, b);
    在 t1(b) 上创建唯一索引 t1b；

3.7. 检查约束

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

CHECK 约束仅在写入表时验证，而不是在读取时验证。此外，可以使用“ PRAGMA ignore_check_constraints=ON; ”语句暂时禁用 CHECK 约束的验证。因此，查询可能会产生违反 CHECK 约束的结果。

3.8. NOT NULL 约束

NOT NULL 约束只能附加到列定义，不能指定为表约束。毫不奇怪，NOT NULL 约束指示关联的列不能包含 NULL 值。在插入新行或更新现有行时尝试将列值设置为 NULL 会导致违反约束。NOT NULL 约束在查询期间不会被验证，因此如果数据库文件已损坏，即使该列被标记为 NOT NULL，对该列的查询也可能会产生 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

可以使用PRAGMA ignore_check_constraints=ON 暂时禁用 CHECK 约束的执行；陈述。

4.1. 对违反约束的响应

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

5. ROWID 和 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 表”。

rowid 表的数据存储为一个 B 树结构，每个表行包含一个条目，使用 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（整数主键）的别名：

  • 创建表 t(x 整数主键 ASC, y, z);
  • 创建表 t(x INTEGER, y, z, PRIMARY KEY(x ASC));
  • 创建表 t(x INTEGER, y, z, PRIMARY KEY(x DESC));

但以下声明不会导致“x”成为 rowid 的别名：

  • 创建表 t(x INTEGER PRIMARY KEY DESC, y, z);

可以使用 UPDATE 语句以与任何其他列值相同的方式修改 Rowid 值，使用内置别名之一（“rowid”、“oid”或“_rowid_”）或使用由整数主键。类似地，INSERT 语句可以提供一个值用作插入的每一行的 rowid。与普通的 SQLite 列不同，整数主键或 rowid 列必须包含整数值。整数主键或 rowid 列不能保存浮点值、字符串、BLOB 或 NULL。

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

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

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