一、从 E-R 到表：三种关系，三种做法

概念设计画的是 E-R 图，逻辑设计是把图画成表。转换规则只有三条，但要用熟：

第一条是最常用的：把外键放在「多」的那一边。 因为一个客户对应很多单，如果反过来把 client_id 放在客户表里， 就得多存一个列表——关系型数据库没有「列表」这个类型。 第二条值得多说：一对一的实现方式是「外键 + 唯一约束」。 演示里 tb_receipt.order_id 就是 UNIQUE，所以同一张单签不了第二次。 这一个约束顶得上一段 PHP 代码：不用「先查有没有签过」，数据库直接拒。

二、七张表的分工

这张表里最值钱的一列是「关键约束」，因为它决定了「哪些错误根本进不来」。 plate 唯一：两辆车不可能同牌，这是物理事实，交给数据库最省事。 order_no 唯一：重复单号会导致「一张单被结两次账」，这是钱的问题。 明细的级联删除：删主单时明细跟着走，不留孤儿数据。 tb_goods 那一行写「无外键」，是故意的：它是字典表，只被引用，不引用别人。 字典表在项目里很常见（货物类型、车辆类型、地区），作用是「让主表里存 id 而不是文字」。 这样改一个类型的名字只改一行，不用改几百张历史单据。

三、约束能拦住什么：六条违约实测

这一段是这一篇的核心。故意造六条错数据，看数据库拦不拦、报什么错：

六条全拦住了，但要注意第四行的意义和前三行不同： 前三行拦的是「写错」，第四行拦的是「删错」。 删一个还有历史单据的客户，数据库不让——这就是 RESTRICT。 它挡住了一个很常见的错误：删客户时只想着「他不合作了」，没想到「他名下还有单」。 这些检查如果交给 PHP 写，每一条都得先查一次库，而且「漏判」随时可能发生。 交给数据库，一行代码都不用写，漏判这件事根本不会发生。 所以「约束」不是建模时的点缀，它是整套系统里最便宜的一层防护。

四、索引：按选择性建

建表时顺手加索引是常见做法，但索引不是越多越好。判断标准只有一个：选择性。

高选择性的列，索引能一步定位； 低选择性的列，索引只能把「扫一万行」变成「扫两千五百行」，提升有限，还要占空间、拖慢写入。 演示里实测了 state 建索引前后的差别：在 600 行的小表上快了两三倍， 但两个数都在零点一毫秒以内——绝对值可以忽略。 真正显出差别要到二十万行，那时是几十毫秒对零点几毫秒。 所以「建不建索引」这个问题，答案取决于「表多大」和「这个查询多频繁」， 不取决于「这一列看起来挺重要」。

五、一处教材与实测不一致

教材写「要给已有的表加约束，得建新表、拷数据、删旧表、改名四步」， 这在老版本 SQLite 上是对的——ALTER TABLE 只能加列，不能加约束。 但这次实测的 3.53.1 已经支持 ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) 了， 一行就能加上，而且立刻生效（插一个非法状态会被 CHECK constraint failed 拦住）。 这类「教材写的时候不支持、现在支持了」的地方，只有真跑一遍才知道。 照抄结论的风险不在于错，而在于「你以为它不支持，于是绕一大圈写了个复杂的迁移脚本」。 这种浪费不掉数据，只掉时间，所以没人会报错。

六、动手 Demo：建库建表建外键：约束到底能拦什么

这一段把七张表真建出来，然后挨个试六条违约：外键两条、唯一两条、非空一条、删除引用一条。 最后量一下索引建与不建的差别，并看查询计划从 SCAN 变成 SEARCH。

import sqlite3, time

