表设计与约束

S3-002必学进阶约 90 分钟JDK 21企业开发基础 · MySQL 与数据建模

1. 本节定位

《SQL 基础》学会了写 SQL，本节解决更靠前的问题：表怎么建。

表结构一旦定下来，改动成本很高（要改数据、改代码、改索引）。而约束是表设计里最容易被忽略、但收益最直接的部分——它让数据库替你守住底线。

2. 学习目标

完成本节后，你应该能够：

说出六类约束的作用与适用场景；
说清主键与唯一约束的区别（一个表能有几个？允许 NULL 吗？）；
配置外键的删除行为（RESTRICT / CASCADE / SET NULL）；
为常见字段选择合适的数据类型；
说清为什么金额不能用浮点数，并写出两种正确做法；
解释"约束是最后一道防线"——为什么应用层校验不能替代它。

3. 前置知识检查

会写 CREATE TABLE 吗？（《SQL 基础》）
知道 Java 里 double 与 BigDecimal 的差别吗？（《线程与同步》提过线程安全，本节讲精度）

4. 为什么需要这个知识

应用层的校验一定会被绕过——这不是猜测，是必然会发生的：

绕过方式	例子
多入口	接口做了校验，但导入功能没做
直接改库	运维手工 `UPDATE`，或数据修复脚本
并发	两个请求同时通过"查重"，然后都插入
历史数据	更早的版本没有这条校验
其他系统	另一个服务也写这张表（数据共享）

约束能挡住所有这五种情况——因为它在数据层，不在业务层。

一句话认知：

应用层校验是为了给用户友好提示；数据库约束是为了保证数据永远正确。

两者不是二选一，而是都要有。

5. 核心概念

5.1 六类约束

约束	写法	作用	典型场景
主键	`PRIMARY KEY`	唯一标识一行，非空 + 唯一	每张表都该有
非空	`NOT NULL`	不允许 NULL	必填字段
唯一	`UNIQUE`	值不重复（允许多个 NULL）	手机号、订单号
默认值	`DEFAULT`	不传时用默认值	状态、创建时间
检查	`CHECK`	值必须满足条件	金额 > 0、状态在白名单内
外键	`FOREIGN KEY`	引用的记录必须存在	订单 → 用户

一个容易忽略的事实：约束不只是"防错"，它还会自动创建索引——主键与唯一约束都会生成索引，这也是"查重查询很快"的原因（《索引》）。

5.2 主键 vs 唯一约束

	主键	唯一约束
一个表能有几个	1 个	多个
允许 NULL 吗	不允许	允许（MySQL 里多个 NULL 被视为互不重复）
能否被外键引用	✅ 常被引用	✅ 也能（但少见）
是否自动建索引	✅	✅

实践建议：

每张表都要有主键——没有主键的表难以定位单行、复制也不安全；
业务上的"唯一"用 UNIQUE（如手机号），主键用无意义的自增 ID 或 UUID。

为什么不建议用"手机号"当主键：手机号可能变更，而主键一旦被引用就不能改（要改就得级联更新所有引用它的表）。用无意义的代理键，业务字段变了也不影响关系。

5.3 外键的三种删除行为

当被引用的记录被删除时，外键可以配置三种反应：

行为	写法	效果	适用
拒绝（默认）	`ON DELETE RESTRICT`	有子记录时不允许删父记录	最安全，默认选它
级联删除	`ON DELETE CASCADE`	删父记录时连带删子记录	子记录脱离父记录无意义（如订单明细）
置空	`ON DELETE SET NULL`	把子记录的外键设为 NULL	子记录独立存在（如文章的作者被删）

CASCADE 要谨慎：它意味着"删一个用户，他的所有订单都被删"——在生产环境里往往是灾难。

实践建议：

场景	选择
订单 → 订单明细	`CASCADE`（明细脱离订单没有意义）
订单 → 用户	`RESTRICT`（用户不该被物理删除，应改为"注销"状态）
文章 → 分类	`SET NULL` 或 `RESTRICT`

