SQLite - 创建表

CREATE TABLE 语句用于在数据库中创建新表。创建表需要定义表的名称、包含的列以及每列的数据类型。现代数据库设计还涉及定义约束（constraints）以确保数据完整性。

现代语法与最佳实践

下面是 CREATE TABLE 语句的现代推荐语法，其中包含常见的约束。

CREATE TABLE [IF NOT EXISTS] table_name (
   column1_name data_type [PRIMARY KEY] [constraints],
   column2_name data_type [constraints],
   ...,
   [FOREIGN KEY (column_name) REFERENCES other_table (other_column_name)]
);

IF NOT EXISTS 是一个可选但非常有用的子句，它可以在表已存在时防止报错。下面我们来分解一下其关键组成部分：

数据类型（类型亲和性）

SQLite 使用一种名为“类型亲和性”（type affinity）的动态类型系统。列可以存储任何类型的值，但亲和性是推荐的类型。主要的亲和性有：

INTEGER：用于整数。定义为 INTEGER PRIMARY KEY 的列将自动递增（auto-increment），这是创建唯一 ID 的标准方法。
TEXT：用于文本字符串。
REAL：用于浮点数（decimal）。
BLOB：用于存储原始输入数据（例如图片、文件）。
NUMERIC：可以存储任何格式的值，但会尽量将其转换为 INTEGER 或 REAL。

约束

约束是对表中数据强制执行的规则。它们对于维护数据完整性至关重要。

PRIMARY KEY（主键）：唯一标识表中的每条记录。它必须包含唯一值，且不能包含 NULL 值。
FOREIGN KEY（外键）：将两个表连接起来。它是一个用于强制执行两个表之间数据链接的键，防止破坏这些链接的操作。
NOT NULL：确保列不能包含 NULL 值（空值）。
UNIQUE：确保列中的所有值都是唯一的。
DEFAULT value：当未指定值时，为列提供一个默认值。
CHECK (condition)：确保列中的所有值都满足特定条件。

示例：现代方法

让我们为一个小型公司创建一个关系型模式，包含 DEPARTMENTS（部门）表和 EMPLOYEES（员工）表。这将演示像外键这样的现代最佳实践。

-- 首先，创建 DEPARTMENTS 表
sqlite> CREATE TABLE DEPARTMENTS (
   id INTEGER PRIMARY KEY, -- 自动递增主键
   name TEXT NOT NULL UNIQUE
);

-- 现在，创建一个带有外键的 EMPLOYEES 表
sqlite> CREATE TABLE EMPLOYEES (
   id INTEGER PRIMARY KEY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL CHECK(age > 18),
   address TEXT,
   salary REAL DEFAULT 0.00,
   department_id INTEGER,
   FOREIGN KEY (department_id) REFERENCES DEPARTMENTS (id)
);

验证表创建

您可以使用 SQLite 命令行工具中的 .tables 命令来验证表是否成功创建。

sqlite> .tables
DEPARTMENTS  EMPLOYEES

要查看表的完整 CREATE TABLE 语句，请使用 .schema 命令。这对于调试非常有用。

sqlite> .schema EMPLOYEES
CREATE TABLE EMPLOYEES (
   id INTEGER PRIMARY KEY,
   first_name TEXT NOT NULL,
   last_name TEXT NOT NULL,
   age INTEGER NOT NULL CHECK(age > 18),
   address TEXT,
   salary REAL DEFAULT 0.00,
   department_id INTEGER,
   FOREIGN KEY (department_id) REFERENCES DEPARTMENTS (id)
);