SQLite 约束

SQLite 约束的基本语法

SQLite 支持列级约束和表级约束。

列级约束直接写在列定义后面，例如：

CREATE TABLE COMPANY (
    ID INTEGER PRIMARY KEY,
    NAME TEXT NOT NULL,
    AGE INTEGER CHECK (AGE >= 18),
    SALARY REAL DEFAULT 50000.00
);

表级约束写在所有列定义之后，通常用于多个列共同参与约束的情况。

CREATE TABLE EMPLOYEE (
    ID INTEGER PRIMARY KEY,
    NAME TEXT NOT NULL,
    DEPARTMENT TEXT NOT NULL,
    EMPLOYEE_NO TEXT NOT NULL,
    UNIQUE (DEPARTMENT, EMPLOYEE_NO)
);

上面的 UNIQUE (DEPARTMENT, EMPLOYEE_NO) 要求部门与员工编号的组合不能重复。

需要注意，SQLite 中的 NOT NULL 只能作为列约束声明，而 UNIQUE、CHECK 和 PRIMARY KEY 等可以根据用途采用列级或表级写法。

---

NOT NULL 约束

默认情况下，SQLite 表中的普通列允许存储 NULL。

如果某列必须提供有效值，可以使用 NOT NULL 约束。

NULL 表示缺失或未知的值，不等于数字 0，也不等于空字符串 ''。

实例

下面创建一个 COMPANY 表，要求员工姓名和年龄不能为空。

插入正常数据：

INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY)
VALUES ('Zhang San', 28, 'Beijing', 60000);

插入成功后，可以通过以下语句查询：

SELECT * FROM COMPANY;

查询结果：

ID	NAME	AGE	ADDRESS	SALARY
1	Zhang San	28	Beijing	60000.0

如果尝试插入 NAME 为 NULL 的记录：

INSERT INTO COMPANY (NAME, AGE)
VALUES (NULL, 25);

SQLite 会拒绝该操作，错误信息类似：

NOT NULL constraint failed: COMPANY.NAME

注意：NOT NULL 只能阻止 NULL，不能阻止空字符串。

例如，下面的语句不会因为 NOT NULL 而失败：

INSERT INTO COMPANY (NAME, AGE)
VALUES ('', 25);

如果还要求姓名不能为空字符串，可以结合 CHECK 约束进行限制。

---

DEFAULT 约束

DEFAULT 用于指定列的默认值。

当 INSERT INTO 语句没有提供某列的值时，SQLite 会使用该列的默认值。

实例

下面创建 COMPANY 表，将 SALARY 的默认值设置为 50000.00。

插入数据时不指定工资：

INSERT INTO COMPANY (NAME, AGE)
VALUES ('Li Si', 30);

SELECT ID, NAME, SALARY FROM COMPANY;

查询结果：

ID	NAME	SALARY
1	Li Si	50000.0

需要注意，没有提供列值与显式提供 NULL 是两种不同情况。

例如：

INSERT INTO COMPANY (NAME, AGE, SALARY)
VALUES ('Wang Wu', 26, NULL);

这条语句明确将工资设置为 NULL，不会使用默认值 50000.00。

如果不允许工资为 NULL，可以组合使用：

SALARY REAL NOT NULL DEFAULT 50000.00

使用日期作为默认值

SQLite 还支持 CURRENT_DATE、CURRENT_TIME 和 CURRENT_TIMESTAMP 等默认值。

CREATE TABLE LOGS (
    ID INTEGER PRIMARY KEY,
    MESSAGE TEXT NOT NULL,
    CREATED_AT TEXT DEFAULT CURRENT_TIMESTAMP
);

插入日志时，不必手动指定创建时间：

INSERT INTO LOGS (MESSAGE)
VALUES ('User logged in');

CURRENT_TIMESTAMP 通常生成 UTC 时间，格式为 YYYY-MM-DD HH:MM:SS。

---

UNIQUE 约束

UNIQUE 约束用于保证指定列或多个列的组合不出现重复值。

例如，在员工表中，可以要求邮箱地址保持唯一。

实例

插入第一条记录：