更常见的做法：业务数据不做物理删除，用 status / deleted_at 标记（软删除）——这样外键的级联问题根本不出现。

5.4 数据类型选择

用途	推荐	避免	原因
主键	`BIGINT AUTO_INCREMENT`	`INT`（会耗尽）	`INT` 上限约 21 亿
金额	`BIGINT`（存分）或 `DECIMAL(12,2)`	`FLOAT` / `DOUBLE`	浮点有精度误差
字符串	`VARCHAR(n)`	`CHAR(n)`（除非定长）	`CHAR` 会补空格
状态	`VARCHAR(16)` 或 `TINYINT`	—	枚举值加 `CHECK`
时间	`DATETIME` / `TIMESTAMP`	—	`TIMESTAMP` 有时区语义、范围小
布尔	`TINYINT(1)` 或 `BIT`	—	MySQL 没有真正的 boolean
大文本	`TEXT` / `LONGTEXT`	放在主表	影响主表行大小与查询

VARCHAR(n) 的 n 要选多大：按业务上限来（如用户名 32、手机号 20）。不要一律 VARCHAR(255)——虽然 MySQL 8 里它不占额外空间，但会误导读者、也可能影响索引长度限制。

5.5 金额不能用浮点数（本节重点）

先看一个"确定会出错"的实验：

java

System.out.println(0.1 + 0.2);        // 输出 0.30000000000000004

原因：浮点数用二进制存储，而 0.1、0.2 这类十进制小数在二进制里是无限循环小数——只能近似存储。相加后误差被放大并暴露出来。

两种正确做法：

做法	写法	优点	缺点
整数存"分"	`price_in_cents BIGINT`	完全精确、运算快、无歧义	展示时要除以 100
定点数存"元"	`DECIMAL(12, 2)`	精确、直接读	运算比整数稍慢

本项目用的是第一种（price_in_cents）——因为：

整数运算不会有任何精度问题；
Java 侧可以用 long 或 BigDecimal，不用考虑舍入模式；
很多支付系统的 API（如微信支付）本身就要求"以分为单位"。

如果选第二种：Java 侧必须用 BigDecimal（不能用 double），并且明确 scale 与舍入模式：

java

BigDecimal price = new BigDecimal("199.00");
BigDecimal total = price.multiply(new BigDecimal("3"));
// 需要舍入时显式指定模式，不要依赖默认行为
total.setScale(2, RoundingMode.HALF_UP);

绝对不要做的事：

java

double price = 199.00;                       // ✗ 精度问题
float total = price * 3;                     // ✗ 误差会累积
if (total == 597.00) { }                     // ✗ 浮点数不能用 == 比较

5.6 自增 ID vs UUID

	自增 ID	UUID
长度	8 字节	16 字节（或 36 字符）
索引效率	高（顺序写入）	低（随机写入，页分裂）
可猜测性	容易被猜（`/orders/1`、`/orders/2`）	不可猜
分布式唯一	❌	✅
适用	单库、内部 ID	需要跨库唯一、对外暴露的 ID

折中方案（推荐）：

内部主键：BIGINT AUTO_INCREMENT（索引友好）
对外的业务 ID：VARCHAR(32) 存 UUID / 雪花 ID（不可猜测）

为什么不直接用 UUID 当主键：UUID 是随机的，插入时索引的写入位置也随机——在 InnoDB 里会导致页分裂与索引碎片，写入性能明显下降（《索引》会讲索引结构）。

顺带一个安全问题：对外暴露自增 ID 会泄露业务量（新注册用户看到 /orders/100000，就知道系统有多少订单）——对外一律用不可猜测的 ID。

6. 递进示例

6.1 最小示例：一张带约束的表

sql

CREATE TABLE orders (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no        VARCHAR(32) NOT NULL UNIQUE,
    user_id         BIGINT NOT NULL,
    amount_in_cents BIGINT NOT NULL,
    status          VARCHAR(16) NOT NULL DEFAULT 'CREATED',
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_amount CHECK (amount_in_cents > 0)
);

