Skip to content
Lv1SQL 与数据库进度 8 / 12

第 8 章:表设计与约束 ​

写错一条 SQL,改一行就完事;建错一张表,可能要停机迁移。这一章讲的全是"一次做对,后面省事"的决定。


学习目标 ​

  • 用四个标准检查一张表设计得好不好
  • 知道主键该选自增、UUID 还是复合键,各自的代价
  • 会用外键、NOT NULL、UNIQUE、CHECK 把业务规则写进库里
  • 理解范式的作用,也知道什么时候该故意反范式

8.1 好表的四个标准 ​

拿到一个需求,先别急着写 CREATE TABLE。用这四个问题过一遍:

标准问自己不合格的样子
单一职责这张表只描述一类事物吗?一张表里同时存客户和订单
有主键每一行能被唯一标识吗?靠"姓名 + 时间"凑唯一
空值明确每个字段"没有值"意味着什么?分不清"没填"和"填了 0"
规则入库业务规则能用约束表达吗?靠代码保证"价格不能为负",总有人绕过

第四点最容易被忽略。代码里写 if (price < 0) throw 是应用层校验;数据库里写 CHECK (price >= 0) 是最后一道防线。前者会被并发绕过、被新写的脚本绕过、被手动改库绕过;后者绕不过去。

原则:能写进数据库的规则,就别只写在代码里。


8.2 主键:三种选择 ​

自增整数(推荐默认) ​

sql
id INTEGER PRIMARY KEY

SQLite 里这行有特殊含义:它直接映射到内部的 rowid,插入快、按主键查快、占用小。PostgreSQL 对应 GENERATED BY DEFAULT AS IDENTITY(第 12 章细讲)。

适合:单库、单写入点、数据量在千万级以内。绝大多数业务表都该用它。

UUID ​

sql
id TEXT PRIMARY KEY  -- 存 '550e8400-e29b-41d4-a716-446655440000'

优点是不依赖数据库生成,多台机器、离线客户端都能各自生成不冲突的 ID;缺点是占空间大、索引随机写入、人眼不可读。

适合:分布式系统、需要在前端就确定 ID 的场景(比如离线创建后同步)。不要因为"看起来高级"就选它。

复合主键 ​

sql
CREATE TABLE book_tags (
    book_id INTEGER NOT NULL REFERENCES books(id),
    tag     TEXT    NOT NULL,
    PRIMARY KEY (book_id, tag)
);

两列一起才能唯一标识一行。典型场景是"关联表":一本书可以有多个标签,同一个标签不能在一本书上重复。

复合主键的代价是:所有引用它的外键都要带两列,写起来啰嗦。实务中更常见的是"自增主键 + 唯一约束":

sql
id INTEGER PRIMARY KEY,
UNIQUE (book_id, tag)

两者效果接近,后者更好引用。


8.3 外键:让"引用"变成强制 ​

order_items.book_id 指向 books.id,这就是外键:

sql
book_id INTEGER NOT NULL REFERENCES books(id)

它保证两件事:

  1. 插入明细时,book_id 必须在 books 里存在;
  2. 删除书籍时,如果有明细还引用它,操作会被拒绝(默认行为)。

SQLite 的外键默认是关的 ​

这是个必须记住的坑:SQLite 为了兼容旧行为,外键约束默认不启用。每个连接都要先执行:

sql
PRAGMA foreign_keys = ON;

init.sql 的第一行就是这个。用 Python 连接时也要记得:

python
con.execute("PRAGMA foreign_keys = ON")

不开启的话,外键只是"写在 schema 里的一句注释",插一个不存在的 book_id 照样成功。

删除时的三种行为 ​

sql
-- 默认:有引用就拒绝删除(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:区分"没填"和"空" ​

sql
title TEXT NOT NULL
author TEXT          -- 允许 NULL:有佚名作品

NOT NULL 不只是"防止漏填",它还是语义声明:这一列永远有值,查询时不用考虑 NULL 分支。

反过来,如果一列"允许为空"是有意义的(作者未知、手机号未填),就让它可空,并在文档里写清"NULL 代表什么"。最糟的是所有人都不知道某列的 NULL 是"没填"还是"不适用"。

UNIQUE:唯一但不一定非空 ​

sql
email TEXT NOT NULL UNIQUE

UNIQUE 允许多行 NULL(第 3 章提过:NULL 不等于 NULL),所以"唯一且必填"要两个一起写。

唯一约束会自动建索引,查询 WHERE email = ? 会走索引,这也是登录接口能快的原因。

CHECK:把业务规则写进库 ​

sql
price REAL NOT NULL CHECK (price >= 0),
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'refunded'))

CHECK 能表达任何"单行内可判断"的规则:范围、枚举、格式(部分)。它不能跨行判断(比如"库存不能小于已售数量",那要用触发器或应用逻辑)。

写 CHECK 的一个技巧:约束名要能读懂。SQLite 不支持给 CHECK 命名,PostgreSQL 支持:

sql
CONSTRAINT price_non_negative CHECK (price >= 0)

报错时能直接看到是哪个规则挂了,排查快很多。

DEFAULT:减少调用方的负担 ​

sql
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 用 BOOLEANSQLite 没有原生布尔
枚举TEXT + CHECK,不用魔法数字status = 'paid' 比 status = 2 可读
外键列加索引(多数库不会自动加)否则 JOIN 和级联删除都会全表扫

金额那条值得展开:REAL 是二进制浮点,0.1 + 0.2 不等于 0.3。订单金额差一分钱,财务就会来找你。用整数存分(1990 表示 19.90 元)是最稳的做法,SQLite 和 PostgreSQL 都适用。


8.7 设计演练:加一张评论表 ​

需求:用户可以给书打分并留言,一本书可以有多条评论,评分 1–5。

sql
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 次",以及为什么你建了索引却没生效。

👉 第 9 章:索引与执行计划

本文阅读--次|全站访问--次|访客--人