INSERT INTO COMPANY (NAME, EMAIL)
VALUES ('Zhang San', 'zhang@example.com');

再次插入相同邮箱：

INSERT INTO COMPANY (NAME, EMAIL)
VALUES ('Li Si', 'zhang@example.com');

第二次插入会失败，错误信息类似：

UNIQUE constraint failed: COMPANY.EMAIL

UNIQUE 允许多个 NULL

SQLite 的 UNIQUE 约束允许多个 NULL 值，因为在唯一性检查中，NULL 被视为彼此不同。

例如：

INSERT INTO COMPANY (NAME, EMAIL)
VALUES ('Wang Wu', NULL);

INSERT INTO COMPANY (NAME, EMAIL)
VALUES ('Zhao Liu', NULL);

以上两条记录都可以成功插入。

如果希望邮箱既不能为空，又不能重复，可以使用：

EMAIL TEXT NOT NULL UNIQUE

组合唯一约束

如果需要保证多个列的组合不重复，可以使用表级 UNIQUE 约束。

这表示同一个部门内不能出现重复的员工编号，但不同部门可以使用相同的员工编号。

如果组合中的列允许 NULL，需要额外考虑 SQLite 对 NULL 的唯一性处理规则。业务上要求完整组合唯一时，通常应该同时声明 NOT NULL。

---

PRIMARY KEY 约束

PRIMARY KEY 用于指定表的主键，通常用于唯一标识每一条记录。

一个表最多只能定义一个主键，但主键可以由一个或多个列组成。

在 SQLite 中，主键有一些需要特别注意的实现细节。

INTEGER PRIMARY KEY

对于普通的 SQLite 行表，如果将一个列声明为 INTEGER PRIMARY KEY，该列通常会成为表的 rowid 别名。

例如：

查询结果：

ID	NAME	AGE
1	Zhang San	28
2	Li Si	30

由于没有显式指定 ID，SQLite 会自动为记录选择合适的整数主键值。

这里的自动生成不等于一定连续递增，也不保证已删除的编号永远不会再次使用。

INT PRIMARY KEY 与 INTEGER PRIMARY KEY 的区别

在 SQLite 中，INT PRIMARY KEY 和 INTEGER PRIMARY KEY 并不完全相同。

定义方式	主要特点
ID INTEGER PRIMARY KEY	在普通行表中，通常是 rowid 的别名，可自动生成整数主键值
ID INT PRIMARY KEY	通常作为普通主键约束处理，不会成为 rowid 的别名

因此，如果希望在 SQLite 普通行表中使用常见的自动整数主键，通常推荐：

ID INTEGER PRIMARY KEY

SQLite 主键允许 NULL 的特殊情况

根据 SQL 标准，主键列应当不允许 NULL。

但 SQLite 为了兼容早期版本，保留了一个特殊行为：在普通行表中，某些非 INTEGER PRIMARY KEY 的主键列可能允许 NULL。

例如：

CREATE TABLE TEST_PK (
    ID INT PRIMARY KEY,
    NAME TEXT
);

INSERT INTO TEST_PK (ID, NAME)
VALUES (NULL, 'Test');

在普通 SQLite 行表中，上述插入可以成功。

以下情况则不适用这一宽松规则：

• 主键列声明了 NOT NULL。
• 使用了符合特殊规则的 INTEGER PRIMARY KEY。
• 表使用 WITHOUT ROWID。
• 表使用 STRICT 模式。

因此，创建普通非整数主键时，如果要求主键不能为空，建议明确添加 NOT NULL。

复合主键

一个主键还可以由多个列组成，这种主键称为复合主键（Composite Primary Key）。

该表允许一个订单包含多个商品，但同一订单中不能存在重复的商品编号组合。

这里明确为两个主键列添加了 NOT NULL，避免 SQLite 普通行表的 NULL 兼容行为影响数据完整性。

---

CHECK 约束

CHECK 约束用于限制数据必须满足指定的条件表达式。

例如，可以要求员工年龄不小于 18，工资必须大于零。

实例

插入正常数据：