六个约束在一张表里全用上了——这是大多数业务表的标准形态。

6.2 业务示例：约束帮我们挡住什么

见第 7 节的五条真实报错——每一条都对应一种"如果没约束就会进库的坏数据"。

6.3 反例：只靠应用层校验

java

// 应用层校验：看起来没问题
if (amount <= 0) {
    throw new IllegalArgumentException("金额必须为正");
}
orderRepository.save(order);

它挡不住的情况：

情况	结果
另一个服务直接写这张表	负金额进库
运维手工 SQL 修复数据	负金额进库
并发下两个请求都通过校验	重复数据进库
老版本代码（没有这段校验）	负金额进库

加上 CHECK (amount_in_cents > 0) 后：无论从哪条路进来，数据库都会拒绝。

7. 完整可运行代码

文件：labs/sql-basics/src/main/java/cn/kb/learn/sql/ConstraintLesson.java（节选）

java

public class ConstraintLesson {
    public static void main(String[] args) {
        try (SqlRunner db = SqlRunner.open()) {
            System.out.println("=== 1. 建表时声明约束 ===");
            db.execute("""
                    CREATE TABLE orders (
                        id              BIGINT AUTO_INCREMENT PRIMARY KEY,
                        order_no        VARCHAR(32) NOT NULL UNIQUE,
                        user_id         BIGINT NOT NULL,
                        amount_in_cents BIGINT NOT NULL,
                        status          VARCHAR(16) NOT NULL DEFAULT 'CREATED',
                        created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        CONSTRAINT chk_amount CHECK (amount_in_cents > 0)
                    )
                    """);

            System.out.println("=== 2. 正常插入：默认值自动生效 ===");
            db.execute("INSERT INTO orders (order_no, user_id, amount_in_cents) "
                    + "VALUES ('ORD-001', 1, 19900)");
            System.out.print(db.query(
                    "SELECT order_no, status, amount_in_cents, created_at IS NOT NULL AS has_created_at FROM orders"));

            System.out.println("=== 3. 违反约束会被数据库拒绝 ===");
            expectFailure(db, "主键重复",
                    "INSERT INTO orders (id, order_no, user_id, amount_in_cents) VALUES (1, 'ORD-002', 1, 100)");
            expectFailure(db, "唯一约束冲突（order_no 重复）",
                    "INSERT INTO orders (order_no, user_id, amount_in_cents) VALUES ('ORD-001', 1, 100)");
            expectFailure(db, "非空约束（user_id 为 NULL）",
                    "INSERT INTO orders (order_no, user_id, amount_in_cents) VALUES ('ORD-003', NULL, 100)");
            expectFailure(db, "CHECK 约束（金额必须为正）",
                    "INSERT INTO orders (order_no, user_id, amount_in_cents) VALUES ('ORD-004', 1, 0)");

            System.out.println("=== 4. 外键：引用必须存在 ===");
            db.execute("""
                    CREATE TABLE order_item (
                        id           BIGINT AUTO_INCREMENT PRIMARY KEY,
                        order_id     BIGINT NOT NULL,
                        product_name VARCHAR(50) NOT NULL,
                        quantity     INT NOT NULL,
                        CONSTRAINT fk_order_item_order FOREIGN KEY (order_id) REFERENCES orders (id)
                    )
                    """);
            db.execute("INSERT INTO order_item (order_id, product_name, quantity) VALUES (1, '键盘', 2)");
            System.out.println("插入合法明细成功（order_id = 1 存在）");
            expectFailure(db, "外键约束（order_id = 999 不存在）",
                    "INSERT INTO order_item (order_id, product_name, quantity) VALUES (999, '鼠标', 1)");

            System.out.println("=== 5. 金额必须用 DECIMAL ===");
            System.out.println("先看 Java 里的浮点误差：0.1 + 0.2 = " + (0.1 + 0.2));
            // ... 建 price_demo 表并对比 DOUBLE 与 DECIMAL 的存储
        }
    }