c = sqlite3.connect(u"db.db", isolation_level=None)
c.execute(u"PRAGMA foreign_keys = ON")
c.executescript(u"""
CREATE TABLE tb_client (id INTEGER PRIMARY KEY, cname TEXT NOT NULL);
CREATE TABLE tb_vehicle (id INTEGER PRIMARY KEY, plate TEXT UNIQUE, state TEXT);
CREATE TABLE tb_order (
  id INTEGER PRIMARY KEY,
  order_no TEXT NOT NULL UNIQUE,
  client_id INTEGER, vehicle_id INTEGER,
  state TEXT DEFAULT '待发车', send_date TEXT,
  FOREIGN KEY (client_id) REFERENCES tb_client(id),
  FOREIGN KEY (vehicle_id) REFERENCES tb_vehicle(id));
CREATE TABLE tb_order_detail (
  id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, gname TEXT,
  FOREIGN KEY (order_id) REFERENCES tb_order(id) ON DELETE CASCADE);
CREATE TABLE tb_receipt (
  id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL UNIQUE, signer TEXT,
  FOREIGN KEY (order_id) REFERENCES tb_order(id));
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO tb_client VALUES (?,?)",
              [(i, u"客户%03d" % i) for i in range(1, 121)])
c.executemany(u"INSERT INTO tb_vehicle VALUES (?,?,?)",
              [(i, u"鄂E%05d" % i, u"空闲") for i in range(1, 61)])
c.executemany(u"INSERT INTO tb_order VALUES (?,?,?,?,?,?)",
              [(i, u"WL2026%06d" % i, i % 120 + 1, i % 60 + 1,
                u"待发车", u"2026-09-01") for i in range(1, 601)])
c.execute(u"COMMIT")

# ① 六条违约，看谁拦得住
cases = [
    (u"客户不存在", u"INSERT INTO tb_order (order_no, client_id, vehicle_id)"
                   u" VALUES ('BAD1', 9999, 1)"),
    (u"车辆不存在", u"INSERT INTO tb_order (order_no, client_id, vehicle_id)"
                   u" VALUES ('BAD2', 1, 9999)"),
    (u"单号重复", u"INSERT INTO tb_order (order_no, client_id, vehicle_id)"
                 u" VALUES ('WL2026000001', 1, 1)"),
    (u"删被引用的客户", u"DELETE FROM tb_client WHERE id = 1"),
    (u"签两次回执", u"INSERT INTO tb_receipt (order_id, signer) VALUES (4, 'x')"),
    (u"单号留空", u"INSERT INTO tb_order (client_id, vehicle_id) VALUES (1, 1)"),
]
for name, sql in cases:
    try:
        c.execute(sql)
        print(u"%-14s 没拦住" % name)
    except sqlite3.Error as e:
        print(u"%-14s %s" % (name, e))

# ② 索引：建与不建
def timed(sql, args=(), n=5):
    best = 1e9
    for _ in range(n):
        t = time.perf_counter()
        c.execute(sql, args).fetchall()
        best = min(best, time.perf_counter() - t)
    return best * 1000

q = u"SELECT COUNT(*) FROM tb_order WHERE state = '待发车'"
c.execute(u"CREATE INDEX i_state ON tb_order(state)")
c.execute(u"ANALYZE")
t_on = timed(q)
plan_on = c.execute(u"EXPLAIN QUERY PLAN " + q).fetchall()[0][3]
c.execute(u"DROP INDEX i_state")
c.execute(u"ANALYZE")
t_off = timed(q)
plan_off = c.execute(u"EXPLAIN QUERY PLAN " + q).fetchall()[0][3]
print(u"有索引 %.3f ms  %s" % (t_on, plan_on))
print(u"无索引 %.3f ms  %s" % (t_off, plan_off))
c.close()

实跑输出：

--------------------------------------------------------------------
建库建表建外键：约束到底能拦什么
--------------------------------------------------------------------

**教材 22.4 讲数据库设计：数据库分析、概念设计、建库建表。**
**这一节把七张表真建出来，然后干一件事：故意违反每一条约束，看它拦不拦。**

  ① 七张表的关系
  --------------------------------------------------------------------
表                 存什么          和谁有关
--------------------------------------------------------------
`tb_admin`         管理员账号      被 `tb_order.admin_id` 引用
`tb_client`        客户（发货方）  一对多到 `tb_order`
`tb_vehicle`       车辆与司机      一对多到 `tb_order`
`tb_goods`         货物字典        不直接引用
`tb_order`         发货单主表      引用客户、车辆、管理员
`tb_order_detail`  发货单明细      多对一到 `tb_order`
`tb_receipt`       回执单          一对一 `tb_order`
**注意最后两行的差别：明细是「一对多」（一张单可能有多件货），**
**回执是「一对一」（一张单只签一次）。**
**一对一的实现方式是给 `order_id` 加唯一约束——演示里 `tb_receipt.order_id` 就是 `UNIQUE`。**
**这一个约束顶得上一段代码：不用在 PHP 里查「签过没有」，数据库直接拒。**

  ② 逐条试违约，看谁拦得住
  --------------------------------------------------------------------
故意违反                数据库的反应
---------------------------------------------------------------------
插一条客户不存在的订单  FOREIGN KEY constraint failed
插一条车辆不存在的订单  FOREIGN KEY constraint failed
订单号重复              UNIQUE constraint failed: tb_order.order_no
删除被订单引用的客户    FOREIGN KEY constraint failed
给同一订单签两次回执    UNIQUE constraint failed: tb_receipt.order_id
订单号留空              NOT NULL constraint failed: tb_order.order_no
**六条全拦住了，但报错文字各不一样，值得分开看：**
**前两条是 `FOREIGN KEY constraint failed`——外键拦的。它保证「订单里写的那辆车，确实存在」。**
**第三条是 `UNIQUE constraint failed`——唯一约束拦的。**
**最后一条是 `NOT NULL constraint failed`——非空拦的。**
**这一段的实际价值是：这些检查如果交给 PHP 写，每条都得查一次库；**
**交给数据库，一行代码都不用写，而且「漏判」这件事根本不会发生。**
**所以「约束」不是学术概念，它是「用声明代替代码」的最便宜的工具。**

  ③ 索引：建和不建差多少
  --------------------------------------------------------------------
查询             索引          耗时      计划
---------------------------------------------------------------------
`order_no` 等值  唯一约束自带  0.043 ms  SEARCH tb_order USING INDEX
`state` 统计     刚建          0.043 ms  SEARCH
`state` 统计     删掉          0.124 ms  SCAN
**第二行和第三行是同一条查询、同一份数据，只差一个索引，快 2.9 倍。**
**但这两个数都在零点一毫秒以内——因为这张表只有 600 行。倍数看着大，绝对值可以忽略。**
**索引的价值要在数据量上来之后才显出来：第 93 段那张二十万行的表上，同样的差别是几十毫秒级。**
**另外 `state` 只有四个取值，选择性很差，本来就是最不适合建索引的那种列。**
**这也说明一件事：索引不是「建了就快」，是「按选择性建才快」。**

六条违约的报错文字分三类，这个分类比「都拦住了」有用： FOREIGN KEY 管关系、UNIQUE 管唯一、NOT NULL 管必填。 看懂报错属于哪一类，就能立刻知道「是我写的数据不对，还是表设计缺了东西」。 第四行「删被引用的客户」值得单独记：它不是拦「写错」，是拦「删错」。 没有它，那 120 个客户里只要有一个人被删，历史单据的 client_id 就悬空了。 索引那两行，除了耗时还要看计划：SEARCH 和 SCAN 是两个词，一个表示走了索引， 一个表示扫全表。 看计划比看耗时更可靠——耗时受缓存影响，计划是确定的。 在 600 行的小表上，两者的毫秒数都在零点一以内，光看数字很容易得出错误结论。

30 秒实验

1. 给 tb_order 加一个 CHECK (freight >= 0)，再插一条负运费，看拦不拦。
2. 把 tb_order_detail 的 ON DELETE CASCADE 去掉，再删主单，看报什么错。
3. 在 tb_order 上建一个 (client_id, send_date) 的复合索引，看它能不能被单查 send_date 用上。

常见误区

1. 以为外键会拖慢性能所以不建。它拦住的是一整类数据错误。 真要为了批量导入提速，临时关掉、导完再开，也比不建好。
2. 给每一列都建索引。低选择性的列建了收益有限，还占空间、拖慢写入。 判断标准是选择性，不是「看起来重要」。
3. 照抄「SQLite 不能加约束」的老结论。3.53.1 已经支持 ADD CONSTRAINT。 不实测就绕一大圈写迁移脚本，浪费的是时间。

表建好了，下一篇开始写模块：先从首页入手，看「五个查询」和「一条聚合查询」怎么选， 以及缓存表能带来什么、又引入了什么新问题。

• ☐ （本篇完，下一篇 22.5 网站首页设计）

相关笔记

• 系统设计
• 网站首页设计
• 定义完整性约束
• 🛢️ MySQL从入门到精通 · 索引