第 8 章:表设计与约束
写错一条 SQL,改一行就完事;建错一张表,可能要停机迁移。这一章讲的全是"一次做对,后面省事"的决定。
学习目标
- 用四个标准检查一张表设计得好不好
- 知道主键该选自增、UUID 还是复合键,各自的代价
- 会用外键、
NOT NULL、UNIQUE、CHECK把业务规则写进库里 - 理解范式的作用,也知道什么时候该故意反范式
8.1 好表的四个标准
拿到一个需求,先别急着写 CREATE TABLE。用这四个问题过一遍:
| 标准 | 问自己 | 不合格的样子 |
|---|---|---|
| 单一职责 | 这张表只描述一类事物吗? | 一张表里同时存客户和订单 |
| 有主键 | 每一行能被唯一标识吗? | 靠"姓名 + 时间"凑唯一 |
| 空值明确 | 每个字段"没有值"意味着什么? | 分不清"没填"和"填了 0" |
| 规则入库 | 业务规则能用约束表达吗? | 靠代码保证"价格不能为负",总有人绕过 |
第四点最容易被忽略。代码里写 if (price < 0) throw 是应用层校验;数据库里写 CHECK (price >= 0) 是最后一道防线。前者会被并发绕过、被新写的脚本绕过、被手动改库绕过;后者绕不过去。
原则:能写进数据库的规则,就别只写在代码里。
8.2 主键:三种选择
自增整数(推荐默认)
id INTEGER PRIMARY KEYSQLite 里这行有特殊含义:它直接映射到内部的 rowid,插入快、按主键查快、占用小。PostgreSQL 对应 GENERATED BY DEFAULT AS IDENTITY(第 12 章细讲)。
适合:单库、单写入点、数据量在千万级以内。绝大多数业务表都该用它。
UUID
id TEXT PRIMARY KEY -- 存 '550e8400-e29b-41d4-a716-446655440000'优点是不依赖数据库生成,多台机器、离线客户端都能各自生成不冲突的 ID;缺点是占空间大、索引随机写入、人眼不可读。
适合:分布式系统、需要在前端就确定 ID 的场景(比如离线创建后同步)。不要因为"看起来高级"就选它。
复合主键
CREATE TABLE book_tags (
book_id INTEGER NOT NULL REFERENCES books(id),
tag TEXT NOT NULL,
PRIMARY KEY (book_id, tag)
);两列一起才能唯一标识一行。典型场景是"关联表":一本书可以有多个标签,同一个标签不能在一本书上重复。
复合主键的代价是:所有引用它的外键都要带两列,写起来啰嗦。实务中更常见的是"自增主键 + 唯一约束":
id INTEGER PRIMARY KEY,
UNIQUE (book_id, tag)两者效果接近,后者更好引用。
8.3 外键:让"引用"变成强制
order_items.book_id 指向 books.id,这就是外键:
book_id INTEGER NOT NULL REFERENCES books(id)它保证两件事:
- 插入明细时,
book_id必须在books里存在; - 删除书籍时,如果有明细还引用它,操作会被拒绝(默认行为)。
SQLite 的外键默认是关的
这是个必须记住的坑:SQLite 为了兼容旧行为,外键约束默认不启用。每个连接都要先执行:
PRAGMA foreign_keys = ON;init.sql 的第一行就是这个。用 Python 连接时也要记得:
con.execute("PRAGMA foreign_keys = ON")不开启的话,外键只是"写在 schema 里的一句注释",插一个不存在的 book_id 照样成功。
删除时的三种行为
-- 默认:有引用就拒绝删除(RESTRICT / NO ACTION)
customer_id INTEGER NOT NULL REFERENCES customers(id)
-- 级联:删客户时,他的订单一起删
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
-- 置空:删客户时,订单的 customer_id 变成 NULL(该列必须可空)
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL怎么选:
| 场景 | 选择 | 理由 |
|---|---|---|
| 订单属于客户 | RESTRICT(默认) | 订单不能凭空消失,该拦就拦 |
| 草稿、临时附件 | CASCADE | 跟着主体一起清理 |
| 可选关联(如"推荐人") | SET NULL | 推荐人注销了,客户还在 |
CASCADE 要慎用。它很爽,但一次误删主表会连锁删掉一大片,而且没有二次确认。生产环境更常见的做法是"软删除"(加 deleted_at 字段)而不是物理删除。
8.4 四个常用约束
NOT NULL:区分"没填"和"空"
title TEXT NOT NULL
author TEXT -- 允许 NULL:有佚名作品NOT NULL 不只是"防止漏填",它还是语义声明:这一列永远有值,查询时不用考虑 NULL 分支。
反过来,如果一列"允许为空"是有意义的(作者未知、手机号未填),就让它可空,并在文档里写清"NULL 代表什么"。最糟的是所有人都不知道某列的 NULL 是"没填"还是"不适用"。
UNIQUE:唯一但不一定非空
email TEXT NOT NULL UNIQUEUNIQUE 允许多行 NULL(第 3 章提过:NULL 不等于 NULL),所以"唯一且必填"要两个一起写。
唯一约束会自动建索引,查询 WHERE email = ? 会走索引,这也是登录接口能快的原因。
CHECK:把业务规则写进库
price REAL NOT NULL CHECK (price >= 0),
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'refunded'))CHECK 能表达任何"单行内可判断"的规则:范围、枚举、格式(部分)。它不能跨行判断(比如"库存不能小于已售数量",那要用触发器或应用逻辑)。
写 CHECK 的一个技巧:约束名要能读懂。SQLite 不支持给 CHECK 命名,PostgreSQL 支持:
CONSTRAINT price_non_negative CHECK (price >= 0)报错时能直接看到是哪个规则挂了,排查快很多。
DEFAULT:减少调用方的负担
stock INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'pending',
created_at TEXT NOT NULL DEFAULT (datetime('now'))默认值让"最常用的情况"不用写。注意 DEFAULT 只在插入时没提供该列才生效,显式传 NULL 不会触发默认值。
8.5 范式:为什么要拆,什么时候不拆
第 1 章讲过"同一事实存多份"的坏处。范式就是"拆到不重复"的一套规则,常用的到第三范式:
| 范式 | 一句话 | 违反的样子 |
|---|---|---|
| 1NF | 每列存一个原子值 | 一个 tags 列存 "文学,历史" |
| 2NF | 非主键列不依赖复合主键的一部分 | 关联表里冗余存了书名 |
| 3NF | 非主键列不依赖其他非主键列 | 订单表存了客户城市(城市依赖客户,不依赖订单) |
小书店的四张表基本满足 3NF:客户信息在 customers,书籍信息在 books,订单只存 customer_id,明细只存 book_id 和成交价。
外键方向就是"多的那一侧指向一的那一侧":明细指向订单,订单指向客户,明细指向书。
反范式:两个必须知道的例外
例外一:成交价必须冗余。
order_items.unit_price 存的是下单时的价格,它和 books.price 会不一致——这不是冗余错误,是历史快照。书涨价了,历史订单的金额不能跟着变,否则对账永远对不上。
判断标准:这个值是"当前状态"还是"发生时的事实"。当前状态可以派生,事实必须落库。
例外二:高频统计可以预聚合。
如果首页要显示"每本书累计销量",每次都 JOIN + GROUP BY 全表会很慢,可以加一列 sales_count,由写入时更新。代价是要保证它和明细始终一致(事务里一起改,第 10 章)。
反范式的通用原则:先按范式设计,有实测的性能问题再反范式。反过来做(一开始就到处冗余)几乎一定会得到一份对不上的数据。
8.6 字段设计的几个惯例
这些不是硬规则,但能让表在半年后还看得懂:
| 惯例 | 做法 | 原因 |
|---|---|---|
| 命名 | 统一 snake_case,表用复数 | order_items 比 OrderItems 跨库兼容 |
| 时间 | 存 UTC,用 TEXT(YYYY-MM-DDTHH:MM:SSZ)或 timestamptz | 避免时区歧义,展示层再转本地 |
| 金额 | 整数存"分",或 PostgreSQL 的 NUMERIC(12,2) | 浮点数有精度误差,第 0.1 节那个 59.699... 就是教训 |
| 布尔 | SQLite 用 INTEGER 存 0/1,PostgreSQL 用 BOOLEAN | SQLite 没有原生布尔 |
| 枚举 | TEXT + CHECK,不用魔法数字 | status = 'paid' 比 status = 2 可读 |
| 外键列 | 加索引(多数库不会自动加) | 否则 JOIN 和级联删除都会全表扫 |
金额那条值得展开:REAL 是二进制浮点,0.1 + 0.2 不等于 0.3。订单金额差一分钱,财务就会来找你。用整数存分(1990 表示 19.90 元)是最稳的做法,SQLite 和 PostgreSQL 都适用。
8.7 设计演练:加一张评论表
需求:用户可以给书打分并留言,一本书可以有多条评论,评分 1–5。
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
customer_id INTEGER NOT NULL REFERENCES customers(id),
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
content TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
UNIQUE (book_id, customer_id)
);逐个决定解释一遍:
book_id用CASCADE:书下架了,评论没有保留意义;customer_id用默认RESTRICT:删客户前得先处理他的评论,不能悄悄带走;rating用CHECK BETWEEN 1 AND 5:评分范围是硬规则,必须入库;content可空:允许只打分不留言;UNIQUE (book_id, customer_id):一个人对一本书只能评论一次。
这张表建完,业务规则已经固化了 80%,剩下 20%(比如"只能评论买过的书")才需要应用层判断。
本章小结
| 要点 | 说明 |
|---|---|
| 好表的四个标准 | 单一职责、有主键、空值明确、规则入库 |
| 主键优先自增整数 | UUID 只在分布式/离线场景才划算 |
| SQLite 外键默认关闭 | 每个连接都要 PRAGMA foreign_keys = ON |
CASCADE 慎用 | 误删会连锁,生产多用软删除 |
UNIQUE 允许多行 NULL | 唯一且必填要加 NOT NULL |
| 能入库的规则就别只写代码 | CHECK 是最后一道防线 |
| 先范式,后反范式 | 成交价是必须冗余的历史快照 |
| 金额用整数存分 | 浮点数会差钱 |
下一章
表建好了,数据也进去了,接下来会遇到第一个真实问题:数据一多就慢。下一章讲索引——它为什么能把"扫 10 万行"变成"查 3 次",以及为什么你建了索引却没生效。