    private static void expectFailure(SqlRunner db, String label, String sql) {
        try {
            db.execute(sql);
            System.out.println("  [意外成功] " + label);
        } catch (RuntimeException exception) {
            Throwable cause = exception.getCause() == null ? exception : exception.getCause();
            System.out.println("  [已拒绝] " + label + " → " + oneLine(cause.getMessage()));
        }
    }
}

运行命令：

powershell

cd labs
./mvnw -pl sql-basics compile exec:exec -Dexec.mainClass=cn.kb.learn.sql.ConstraintLesson

预期输出：

text

=== 1. 建表时声明约束 ===
表 orders 已创建（主键 / 非空 / 唯一 / 默认值 / CHECK）

=== 2. 正常插入：默认值自动生效 ===
order_no | status  | amount_in_cents | has_created_at
------------------------------------------------------
ORD-001  | CREATED | 19900           | true
（共 1 行）

=== 3. 违反约束会被数据库拒绝 ===
  [已拒绝] 主键重复 → Unique index or primary key violation: "PRIMARY KEY ON public.orders(id) ...
  [已拒绝] 唯一约束冲突（order_no 重复） → Unique index or primary key violation: "public.CONSTRAINT_INDEX_C ...
  [已拒绝] 非空约束（user_id 为 NULL） → NULL not allowed for column "user_id"; ...
  [已拒绝] CHECK 约束（金额必须为正） → Check constraint violation: "chk_amount: "; ...

=== 4. 外键：引用必须存在 ===
插入合法明细成功（order_id = 1 存在）
  [已拒绝] 外键约束（order_id = 999 不存在） → Referential integrity constraint violation: "fk_order_item_order: ...

=== 5. 金额必须用 DECIMAL ===
先看 Java 里的浮点误差：0.1 + 0.2 = 0.30000000000000004
浮点数用二进制近似小数，所以用来存金额会「算不准」。
as_double | as_decimal
-----------------------
0.1       | 0.10
0.2       | 0.20
（共 2 行）
decimal_sum | in_cents
-----------------------
0.30        | 30.00
（共 1 行）
DECIMAL(12, 2) = 最多 12 位数字、其中 2 位小数：精确存储，不做二进制近似。

四处值得对照：

输出	含义
`status = CREATED`	默认值生效——插入时没传 `status`
`has_created_at = true`	`DEFAULT CURRENT_TIMESTAMP` 自动填了时间
五条 `[已拒绝]`	每一类约束都在工作，且给出了可读的拒绝原因
`0.1 + 0.2 = 0.30000000000000004`	浮点的精度问题，这就是金额不能用 `DOUBLE` 的原因

注意报错信息的措辞差异（真实项目里要靠它定位问题）：

约束	报错关键词
主键/唯一	`Unique index or primary key violation`
非空	`NULL not allowed for column`
CHECK	`Check constraint violation`
外键	`Referential integrity constraint violation`

8. Debug 观察点

在 expectFailure 的 catch 处断点，展开 cause 与它的 message——数据库的报错里有约束名（如 chk_amount、fk_order_item_order），这正是给约束起名的作用；
在 db.execute("INSERT INTO order_item ...") 处观察 getUpdateCount() 返回 1——插入成功与违反约束的返回值差异；
观察 created_at IS NOT NULL AS has_created_at 这个写法：在 SQL 里把"是否为空"变成一列，比读到 null 再判断更方便；
把 CHECK (amount_in_cents > 0) 删掉重建表，再插入 0——观察它这次成功了，说明约束确实是"可选的最后一道防线"（做完改回来）。

9. 常见错误

错误 1：用 `DOUBLE` 存金额

症状：对账时出现 0.01 的差额，累计久了变成几块钱。

修复：用 BIGINT 存分，或 DECIMAL + Java 侧 BigDecimal。

错误 2：主键用 `INT` 而不是 `BIGINT`

症状：业务增长后 INT 溢出（上限约 21 亿）。