INSERT INTO COMPANY (NAME, AGE, SALARY)
VALUES ('Zhang San', 28, 60000);

如果尝试插入负数工资：

INSERT INTO COMPANY (NAME, AGE, SALARY)
VALUES ('Li Si', 30, -1000);

将产生类似错误：

CHECK constraint failed: SALARY > 0

CHECK 与 NULL

这是 SQLite 中容易忽略的一个细节。

SQLite 的 CHECK 表达式计算结果为零时，才判定违反约束。如果结果为非零或 NULL，则通过检查。

例如：

CREATE TABLE PRODUCTS (
    ID INTEGER PRIMARY KEY,
    PRICE REAL CHECK (PRICE > 0)
);

由于 PRICE 没有声明 NOT NULL，下面的语句可以成功：

INSERT INTO PRODUCTS (PRICE)
VALUES (NULL);

如果业务要求价格必须存在且大于零，应使用：

PRICE REAL NOT NULL CHECK (PRICE > 0)

表级 CHECK 约束

CHECK 也可以用于验证多个列之间的关系。

这里假设日期采用固定的 YYYY-MM-DD 格式保存。

通过表级 CHECK，可以要求结束日期不能早于开始日期。

注意：这个约束只比较字符串大小，不会验证日期本身是否合法。实际业务中还应对输入的日期格式和有效性进行检查。

---

FOREIGN KEY 外键约束

FOREIGN KEY 用于建立不同表之间的引用关系。

例如，员工表中的部门编号应该引用部门表中实际存在的部门。

启用外键检查

SQLite 的外键约束是否被执行，取决于数据库连接中的 foreign_keys 设置和 SQLite 构建配置。

为了避免依赖默认设置，建议在每个需要外键检查的数据库连接中明确执行：

PRAGMA foreign_keys = ON;

可以通过以下语句检查是否启用：

PRAGMA foreign_keys;

如果返回 1，表示当前连接启用了外键约束检查。

注意：不要在已经开始的事务中尝试切换 PRAGMA foreign_keys，因为此时该设置不会立即生效。

实例

上面的插入操作可以成功，因为编号为 1 的部门存在。

如果尝试插入一个不存在的部门编号：

INSERT INTO EMPLOYEES (NAME, DEPARTMENT_ID)
VALUES ('Li Si', 999);

启用外键检查后，SQLite 会拒绝该操作：

FOREIGN KEY constraint failed

外键删除规则

SQLite 可以通过 ON DELETE 配置父记录被删除时的处理方式。

规则	说明
NO ACTION	默认行为，根据约束检查时机决定是否拒绝违反引用关系的操作
RESTRICT	在尝试删除或修改被引用键时，若存在相关子记录则拒绝操作
CASCADE	删除父记录时，自动删除相关子记录
SET NULL	将相关子记录的外键设置为 NULL
SET DEFAULT	将相关子记录的外键设置为默认值，该值仍需满足外键约束

例如：

FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES DEPARTMENTS(ID)
    ON DELETE CASCADE

使用 ON DELETE CASCADE 后，删除部门记录时，与该部门关联的员工记录也会被自动删除。

因此，实际开发中应根据业务需求选择合适的删除规则，避免意外删除关联数据。

---

组合使用多个约束

在实际项目中，一张表通常会同时使用多种约束。

下面创建一个相对完整的员工表，包含主键、非空、唯一、默认值、检查和外键约束。

运行成功后，员工表包含以下数据：

ID	NAME	EMAIL	AGE	SALARY	DEPARTMENT_ID
1	Zhang San	zhang@example.com	28	50000.0	1

该表同时保证：

• 每条记录具有唯一的整数主键。
• 员工姓名不能为空。
• 员工邮箱不能为空且不能重复。
• 员工年龄不小于 18。
• 员工工资大于零，未指定时默认使用 50000.00。
• 部门编号如果不为 NULL，就必须引用存在的部门。

---

删除或修改约束

SQLite 对 ALTER TABLE 的支持与 MySQL、PostgreSQL 等数据库存在差异。

不能直接使用以下语法删除约束：

