SQLite STRICT 表与 CHECK 约束：建模阶段拦截脏数据

问题场景

SQLite 默认使用动态类型，列上声明的类型更多是类型亲和性提示。普通表中，即使把明显不符合类型的值写入某列，也可能被接受或被隐式转换。只在应用层校验，容易出现“接口校验漏一处，脏数据进库”的问题。

SQLite 从 3.37 版本开始提供 STRICT 表，可以强制列类型；再配合 CHECK 约束限制状态枚举和数值范围，就能在建表阶段把一部分规则固化为数据库约束。下面用一个订单表演示最小可运行方案。

建表：STRICT + CHECK

-- schema.sql
PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL CHECK (user_id > 0),
  amount     INTEGER NOT NULL CHECK (amount > 0 AND amount <= 1000000),
  status     TEXT    NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  note       TEXT    NOT NULL DEFAULT '' CHECK (length(trim(note)) <= 200),
  created_at TEXT    NOT NULL DEFAULT (datetime('now'))
) STRICT;

INSERT INTO orders (user_id, amount, status, note)
VALUES (1001, 9900, 'pending', '示例订单');

这里做了三件事：用 STRICT 要求列类型必须来自 SQLite 允许的严格类型集合；用 NOT NULL 避免 CHECK 遇到 NULL 时直接放行；用 CHECK 限制 user_id、amount 和 status 的取值范围与枚举值。

运行方法

先确认环境支持 STRICT 表：

sqlite3 --version

若版本低于 3.37，建表时可能报语法错误或无法识别 STRICT。确认版本后执行：

sqlite3 demo.db < schema.sql

也可以进入交互式命令行：

sqlite3 demo.db
.read schema.sql

下面三条错误示例建议在交互式环境中逐条执行，便于观察每一条报错；脚本中一条失败可能影响后续语句的执行观察。

-- 错误类型：把不可转为整数的文本写入 INTEGER 列
INSERT INTO orders (user_id, amount, status)
VALUES ('abc', 9900, 'pending');
-- 预期报错：cannot store TEXT value in INTEGER column orders.user_id

-- 越界值：amount 必须大于 0
INSERT INTO orders (user_id, amount, status)
VALUES (1002, 0, 'pending');
-- 预期报错：CHECK constraint failed

-- 非法枚举：status 不在允许集合中
INSERT INTO orders (user_id, amount, status)
VALUES (1003, 100, 'unknown');
-- 预期报错：CHECK constraint failed

三条语句都失败后，可以确认表中只有最初那条正确记录：

SELECT count(*) AS rows_after_bad_inserts FROM orders;

预期结果为 1。具体错误文本可能随 SQLite 版本略有差异，但约束拦截行为一致。

验证约束生效

用 PRAGMA table_info 查看列类型和 NOT NULL 标记：

PRAGMA table_info(orders);

输出中应能看到 user_id、amount 的类型为 INTEGER，status、note 的类型为 TEXT，且相关列 notnull 为 1。需要注意，PRAGMA table_info 不展示 CHECK 约束，也不直接展示 STRICT 标志，因此还要查看建表语句：

SELECT sql
FROM sqlite_master
WHERE type = 'table' AND name = 'orders';

返回的 DDL 中应包含 STRICT 以及各个 CHECK (...) 表达式。这样既能验证列类型，也能确认约束确实写入了 schema。

边界与限制

1. STRICT 表依赖 SQLite 3.37 及以上版本；部署前要确认 CLI、驱动或嵌入式环境中的 SQLite 版本。
2. STRICT 表只允许 INT、INTEGER、REAL、TEXT、BLOB、ANY 等类型声明；写成 VARCHAR(20)、BOOLEAN、DATETIME 可能导致建表失败，需要改用允许的类型并用 CHECK 表达业务含义。
3. CHECK 表达式结果为 NULL 时视为通过。因此枚举列、数值列应配合 NOT NULL，否则 NULL 可能绕过检查。
4. CHECK 不能引用其他行或其他表，也不能包含子查询；跨表一致性仍需外键、触发器或应用层事务来保证。
5. STRICT 只约束存储类型，不负责去除空白字符串、统一大小写或校验复杂业务规则；这些规则要单独设计 CHECK 或在应用层处理。
6. 已有普通表不能直接改成 STRICT 表，通常需要新建 STRICT 表、迁移并清理不符合类型的数据，再替换旧表。
7. 应用层仍应捕获约束错误并转换为友好提示，不要依赖具体错误文本做业务分支。

把 STRICT 与 CHECK 放在建表阶段，可以让类型错误、非法枚举和明显越界值尽早失败；但它不是万能的，跨表规则和复杂业务校验仍需在应用层与数据库设计之间配合完成。