修复：主键一律 BIGINT——现在省 4 字节，将来改类型的成本远高于它。

错误 3：唯一约束与 NULL 的误解

sql

CREATE TABLE t (phone VARCHAR(20) UNIQUE);
INSERT INTO t VALUES (NULL), (NULL);        -- 两行都能插入成功

原因：SQL 标准里 NULL 不等于 NULL，所以唯一约束不认为它们重复。

如果业务上要求"手机号不能为空"：必须加 NOT NULL ✓

错误 4：外键用 `CASCADE` 不加思考

症状：删一个用户，发现他的订单、明细、日志全没了。

修复：默认用 RESTRICT；业务上优先用软删除（status / deleted_at），避免物理删除。

错误 5：约束名不写，靠系统生成

sql

CONSTRAINT chk_amount CHECK (...)        -- 写了名字，报错时一眼能认出来
CHECK (...)                              -- 不写名字，报错信息里是一串随机标识

修复：给约束起有意义的名字（chk_ / uk_ / fk_ / idx_ 前缀）——报错信息可读性会完全不同。

错误 6：以为有了约束就不需要应用层校验

两者的分工不同：数据库约束保证"数据不会错"，应用层校验保证"用户得到友好提示"。数据库抛出的约束错误不该直接返回给用户（那是 500 错误）。

正确做法：应用层先校验（返回友好提示）→ 数据库约束兜底（防止绕过）。

10. 动手实验

目标：亲手验证每一类约束，并制造一次"浮点误差"。

步骤：

运行第 7 节的命令，核对五条拒绝信息；
加一条约束：给 orders 加 CHECK (status IN ('CREATED', 'PAID', 'CANCELLED'))，然后试着插入 status = 'UNKNOWN'，观察被拒绝；
验证唯一约束与 NULL：建一张 t(phone VARCHAR(20) UNIQUE)，插入两行 NULL，观察是否成功；再加上 NOT NULL 重试；
验证外键的级联：给 order_item 的外键加上 ON DELETE CASCADE，删除 orders 里那条记录，观察明细是否一起消失；
制造浮点误差：写一个循环，累加 100 次 0.01（用 double），观察结果是不是 1.0；再改用 long 累加"分"，对比结果；
用 DECIMAL 重做第 5 步：在 SQL 里 SUM 一个 DECIMAL(12,2) 列，确认结果是精确的。

验收方式：能说出第 3 步两次的结果差异、第 5 步 double 累加的实际值。

扩展任务：为一张"商品表"设计完整的约束（价格、库存、状态、上架时间），并写出"每一条约束在挡什么坏数据"。

11. 练习题

基础题

判断：一张表可以有多个主键。（对 / 错）
填空：金额推荐用 ______ 或 ______ 存储；不要用 ______。
简答：为什么"应用层校验"不能替代"数据库约束"？

代码阅读题

sql

CREATE TABLE article (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    title      VARCHAR(100) NOT NULL,
    status     VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
    author_id  BIGINT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_article_author FOREIGN KEY (author_id)
        REFERENCES app_user (id) ON DELETE SET NULL,
    CONSTRAINT chk_status CHECK (status IN ('DRAFT', 'PUBLISHED', 'ARCHIVED'))
);

author_id 为什么可以是 NULL？这与外键的 ON DELETE SET NULL 是什么关系？
如果要"删除作者时连带删除他的文章"，应该改成什么？这样做有什么风险？

编程题

设计一张"支付流水表"并写出完整 DDL：
- 字段：id、流水号（唯一）、订单号、用户 id、金额、支付方式、状态、创建时间、完成时间；
- 约束要求：
  - 金额必须为正；
  - 支付方式只允许 ALIPAY / WECHAT / CARD；
  - 状态只允许 PENDING / SUCCESS / FAILED / REFUNDED；
  - 流水号唯一且非空；
- 写出每一条约束在挡什么坏数据；
- 额外：设计一个"同一个订单不允许出现两条成功流水"的约束（提示：可以用唯一索引 + 条件，或应用层保证）。