-- SQLite 不支持此通用语法
ALTER TABLE COMPANY
DROP CONSTRAINT constraint_name;

类似的 DROP PRIMARY KEY、DROP FOREIGN KEY 等语法也不能直接照搬其他数据库的写法。

对于大多数约束修改，SQLite 的通用方法是创建新表、复制数据、删除旧表、重命名新表。

实例：删除 UNIQUE 约束

假设现有员工表定义如下：

CREATE TABLE COMPANY (
    ID INTEGER PRIMARY KEY,
    NAME TEXT NOT NULL,
    EMAIL TEXT UNIQUE
);

INSERT INTO COMPANY (ID, NAME, EMAIL)
VALUES (1, 'Zhang San', 'zhang@example.com');

现在希望取消 EMAIL 列的唯一约束，使不同员工可以使用相同的邮箱。

由于 SQLite 不能直接使用 DROP CONSTRAINT 删除这个约束，可以创建一个不包含 UNIQUE 的新表，并迁移原有数据。

完成后，EMAIL 列不再具有唯一约束。

例如：

INSERT INTO COMPANY (NAME, EMAIL)
VALUES ('Li Si', 'zhang@example.com');

在上述独立表示例中，该语句现在可以成功执行。

重要：上面的 SQL 是简化示例，只适合没有其他依赖对象的简单表。

如果实际数据库中存在外键、触发器、视图或自定义索引，必须先检查并保存这些对象的定义，在迁移后正确重建；还需要验证数据完整性。

对于涉及外键的复杂表结构变更，应按照 SQLite 官方推荐的完整表重建流程操作，包括在事务开始之前处理外键设置、重新创建相关对象，以及执行 PRAGMA foreign_key_check。

添加约束

SQLite 支持使用 ALTER TABLE ... ADD COLUMN 添加新列，并在允许的条件下为新列指定约束。

例如：

ALTER TABLE COMPANY
ADD COLUMN STATUS TEXT NOT NULL DEFAULT 'active';

这会增加一个 STATUS 列，其默认值为 'active'。

需要注意，SQLite 对新增列的约束存在限制。例如，为已有数据的表添加 NOT NULL 列时，通常需要提供非 NULL 的默认值。

如果需要为已有列增加或删除 UNIQUE、PRIMARY KEY 等约束，通常需要根据具体 SQLite 版本及表结构选择重建表方案。

通过唯一索引实现唯一性

如果只是希望为已有列添加唯一性限制，还可以使用独立的唯一索引。

CREATE UNIQUE INDEX idx_company_email
ON COMPANY(EMAIL);

创建成功后，EMAIL 列中的非 NULL 值必须保持唯一。

如果已有数据中存在重复的非 NULL 邮箱，创建唯一索引会失败。

对于通过 CREATE UNIQUE INDEX 创建的独立唯一索引，可以使用：

DROP INDEX idx_company_email;

删除该索引。

但如果唯一索引是由表定义中的 UNIQUE 或 PRIMARY KEY 约束自动创建的，就不能简单地通过 DROP INDEX 删除对应约束。

---

约束冲突处理

当插入或更新的数据违反约束时，SQLite 通常会拒绝该操作并返回错误。

对于部分约束冲突，可以通过 ON CONFLICT 或相关语句选择处理方式。

常见冲突处理方式如下：

处理方式	作用
ABORT	默认处理方式，撤销当前语句已经进行的修改并报告错误
IGNORE	对于适用的约束冲突，跳过冲突行并继续执行
REPLACE	对于适用的 UNIQUE 或 PRIMARY KEY 冲突，删除冲突的旧记录再执行插入或更新
FAIL	报告约束错误，但可以保留当前语句在错误发生之前已经完成的修改
ROLLBACK	在事务中发生适用的约束冲突时，回滚整个事务

实例：INSERT OR IGNORE

假设已有一张邮箱唯一的表：

CREATE TABLE USERS (
    ID INTEGER PRIMARY KEY,
    EMAIL TEXT NOT NULL UNIQUE
);

INSERT INTO USERS (EMAIL)
VALUES ('admin@example.com');

如果再次插入相同邮箱：

