SQLite 教学工作区 · 速查卡 · 配合课程 0002

表设计与约束速查

设计一张表时,先把列归到四类里,再逐列问「它该有什么约束」。来源:官方 CREATE TABLE、AUTOINCREMENT、ON CONFLICT。

四类列(设计检查清单)

类别	典型列	自问
身份	主键、业务指纹(hash/编号/唯一名)	一行"是同一行"的判据是什么?单列够吗,还是要组合?
业务内容	名称、正文、分类、外键	哪些允许为空?为空的语义是什么("未知"还是"不适用")?
生命周期	status、expires_at、superseded_by、deleted_at	删除是 `DELETE` 还是标记?历史要不要留?
计数与时间	seen_count、retrieval_count、created_at / updated_at / last_seen_at	每个计数/时间各自回答什么问题?能不能合并?

约束速查

写法	作用	注意
`PRIMARY KEY`	唯一标识一行	`INTEGER PRIMARY KEY` 是 rowid 别名,本身自动分配编号
`AUTOINCREMENT`	额外保证:编号永不复用	要配 `INTEGER PRIMARY KEY` 用;多一张 `sqlite_sequence` 表,通常不需要
`NOT NULL`	不允许空值	参与 `UNIQUE` 的列应加它,否则去重有漏洞
`DEFAULT 值`	插入时没给值就用它	只对"没写这一列"生效;显式写 `NULL` 仍会写空
`UNIQUE(列…)`	这些列的组合全表唯一	单列唯一、多列组合唯一都写这里;自动建索引
`CHECK (表达式)`	写入必须满足表达式	最划算的约束:枚举取值、范围、长度都靠它
`REFERENCES 表(列)`	外键	SQLite 默认不强制外键,要每次连接执行 `PRAGMA foreign_keys = ON;`
`COLLATE NOCASE`	比较/唯一性忽略大小写	加在列定义后;`UNIQUE` 会跟着变成大小写不敏感

写入冲突怎么处理(ON CONFLICT)

INSERT OR IGNORE INTO t (…);      -- 冲突就跳过这一行
INSERT OR REPLACE INTO t (…);     -- 冲突就删旧行插新行(注意:是删除!触发器会跑)
INSERT INTO t (…) ON CONFLICT(列) DO UPDATE SET 列 = excluded.列;   -- 更精确的 upsert
-- 列级写法:某列 NOT NULL ON CONFLICT REPLACE

默认行为是 ABORT:报错并回滚这条语句。先想清楚"重复意味着什么",再选冲突策略 —— REPLACE 会静默删掉旧行,常被误用。

常见报错 → 含义

报错	含义
`UNIQUE constraint failed: 表.列, 表.列`	撞了唯一键;报错里列出的是参与该约束的全部列
`CHECK constraint failed: 表达式`	某个 `CHECK` 没过,后面跟的是那条表达式
`NOT NULL constraint failed: 表.列`	该列不允许空,你没给值或给了 `NULL`
`FOREIGN KEY constraint failed`	外键指向的行不存在(需已开 `PRAGMA foreign_keys=ON`)
`database is locked`	别人正在写;见 When To Use SQLite 的并发一节

索引:列顺序就是查询入口顺序

CREATE INDEX idx ON t (a, b, c);
-- 能用上:WHERE a=? / WHERE a=? AND b=? / WHERE a=? AND b=? AND c=?
-- 用不上:WHERE b=? / WHERE c=?            (最左前缀原则)
-- 部分索引用不上:c 单独出现、或中间列用范围条件(WHERE a=? AND b>? 之后 c 不再走索引)

多租户/多项目型表,习惯把"切分维度"放最左(如本机 context.db 的索引都以 project_path 开头)。

一张表写完后的自检(四问)

有没有哪列"应该唯一"却没加 UNIQUE?
有没有哪列"取值只有几种"却没加 CHECK?
有没有哪个 NOT NULL 列其实经常为空(说明建模错了)?
删除数据时,我是真的想删掉历史吗?