综合任务

给一个"电商订单系统"做表设计评审：
- 假设有 5 张表：用户、商品、订单、订单明细、支付流水；
- 逐张检查：有没有主键？主键类型对不对？金额字段用的什么类型？有没有该加没加的约束？
- 找出至少 3 处设计问题并给出修改方案；
- 说明：约束加得越多越好吗？ 举一个"加约束反而带来麻烦"的例子；
- 额外要求：给出一份"建表检查清单"（新建一张表时必须确认的 8 个点）。

分级提示

提示 1（方向）：第 2 题的关键是"精确"——整数与定点数都精确，浮点不精确。

提示 2（关键点）：第 4 题的关键是"外键列可以为 NULL"是 SET NULL 生效的前提；如果列是 NOT NULL，SET NULL 会直接报错。

提示 3（接近答案）：第 7 题的"约束带来麻烦"的例子：唯一约束会阻止"合法的重复"（如同一用户对同一商品下两单是允许的，但如果误加了唯一约束就会被拦），或者外键约束会让数据导入/迁移变复杂（导入顺序有要求）。

12. 参考答案

基础题

错。一张表只能有一个主键。但主键可以是复合主键（多列组合），如 PRIMARY KEY (order_id, product_id)——那仍然是"一个主键"，只是由多列组成。
BIGINT（存"分"） 或 DECIMAL(12,2)（存"元"）；FLOAT / DOUBLE。
因为应用层的校验一定会被绕过：

绕过方式	说明
多入口	接口做了校验，导入功能没做
直接改库	运维脚本、数据修复
并发	两个请求同时通过"查重"
历史数据	老版本代码没有这条校验
其他系统	另一个服务也写这张表

约束在数据层，应用层管不到的地方它都管得到。两者的分工：应用层给用户友好提示，数据库约束保证数据永远正确。

代码阅读题（第 4、5 题）

第 4 题：

author_id 可以为 NULL 是刻意的设计——它表示"这篇文章没有作者"（如系统生成的公告）。

ON DELETE SET NULL 与"列可为 NULL"是配套的：

作者被删除时，文章的 author_id 被置为 NULL（而不是删文章、也不是拒绝删作者）；
前提是 author_id 允许 NULL——如果它是 NOT NULL，这个外键行为会直接报错（删作者时会失败）。

这体现了一条设计原则：外键的删除行为要与字段的可空性一致。

第 5 题：改成 ON DELETE CASCADE。

sql

CONSTRAINT fk_article_author FOREIGN KEY (author_id)
    REFERENCES app_user (id) ON DELETE CASCADE

风险：

风险	说明
误删连带	删一个用户，他的所有文章永久消失——而且往往没有二次确认
难以撤销	级联删除不能部分回滚（除非在事务里整体回滚）
性能	删一个用户要删除他所有的文章，大账号可能锁表很久
业务语义	"用户注销"通常是保留内容、隐藏作者，而不是删内容

更常见的做法：不物理删除用户，用 status = 'DELETED' 标记；文章照常保留 ✓

编程题（第 6 题）

sql

CREATE TABLE payment_flow (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    flow_no       VARCHAR(32)  NOT NULL UNIQUE,
    order_no      VARCHAR(32)  NOT NULL,
    user_id       BIGINT       NOT NULL,
    amount_in_cents BIGINT     NOT NULL,
    pay_method    VARCHAR(16)  NOT NULL,
    status        VARCHAR(16)  NOT NULL DEFAULT 'PENDING',
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at   TIMESTAMP    NULL,
    CONSTRAINT chk_payment_amount CHECK (amount_in_cents > 0),
    CONSTRAINT chk_pay_method CHECK (pay_method IN ('ALIPAY', 'WECHAT', 'CARD')),
    CONSTRAINT chk_payment_status CHECK (status IN ('PENDING', 'SUCCESS', 'FAILED', 'REFUNDED')),
    CONSTRAINT uk_order_success UNIQUE (order_no, status)
);