INSERT OR IGNORE INTO USERS (EMAIL)
VALUES ('admin@example.com');

对于该唯一性冲突，SQLite 会跳过这次插入，而不是返回普通的 UNIQUE 约束错误。

注意：IGNORE 并不是忽略所有错误。例如，外键约束冲突仍可能导致操作失败。

此外，REPLACE 并非简单地更新已有行。它可能删除冲突记录并插入新记录，因此使用前应了解对关联数据和触发器的影响。

---

查看表中的约束

SQLite 可以通过查询表结构和相关 PRAGMA 命令，了解当前表定义和约束信息。

查看建表语句

SELECT sql
FROM sqlite_schema
WHERE type = 'table'
  AND name = 'COMPANY';

通过原始 CREATE TABLE 语句，可以查看表中定义的 PRIMARY KEY、UNIQUE、CHECK 等约束。

查看列信息

PRAGMA table_info('COMPANY');

该命令可以查看列名称、声明类型、是否标记为非空、默认值以及主键信息等。

查看索引信息

PRAGMA index_list('COMPANY');

可以查看与表关联的索引，包括用于维护唯一性约束的相关索引。

查看外键约束

PRAGMA foreign_key_list('COMPANY');

该命令可以查看表中定义的外键及其引用关系。

还可以使用：

PRAGMA foreign_key_check;

检查数据库中是否存在违反外键约束的记录。

---

常见问题

1、NOT NULL 和 UNIQUE 有什么区别？

NOT NULL 要求列不能为 NULL，但允许重复值。

UNIQUE 要求值不能重复，但在 SQLite 中通常允许多个 NULL。

如果既不允许 NULL，也不允许重复，可以组合使用：

EMAIL TEXT NOT NULL UNIQUE

2、为什么 CHECK (PRICE > 0) 仍然可以插入 NULL？

因为当 PRICE 为 NULL 时，表达式 PRICE > 0 的结果也是 NULL。

SQLite 的 CHECK 只有在表达式结果为零时才会判定失败，因此 NULL 可以通过检查。

应使用：

PRICE REAL NOT NULL CHECK (PRICE > 0)

3、SQLite 的主键为什么有时允许 NULL？

这是 SQLite 早期版本遗留的兼容行为，主要发生在普通行表中非特殊整数主键的场景。

实际开发中，建议使用 INTEGER PRIMARY KEY，或者为其他类型的主键明确声明 NOT NULL。

4、为什么执行 DROP CONSTRAINT 会报错？

因为 SQLite 不支持通用的 ALTER TABLE ... DROP CONSTRAINT 语法。

需要根据约束类型和 SQLite 版本，使用受支持的表结构修改操作或重建表。

5、定义 FOREIGN KEY 后为什么没有阻止非法数据？

应首先检查当前数据库连接是否启用了外键检查：

PRAGMA foreign_keys;

如果没有启用，应在事务之外执行：

PRAGMA foreign_keys = ON;

注意，外键设置通常需要在每个数据库连接中单独处理。

---

总结

SQLite 约束用于保证数据库中的数据符合指定规则，是数据库设计的重要组成部分。

常见约束的用途如下：

约束	主要用途
NOT NULL	禁止列存储 NULL
DEFAULT	提供默认值
UNIQUE	保证列或列组合的唯一性
PRIMARY KEY	定义主键，标识表中的记录
CHECK	验证数据满足条件
FOREIGN KEY	维护表之间的引用关系

使用 SQLite 约束时，需要特别注意以下三个方面：

• SQLite 对 NULL、主键和数据类型的处理与部分其他数据库有所不同。
• 外键约束需要确认当前连接已经启用外键检查。
• 修改已有约束不能直接照搬其他数据库的 DROP CONSTRAINT 语法，复杂场景通常需要重建表。

在创建数据库表时，合理组合这些约束，可以在数据写入阶段发现不符合规则的记录，减少后续数据处理中的错误。

参考文档：

• SQLite CREATE TABLE 官方文档
• SQLite ALTER TABLE 官方文档
• SQLite 外键约束官方文档
• SQLite 约束冲突处理