每条约束在挡什么：

约束	挡住的坏数据
`PRIMARY KEY`	无法定位的流水
`flow_no UNIQUE`	重复的流水号（并发下重复提交）
`amount > 0`	负金额、零金额（如对手方接口传错）
`pay_method IN (...)`	未接入的支付方式（如前端传了 `'UNIONPAY'`）
`status IN (...)`	拼错的状态（`'SUCCESSED'`、`'success'`）
`NOT NULL`（各字段）	缺关键字段的流水
`DEFAULT 'PENDING'`	状态为空的记录

"同一订单不允许两条成功流水"：

上面的 UNIQUE (order_no, status) 能做到但不严谨——它同时限制了"同一订单不能有两条 PENDING"（这其实也合理），但不允许"一条失败流水 + 一条成功流水"（(order_no, 'FAILED') 与 (order_no, 'SUCCESS') 不冲突 ✓ 可以）。

但它拦不住：(order_no, 'FAILED') 插入两次 —— 而业务上"重试两次失败"是正常的 ✗

更合适的方案：

方案	做法	评价
部分唯一索引（PostgreSQL 支持）	`CREATE UNIQUE INDEX ... WHERE status = 'SUCCESS'`	✅ 精确表达"只对成功状态唯一"
MySQL 的变通	加一列 `success_flag`（成功时为 `order_no`，否则为 NULL），对它有唯一约束	✅ 利用"多 NULL 不冲突"的特性
应用层 + 分布式锁	下单时用订单号加锁	⚠️ 依赖代码正确性
状态机约束	在 `UPDATE` 时用 `WHERE status = 'PENDING'` 做乐观锁	✅ 简单有效，常与上面组合

MySQL 变通方案的示例：

sql

ALTER TABLE payment_flow ADD COLUMN success_order_no VARCHAR(32) NULL;
ALTER TABLE payment_flow ADD CONSTRAINT uk_success UNIQUE (success_order_no);
-- 支付成功时把 success_order_no 设为 order_no；失败/退款时保持 NULL
-- 这样"同一订单只能有一条成功流水"就被数据库强制保证了

这条设计的巧妙之处：它把"业务上的唯一"翻译成了"列上的唯一"——利用 NULL 不参与唯一约束的规则，实现了"条件唯一"。

综合任务（第 7 题）

建表检查清单（8 个点）：

markdown

## 新建一张表时必须确认的 8 件事

1. **有主键吗？** 类型是 `BIGINT` 吗？（不要用 `INT`，不要用业务字段）
2. **金额字段用的什么类型？** 必须是 `BIGINT`（分）或 `DECIMAL`，**不能是浮点**
3. **字符串长度合理吗？** 按业务上限来，不要一律 255
4. **必填字段都加了 `NOT NULL` 吗？** 能不加 NULL 就不加
5. **有默认值的字段都设了吗？** 状态、创建时间这类
6. **业务上的"唯一"加约束了吗？** 手机号、订单号这类要 `UNIQUE`
7. **枚举型字段加了 `CHECK` 吗？** 状态、类型这类
8. **外键关系建了吗？删除行为想清楚了吗？** 默认 `RESTRICT`，慎用 `CASCADE`

三处常见设计问题（示例）：

问题	现象	修改方案
金额用 `DOUBLE`	对账差几分钱	改 `BIGINT` 存分
主键用 `INT`	增长后溢出	改 `BIGINT`
订单表没有 `UNIQUE(order_no)`	并发下出现重复订单号	加唯一约束

"约束越多越好吗"——不是。

一个"加约束反而麻烦"的例子：

sql

-- 想保证"同一用户对同一商品只能有一条购物车记录"
ALTER TABLE cart ADD CONSTRAINT uk_cart UNIQUE (user_id, product_id);

看起来合理。但业务变化了：

现在要支持"同一商品的不同规格"（颜色/尺寸）→ 唯一约束挡不住了，要改成 (user_id, product_id, spec)；
改约束意味着要重建索引、要处理已有数据，成本不小。

更深的教训：约束是"把业务规则固化到数据库"——业务规则变得越快，约束越容易成为负担。

判断标准：

该加约束	谨慎加约束
不会变的硬规则（金额 > 0、状态白名单）	可能演变的业务规则（"只能有一个"）
防止数据损坏（非空、类型）	涉及多表组合的唯一性
关系完整性（外键）	与外部系统对接的字段

最后的建议：约束优先加在"数据正确性"上，而不是"业务策略"上。前者错了会损坏数据、很难修；后者错了改代码就行。

13. 掌握标准

[ ] 能说出六类约束及其作用；
[ ] 知道主键与唯一约束的三点区别，并能给出外键的三种删除行为；
[ ] 能为常见字段选对数据类型；
[ ] 能说出金额不能用浮点数的原因，并写出两种正确做法；
[ ] 会为约束命名（chk_ / uk_ / fk_），并知道报错信息里会用到它；
[ ] 理解"应用层校验 + 数据库约束"的分工，不做二选一。

14. 扩展资源

15. 下一步学习

《数据库范式与表关联设计》：三种关系的表结构怎么设计。

前置知识

S3-001 SQL 基础

后续知识

进度只保存在本机浏览器，可随时清除

外部学习资源

面试题库索引

项目与练手索引

工具与软件索引

表设计与约束

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 六类约束

5.2 主键 vs 唯一约束

5.3 外键的三种删除行为

5.4 数据类型选择

5.5 金额不能用浮点数（本节重点）

5.6 自增 ID vs UUID

6. 递进示例

6.1 最小示例：一张带约束的表

6.2 业务示例：约束帮我们挡住什么

6.3 反例：只靠应用层校验

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：用 `DOUBLE` 存金额

错误 2：主键用 `INT` 而不是 `BIGINT`

错误 3：唯一约束与 NULL 的误解

错误 4：外键用 `CASCADE` 不加思考

错误 5：约束名不写，靠系统生成

错误 6：以为有了约束就不需要应用层校验

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习

前置知识

后续知识

表设计与约束 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

4. 为什么需要这个知识 ​

5. 核心概念 ​

5.1 六类约束 ​

5.2 主键 vs 唯一约束 ​

5.3 外键的三种删除行为 ​

5.4 数据类型选择 ​

5.5 金额不能用浮点数（本节重点） ​

5.6 自增 ID vs UUID ​

6. 递进示例 ​

6.1 最小示例：一张带约束的表 ​

6.2 业务示例：约束帮我们挡住什么 ​

6.3 反例：只靠应用层校验 ​

7. 完整可运行代码 ​

8. Debug 观察点 ​

9. 常见错误 ​

错误 1：用 DOUBLE 存金额 ​

错误 2：主键用 INT 而不是 BIGINT ​

错误 3：唯一约束与 NULL 的误解 ​

错误 4：外键用 CASCADE 不加思考 ​

错误 5：约束名不写，靠系统生成 ​

错误 6：以为有了约束就不需要应用层校验 ​

10. 动手实验 ​

11. 练习题 ​

12. 参考答案 ​

13. 掌握标准 ​

14. 扩展资源 ​

15. 下一步学习 ​

前置知识

后续知识

表设计与约束

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 六类约束

5.2 主键 vs 唯一约束

5.3 外键的三种删除行为

5.4 数据类型选择

5.5 金额不能用浮点数（本节重点）

5.6 自增 ID vs UUID

6. 递进示例

6.1 最小示例：一张带约束的表

6.2 业务示例：约束帮我们挡住什么

6.3 反例：只靠应用层校验

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：用 `DOUBLE` 存金额

错误 2：主键用 `INT` 而不是 `BIGINT`

错误 3：唯一约束与 NULL 的误解

错误 4：外键用 `CASCADE` 不加思考

错误 5：约束名不写，靠系统生成

错误 6：以为有了约束就不需要应用层校验

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习