MySQL主键与外键设计实战：约束、索引与生产取舍

1. 先说清楚：主键和外键到底在数据库里扮演什么角色

干了这些年数据库相关的工作，被问得最多的问题里，“主键和外键到底怎么用”绝对排得上前三。很多初学者能把SELECT、INSERT写得飞起，一到设计表结构就手忙脚乱：主键随便给个id，外键干脆不管，等到数据开始互相打架、删除一条记录报出一长串错误码，才发现自己对主键和外键的理解还停留在“老师说过要加”的层面。

主键和外键是关系型数据库的两块基石。主键解决“这一行数据凭什么唯一”的问题，是每一条记录的身份标识；外键解决“两张表之间的数据凭什么能对得上”的问题，是表与表之间的一条绳索。前者保证单表的完整性，后者保证跨表的引用完整性，两个加起来，才让 MySQL 从“能存数据的文件”变成“能保证数据不出错的关系型数据库”。

这篇内容适合三类人：刚学 MySQL 不久、正在被课程设计或练习项目折磨的新手；工作了一两年、写 SQL 没问题但表结构总被前辈吐槽的开发者；以及需要给团队新人做基础培训、想要一份可以直接拿去讲的实操素材的同学。我会从设计思路讲到建表语句，从约束行为讲到线上排查，把主键和外键那些书本上一笔带过、实际工作中却天天踩的细节全部摊开。

注意：文中所有示例库表、业务场景均为虚构的练习场景，用于演示语法和设计思路，不涉及任何真实业务系统。

2. 整体设计思路：为什么主键和外键值得花时间搞懂

2.1 从一张“什么都往里塞”的表说起

我见过太多新手的第一张表长这个样子：订单号、客户姓名、客户电话、客户地址、商品名称、商品单价、购买数量，全部塞在一张orders表里。刚写完的时候觉得挺爽，一条 SQL 就能查出所有信息，不用JOIN，多省事。

问题在第三天开始暴露。同一个客户下了三单，姓名电话地址被重复存了三遍，客户改了手机号，得UPDATE三行，漏了一行就出现“同一个客户两个号码”的脏数据。再过一周，商品涨价了，历史订单的价格要不要跟着变？如果直接改商品单价，历史订单金额全乱；如果不动，那这张表里同一件商品就有好几种价格，谁也说不清哪个是对的。

这就是典型的没有做表拆分、没有主外键约束导致的后果。拆表的核心逻辑是把“描述一个实体的属性”归到一张表里：客户的信息归客户表，商品的信息归商品表，订单本身只记录“谁在什么时候买了什么”。拆完之后，每张表需要一个字段来唯一标识自己的一行，这就是主键；订单表要记录“这单是谁下的”，就得引用客户表的主键，这就是外键。

一句话概括这套设计思路：主键负责“我是谁”，外键负责“我和谁有关系”。前者是内政，后者是外交，缺一个，数据的世界就会乱套。

2.2 主键的本质：不是“id 字段”，而是“行的唯一身份”

很多人对主键的理解停留在“建表时写个id INT PRIMARY KEY AUTO_INCREMENT”，这是一种知其然不知其所以然的状态。主键真正的定义是：在整张表中能够唯一确定一行的字段或字段组合，且不允许为 NULL。

这个定义里有两个关键词值得展开。第一个是“唯一”，意味着任意两行在主键上的值不能相同，数据库会为此自动建立唯一索引来强制约束；第二个是“不允许为 NULL”，这是主键和唯一索引最容易混淆的地方——唯一索引允许 NULL 值，而且在 MySQL 里多个 NULL 是被允许的（因为 NULL 不等于 NULL），但主键一个 NULL 都不许有。所以如果你需要一个“可以为空的唯一标识”，那它只能是唯一索引，不能是主键。

还有一个更深层的点，是很多人学 MySQL 时完全没被提醒过的：在 InnoDB 存储引擎里，主键决定了数据在磁盘上的物理组织方式。InnoDB 用的是聚簇索引结构，整张表的数据就挂在那棵以主键为键的 B+ 树上，叶子节点直接存着完整行数据。这意味着两件事：一是按主键查询是最快的路径，因为一次索引查找就拿到整行；二是所有二级索引的叶子节点里存的都是主键值，主键越长，每个二级索引就越大。这也是为什么后面我会反复强调主键要“短”要“有序”。

2.3 外键的本质：一条被数据库强制执行的引用规则

外键的定义是：一张表（子表）中的某个字段，其取值必须来源于另一张表（父表）的主键或唯一键。注意这里说的是“必须”，因为外键约束是由数据库引擎在写入时实时检查的，不是靠应用代码自觉。

举个生活化的类比。公司的工牌编号是员工表的主键，每个员工有唯一工牌号。会议室预约表里记录“这个时段被哪个工牌号占用”，这个工牌号字段就是外键。如果有人拿一个根本不存在的工牌号去预约会议室，前台（数据库）会直接拒绝，因为它查不到这个工牌号对应的员工。这就是外键的价值：把“数据之间的引用关系”从口头约定变成硬性规则。

外键还带来了另一个能力，就是级联行为。父表的记录被删除或更新时，子表该怎么办？是禁止删除（RESTRICT）、跟着一起删（CASCADE）、把外键字段置空（SET NULL），还是保持原样（NO ACTION）？这几个选项后面会详细讲，它们直接决定了你的数据在被误操作时是“安然无恙”还是“全军覆没”。

2.4 为什么很多团队在生产环境禁用外键

说到这里必须泼一盆冷水：在实际的互联网业务开发中，物理外键（数据库层面的FOREIGN KEY约束）的使用率并不高，很多团队的开发规范里明确写着“禁止使用外键”。我早年第一次看到这条规范时也很困惑，既然外键这么好，为什么不用？

原因主要有这么几条，理解了它们，你才能做出适合自己的取舍，而不是盲目跟风。

第一是分库分表的天然冲突。外键约束只能在同一台 MySQL 实例、同一个库里的两张 InnoDB 表之间生效。一旦业务量上来需要水平拆库，订单表和客户表可能落到不同实例上，物理外键根本无法建立，最后还是得靠应用层保证一致性。与其将来拆的时候痛苦，不如一开始就不依赖它。

第二是性能与锁的代价。每次往子表插入数据，数据库都要去父表做一次检查，这个检查需要在父表对应记录上加共享锁。高并发场景下，这种跨表的锁竞争会显著放大死锁概率，尤其是父表记录被频繁更新的业务。一个典型的现象是：更新子表时莫名其妙地卡住，SHOW ENGINE INNODB STATUS一看，等待的锁在父表上。

第三是数据迁移和批量操作的麻烦。做数据归档、批量导入、表结构变更时，外键会带来严格的顺序要求——必须先插父表、再插子表，删除反过来；TRUNCATE父表会被直接拒绝。很多运维同学在导数据时被外键折腾到崩溃，最后干脆一刀切禁用。

第四是级联删除的风险。ON DELETE CASCADE写起来很爽，但它是把双刃剑。一次误删父表记录，可能连锁删除成千上万条子表数据，并且这种删除是静默的，事后想恢复只能靠备份。我在实际项目中见过因为一条DELETE触发了三层级联，直接把某个核心表清空的事故。

所以我的建议是：学习和中小型项目，大胆用外键，它能帮你把数据质量兜住；高并发、要分库分表、需要频繁批量迁移的大型业务，用“逻辑外键”（只建索引不建约束，靠应用层保证）加上定期的一致性校验脚本。这不是谁对谁错的问题，是场景匹配的问题。后面第 6 节我会给出逻辑外键的具体做法。

3. 主键的核心细节与实操要点：选错了会一直难受

3.1 主键的四条铁律，一条都不能破

在动手建表之前，先把主键的四条规则刻进脑子里，这四条是 MySQL 强制的，违反任何一条都会直接报错。

第一条，一张表只能有一个主键。这个“一个”指的是主键约束只有一个，但主键可以由多个字段联合组成，这就是联合主键。别把它理解成“只能有一个字段是主键”。

第二条，主键的值必须唯一。数据库会自动在主键上建一个唯一索引，插入重复值会直接报Duplicate entry错误。这个约束是硬性的，绕不过去。

第三条，主键不能为 NULL。哪怕你显式写INSERT INTO t (id) VALUES (NULL)，也会报错。这一点和唯一索引不同，务必记牢。

第四条，主键的值原则上不应被修改。虽然 SQL 语法允许UPDATE主键字段（在没有外键引用的情况下），但主键一旦被其他表引用，修改就会引发连锁反应。更关键的是，InnoDB 的数据是按主键物理排序的，改主键等于把这一行从树的这个位置搬到另一个位置，开销远大于改普通字段。所以主键的设计原则是：一次定终身，永不变更。

3.2 自增 ID、UUID、业务主键，到底选哪个

这是新手最纠结的问题，我直接把三种方案的特性列成表，先看结论再看分析。

方案	存储占用	写入性能	分库分表友好度	安全性	典型适用场景
BIGINT AUTO_INCREMENT	8 字节	高，顺序追加	需额外方案（号段/雪花）	低，ID 可被枚举	绝大多数单库业务表
CHAR(36)UUID	36 字节	低，随机写入	天然全局唯一	高	少量需要预生成的场景
BINARY(16)UUID	16 字节	中低，随机写入	天然全局唯一	高	同上，但更省空间
业务字段（如身份证、订单号）	视字段而定	中	需保证全局唯一	视业务	有天然唯一业务键的表

自增整型主键是绝大多数场景的最优解，原因有三。第一，它短，BIGINT只占 8 字节，二级索引里存的主键值也短，索引整体更小，缓存命中率更高。第二，它有序，新插入的数据总是追加到 B+ 树的最右侧，页分裂极少，写入性能稳定。第三，它简单，AUTO_INCREMENT由数据库维护，不用应用层操心。

UUID 的坑主要在随机性上。UUID v4 是完全随机的，这意味着每次插入的主键可能落在 B+ 树的任意位置，导致频繁的页分裂和随机磁盘 IO。一张几千万行的表用 UUID 做主键，写入性能可能只有自增 ID 的三分之一甚至更低。如果业务真的需要 UUID（比如前端要提前生成 ID、不想暴露数据量），那就用BINARY(16)存，通过UUID_TO_BIN(uuid, 1)把时间低位调到前面，让生成的二进制值近似有序，能缓解不少问题。

业务字段做主键，我一般只在两种情况下考虑：一是这张表本身就很小、几乎不增长，比如配置表、字典表；二是这个业务字段天然唯一且永不变更。但凡有一点可能变更，就别拿它当主键——改主键的代价，比你多建一个唯一索引大得多。

提示：AUTO_INCREMENT生成的值不保证连续。事务回滚、唯一键冲突、批量插入预分配，都会造成 ID 跳号。这是正常现象，业务上不要依赖“ID 连续”这个假设，更不要拿它当序号用。

3.3 联合主键：什么时候该用，什么时候是自找麻烦

联合主键就是由两个或多个字段共同组成主键。最常见的使用场景是多对多的中间表，比如订单商品表order_item，用(order_id, product_id)作为联合主键，天然保证了“同一订单内同一商品只出现一次”，省掉了一个额外的唯一索引。

联合主键的好处是语义清晰、节省一个索引，但它有两个必须注意的细节。

第一是字段顺序极其重要。PRIMARY KEY (a, b)和PRIMARY KEY (b, a)在约束效果上等价，但在索引能力上完全不同。前者能支持按a查询、按(a, b)查询，单独按b查询用不上这个索引（这是最左前缀原则）。所以联合主键的字段顺序要按照“查询频率高的、选择性高的字段放前面”来排。

第二是所有字段都不能为 NULL。联合主键里的每一个字段都受“主键非空”的约束，这一点经常被忽略，导致设计完之后发现某个字段本来需要允许空值，只能推倒重来。

我个人的习惯是：除非是纯粹的多对多关联表，其他场景一律用单列自增主键 + 业务唯一索引。因为联合主键在主键变更、外键引用、ORM 映射上都会带来额外的复杂度，省下的那点空间和索引，往往抵不过后期的维护成本。

3.4 主键设计的四个实操心得

讲完理论，说点实际踩出来的经验。

心得一：主键类型要和关联字段严格一致。父表主键是BIGINT UNSIGNED，子表外键字段也必须是BIGINT UNSIGNED，连UNSIGNED都不能差。类型不一致时，MySQL 建外键会直接报错，或者更糟——在某些版本里能建上，但JOIN时发生隐式类型转换，索引失效，查询慢到怀疑人生。

心得二：主键别用INT，直接上BIGINT。INT UNSIGNED的上限是 42 亿左右，看着很大，但订单、日志、消息这类表很容易撞上去。而且从INT改到BIGINT是一次全表重建 + 所有关联表跟着改的浩大工程。一开始就用BIGINT UNSIGNED，多花 4 字节，省掉未来一个大坑。

心得三：自增起始值和步长要提前规划。如果未来可能做分库，可以给不同库设置不同的auto_increment_offset和auto_increment_increment，比如双库场景下一个从 1 开始步长 2，另一个从 2 开始步长 2，避免 ID 冲突。这个方案在库数量少的时候很好用，库多了还是得上号段服务或者雪花算法。

心得四：不要用NULL值语义去表达业务状态。有些设计喜欢把“未分配”的关联字段留 NULL，这在主键和外键上都是行不通的。状态就是状态，用单独的状态字段表达，别和主键外键混在一起。

4. 外键的核心细节与实操要点：约束行为决定了你的数据安全边界

4.1 外键的完整语法与四种约束行为

外键的定义写在建表语句里，或者通过ALTER TABLE追加，标准语法是这样的：

CONSTRAINT 约束名 FOREIGN KEY (子表字段) REFERENCES 父表 (父表字段) ON DELETE 删除时的行为 ON UPDATE 更新时的行为

ON DELETE和ON UPDATE各有四种可选行为，它们决定了父表记录发生变化时子表怎么响应：

行为	含义	使用建议
RESTRICT	拒绝操作，报错。与NO ACTION在 MySQL 中等价	默认值，最安全，推荐大多数场景
NO ACTION	同上，标准 SQL 的写法	与RESTRICT等价，二选一即可
CASCADE	父表删除/更新，子表跟着删除/更新	慎用，尤其是DELETE CASCADE
SET NULL	父表删除/更新时，子表外键字段置为 NULL	要求外键字段允许 NULL

这里有个容易踩的坑：SET NULL要求子表的外键字段本身允许 NULL，如果建表时写了NOT NULL，创建外键时会直接报错。

关于CASCADE，我的态度是能用RESTRICT就别用CASCADE。原因很简单：RESTRICT报错时你还有机会反思一下是不是删错了；CASCADE一旦执行，数据就没了，而且可能沿着多层外键一路删下去。如果业务上确实需要“删订单时同时删订单明细”，我更倾向于在应用层用事务显式删除，一来逻辑可见，二来可以加日志、加审批、加软删除。

4.2 三种表关系的建表实操：一对一、一对多、多对多

外键的使用场景可以归结为三种关系，我直接用虚构的电商练习场景把建表语句写全，你可以直接拿去跑。

一对多是最常见的，客户和订单就是典型。一个客户可以有多个订单，一个订单只属于一个客户。外键放在“多”的那一侧，也就是订单表里。

CREATE DATABASE IF NOT EXISTS shop_demo DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE shop_demo; CREATE TABLE customer ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '客户主键', name VARCHAR(50) NOT NULL COMMENT '客户姓名', phone VARCHAR(20) NOT NULL COMMENT '手机号', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表'; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单主键', order_no CHAR(20) NOT NULL COMMENT '业务订单号', customer_id BIGINT UNSIGNED NOT NULL COMMENT '所属客户', amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额', status TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已取消', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_customer_id (customer_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

注意这里我做了两件容易被忽略的事：一是给customer_id单独建了索引idx_customer_id，二是外键约束命名为fk_orders_customer。前者是因为 MySQL 虽然会自动为外键字段创建索引（如果不存在的话），但显式命名能让你清楚地知道这个索引是干什么用的，也方便后续优化；后者是命名规范，“fk_子表_父表”这种命名方式在排查问题时非常好用，一眼就知道是哪个约束报的错。

一对一关系用得相对少，典型场景是用户和用户扩展信息（比如实名认证信息、个人简介），主表存核心字段，扩展表存不常用的字段，通过外键 + 唯一索引实现一对一。

CREATE TABLE customer_profile ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id BIGINT UNSIGNED NOT NULL COMMENT '对应客户，唯一', id_card_no VARCHAR(32) DEFAULT NULL COMMENT '证件号', address VARCHAR(200) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_customer_id (customer_id), CONSTRAINT fk_profile_customer FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户扩展信息表';

这里的关键是UNIQUE KEY uk_customer_id，它保证了“一个客户只能有一条扩展记录”。如果没有这个唯一索引，一对一就退化成了多对一。这里我用ON DELETE CASCADE是因为扩展信息依附于主记录存在，主记录没了它也没有意义，属于可以安全级联的场景。

多对多必须借助中间表，订单和商品就是典型。一个订单包含多个商品，一个商品可以出现在多个订单里。

CREATE TABLE product ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL COMMENT '商品名', price DECIMAL(10,2) NOT NULL COMMENT '当前售价', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表'; CREATE TABLE order_item ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, qty INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量', unit_price DECIMAL(10,2) NOT NULL COMMENT '成交单价，下单时快照', PRIMARY KEY (id), UNIQUE KEY uk_order_product (order_id, product_id), KEY idx_product_id (product_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

这张表里有几个设计取舍值得说。第一，中间表用了独立自增主键，而不是把(order_id, product_id)做成联合主键。原因是这张表未来可能需要被其他表引用（比如退货明细要指向某条订单明细），有个单列主键会方便很多。第二，unit_price是下单时的价格快照，不能靠JOIN商品表去取当前价格，否则商品调价后历史订单金额就变了。第三，两条外键的行为不一样：订单被删时明细跟着删（CASCADE），商品被删时禁止（RESTRICT），因为商品一旦被卖过就不该物理删除，应该下架处理。

4.3 外键对性能的真实影响，以及索引为什么必须有

很多人说“外键影响性能”，这话对，但往往说不清影响在哪。我拆开讲。

写入时的检查开销：每次向子表插入或更新外键字段，InnoDB 都要去父表确认这个值存在。这个检查走的是父表的主键索引，本身很快，但需要在父表对应记录上加共享锁。如果同一个父记录被大量子记录同时引用并写入，锁竞争就会显现。

删除和更新父表时的检查开销：删除父表记录前，MySQL 要确认没有子表记录引用它（RESTRICT模式），这个检查需要对子表的外键索引做范围扫描。如果外键字段没有索引，这个扫描就是全表扫描，一张千万行的子表能把你锁到超时。所以 MySQL 在创建外键时会自动尝试建索引——但仅限于“没有可用索引”的情况，如果字段上已经有索引，它就复用。这也是为什么我在建表语句里显式加了KEY idx_customer_id，把控制权握在自己手里。

死锁概率上升：跨表的锁顺序问题会显著增加死锁概率。典型场景是两个事务分别以不同的顺序更新父表和子表，互相等待对方持有的锁。这类死锁靠重试能解决，但会带来业务抖动，所以高并发写场景要格外注意统一加锁顺序。

提示：如果你的表数据量已经很大，事后想加外键，一定要先在低峰期操作。ALTER TABLE ... ADD CONSTRAINT会对现有数据做全量校验，期间表可能被锁住。更稳妥的做法是先用SELECT找出所有不符合约束的脏数据清理掉，再添加约束。

4.4 外键使用的六条硬性注意事项

第一，存储引擎必须是 InnoDB。MyISAM 引擎会静默忽略外键定义——不报错，但也不生效，这是最阴险的坑。建表时一定要显式写ENGINE=InnoDB。

第二，字段类型和字符集必须完全一致。父表主键是BIGINT UNSIGNED，子表字段就不能是BIGINT（有无符号不一致）。如果是字符类型的外键，字符集和排序规则也必须相同，否则会报errno 150。

第三，外键字段不能是TEXT、BLOB这类大字段。只能用在有索引能力的字段上。

第四，父子表不能是临时表。MySQL 的临时表不支持外键约束。

第五，跨库外键不支持。外键只能引用同一数据库中的表，分库场景下无解。

第六，删除外键要先知道约束名。ALTER TABLE 子表 DROP FOREIGN KEY 约束名，这里的约束名是建表时CONSTRAINT后面那个名字，不是字段名也不是索引名。忘了名字可以用SHOW CREATE TABLE 子表查，或者查information_schema.TABLE_CONSTRAINTS。

5. 完整实操：从零跑通一套带主外键的订单场景

5.1 建库建表并验证约束是否生效

把上面的建表语句按顺序执行：先customer，再orders，再product，最后order_item。顺序不能乱，因为子表建表时要引用父表，父表不存在会报错。

建完之后，执行SHOW CREATE TABLE orders\G，你会看到类似这样的输出（节选）：

CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE

看到这一行，说明外键建成功了。如果只看到KEY idx_customer_id而没有CONSTRAINT，那说明外键没建上，可能是引擎不对，或者建表语句被某个工具改写过了。

接下来验证约束。先插一条客户数据：

INSERT INTO customer (name, phone) VALUES ('张三', '13800000001'); -- 假设返回 id = 1

然后试试插入一个引用不存在客户（customer_id = 999）的订单：

INSERT INTO orders (order_no, customer_id, amount) VALUES ('SO20240101000001', 999, 99.00);

会得到这样的错误：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop_demo`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

这条报错信息非常完整，它告诉你了：哪张表、哪个约束名、引用的是哪张表的哪个字段。这就是给约束起规范名字的价值——如果不命名，MySQL 会自动生成一个orders_ibfk_1这样的名字，多张表混在一起排查时容易对不上号。

再试试删除一个有订单的客户：

DELETE FROM customer WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop_demo`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

RESTRICT生效了，父记录删不掉。这正是我们想要的行为——数据被人引用着的时候，不允许无声无息地消失。

5.2 级联行为实测：CASCADE 到底删掉了什么

把订单表的外键行为改成CASCADE再测一次，你会直观感受到它的威力。为了不破坏前面的表，我另外建一组练习表：

CREATE TABLE demo_parent ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE demo_child ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id BIGINT UNSIGNED NOT NULL, memo VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), KEY idx_parent_id (parent_id), CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES demo_parent (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入一组数据：

INSERT INTO demo_parent (name) VALUES ('父A'); INSERT INTO demo_child (parent_id, memo) VALUES (1, '子1'), (1, '子2'), (1, '子3');

此时demo_child有三条记录。执行DELETE FROM demo_parent WHERE id = 1;，然后查子表：

SELECT COUNT(*) FROM demo_child; -- 结果：0

三条子记录全部被删掉了，一条DELETE清空一张子表——这就是我说CASCADE要慎用的原因。如果这层关系还套着下一级，删除还会继续往下传播。所以我给团队定规矩：CASCADE只允许用在“子表数据完全依附于父表、且子表没有被其他表引用”的场景，比如上面的一对一扩展表、订单明细表；任何可能被第三方引用的表，一律RESTRICT。

顺便测一下ON UPDATE CASCADE。把父表主键从 1 改成 100：

UPDATE demo_parent SET id = 100 WHERE id = 1;

子表的parent_id会自动跟着变成 100。这个行为在某些“主键需要变更”的场景下有用，但正如前面说的，主键本就不该变，所以这条更多是兜底。

5.3 修改和删除外键的完整操作路径

实际工作中经常需要调整外键，把操作路径记熟能省不少时间。

查询现有外键，最直接的是看建表语句：

SHOW CREATE TABLE orders\G

想批量查某个库下所有外键，可以查系统表：

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'shop_demo' AND REFERENCED_TABLE_NAME IS NOT NULL;

删除外键分两步，先删约束再删索引（如果这个索引是外键专用的）：

ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer; ALTER TABLE orders DROP INDEX idx_customer_id;

第二步不是必须的，但从节省空间的角度，如果这个索引没有其他查询在用，删掉更干净。

新增外键：

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (id) ON DELETE RESTRICT ON UPDATE CASCADE;

执行前建议先跑一次脏数据检查，免得加约束时直接失败：

SELECT o.id, o.customer_id FROM orders o LEFT JOIN customer c ON o.customer_id = c.id WHERE c.id IS NULL LIMIT 100;

有结果就说明存在孤儿数据，得先修掉再建约束。这种检查在生产环境非常必要，我见过一次加外键失败导致变更窗口延后的情况，就是因为历史数据里有几千条脏记录没人管。

5.4 一个容易被忽略的点：外键约束的检查时机

MySQL 的外键检查是即时生效的，不是事务提交时才检查。这意味着在一个事务里，如果你先插子表、后插父表，即使两条语句都在同一个事务里，第一条就会报错。

START TRANSACTION; INSERT INTO orders (order_no, customer_id, amount) VALUES ('SO20240101000002', 2, 50.00); -- 这一刻就会报 1452，因为 customer id=2 还不存在 INSERT INTO customer (name, phone) VALUES ('李四', '13800000002'); COMMIT;

所以批量初始化数据时，顺序必须是：父表 → 子表；删除时反过来：先删子表数据或先把子表外键置空，再删父表。很多 ORM 框架的批量操作会打乱顺序，遇到 1452 错误时第一时间检查语句执行顺序。

如果确实需要在同一个事务里临时跳过的场景（比如循环依赖的数据导入），MySQL 提供了一个会话级开关：

SET foreign_key_checks = 0; -- 做批量导入 SET foreign_key_checks = 1;

但这个开关有代价：它只是不检查，不会自动帮你修复数据，关掉期间插入的脏数据会永久留在表里。用它之前一定要确认数据来源可靠，用完立刻打开，并且不要在生产主库上随便用。我一般只在导测试数据、做数据修复脚本时用它，而且用完会立刻跑一遍一致性校验 SQL。

6. 常见问题与排查技巧实录：那些报错码背后的真实原因

6.1 错误码速查表：看到数字就知道往哪查

排查问题最怕的是看到报错不知道从哪下手。我把外键相关的常见错误码整理成表，配合原因和处置方式，遇到时可以直接对号入座。

错误码	报错文本关键词	常见原因	处置方式
1452	Cannot add or update a child row	子表引用的父记录不存在；插入顺序颠倒	检查父表是否有该值，调整插入顺序
1451	Cannot delete or update a parent row	父记录被RESTRICT约束拦住	先处理子表引用，或改约束为CASCADE
1215	Cannot add foreign key constraint	字段类型/字符集/引擎不一致最常见	逐项比对父子字段定义
1005	Can't create table (errno: 150)	建表失败，外键定义有问题	用SHOW ENGINE INNODB STATUS看详情
1062	Duplicate entry	主键或唯一索引冲突	检查插入值，考虑INSERT ... ON DUPLICATE KEY UPDATE
1175	You are using safe update mode	客户端开了安全模式，UPDATE/DELETE无WHERE主键条件	加WHERE或临时关闭安全模式

1215 和 1005 是新手最难缠的两个。它们只说“建不上”，不说为什么。这时候最快的定位手段是执行：

SHOW ENGINE INNODB STATUS\G

在LATEST FOREIGN KEY ERROR段落里，会明确指出是“字段类型不匹配”还是“引用表不存在”还是“索引缺失”。这个命令我几乎每次遇到 1215 都会跑，比猜快一百倍。

6.2 三个真实排查案例，都是坑

案例一：明明父表有这条数据，还是报 1452。

某同学反馈，customer表里确实有id = 5的记录，但往orders插customer_id = 5就是报 1452。查了半天发现，父表的id是BIGINT UNSIGNED，子表字段建表时写成了BIGINT（有符号）。MySQL 在建外键时没有严格拦住，但运行时的比较存在类型转换，导致匹配不上。

这类问题的排查方法很简单：SHOW CREATE TABLE把两张表的字段定义调出来，一个字符一个字符对。类型、长度、UNSIGNED、CHARSET、COLLATE，五项全对才行。

案例二：加外键时把表锁了半小时。

数据量 800 万的子表加外键，执行ALTER TABLE后业务直接报警，查询超时。原因是加外键需要全表扫描校验存量数据，这期间表上有元数据锁，写入全部阻塞。

正确的做法是：先用pt-online-schema-change或gh-ost这类在线变更工具，或者在业务低峰期操作；更重要的是提前跑一次脏数据检查，确认校验能快速通过。我现在的习惯是，任何对千万级表的ALTER，都先在从库上跑一遍，记录耗时，再决定变更窗口。

案例三：外键导致的高频死锁。

某业务日志里频繁出现Deadlock found when trying to get lock，SHOW ENGINE INNODB STATUS显示两个事务在orders和order_item之间交叉等待。根因是代码里有的地方先插订单再插明细，有的地方反过来，加锁顺序不一致。

解决办法是统一加锁顺序：永远先操作父表、再操作子表；批量操作时按主键排序后依次处理。这条规则写进开发规范后，死锁率下降了绝大部分。

6.3 几个能立刻用上的避坑技巧

技巧一：外键字段一定要显式建索引。虽然 MySQL 会自动建，但自己建能控制名字和后续的可维护性，也能避免“删外键时把有用的索引一起删了”的尴尬。

技巧二：外键约束名统一规范。我用的是fk_子表简称_父表简称，比如fk_orders_customer、fk_item_order。规范的名字在报错信息里直接可见，排查效率提升非常明显。

技巧三：批量导入前先关检查，导入后立刻校验。校验 SQL 就是个简单的LEFT JOIN ... WHERE 父表.id IS NULL，跑一遍比事后查数据快得多。

技巧四：删除父表数据前，先跑一次引用计数。比如要删客户，先SELECT COUNT(*) FROM orders WHERE customer_id = ?，确认没引用再删。这个习惯能避免 90% 的 1451 报错。

技巧五：别在业务高峰做结构变更。加外键、改字段类型、加索引，这三类操作都可能锁表。变更前用SHOW PROCESSLIST看看当前连接数，选个低峰期，比什么都强。

7. 生产环境的折中方案：逻辑外键怎么落地

7.1 逻辑外键的完整做法

前面说过，高并发或要分库分表的业务通常不用物理外键。那不用外键，怎么保证数据不出错？答案是逻辑外键 + 应用层保障 + 定期校验，三件套缺一不可。

第一步，字段照建，索引照加，只是不写CONSTRAINT：

CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id BIGINT UNSIGNED NOT NULL COMMENT '逻辑外键，指向 customer.id', -- 其他字段略 PRIMARY KEY (id), KEY idx_customer_id (customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第二步，在应用层保证一致性。具体做法是：写入订单前先校验客户存在（可以在同一个事务里SELECT ... FOR SHARE锁住客户行），删除客户前先检查是否有未完成订单。这些校验要封装在服务层，不能指望每个调用方自觉。

第三步，加定期校验任务。我一般会写一个每天凌晨跑的巡检脚本，把所有可能存在孤儿数据的关联扫一遍：

-- 找出没有对应客户的订单 SELECT o.id, o.customer_id FROM orders o LEFT JOIN customer c ON o.customer_id = c.id WHERE c.id IS NULL LIMIT 1000;

有结果就报警，人工确认后修复。这套机制跑顺了以后，数据质量其实不比物理外键差，而且换来了分库分表的自由和更低的锁开销。

7.2 什么规模该切换方案

给个粗略的判断标准，不是绝对，但可以作为参考。

场景特征	推荐方案
单库、日订单量万级以下、团队规模小	物理外键，省心
单库、日订单量十万级以上、写入并发高	逻辑外键 + 应用层校验
已分库分表或计划一年内分库	逻辑外键，别无选择
数据需要频繁批量迁移、归档	逻辑外键，避免约束顺序问题
强监管、数据一致性要求极高且量不大	物理外键，多一层保障

我自己的项目里，早期一律上物理外键，简单直接；等到单表过千万、写入 QPS 上来了，再逐步把核心链路改成逻辑外键，非核心表保留物理约束。这种渐进式的做法比一刀切稳妥得多。

最后分享一个我用了很多年的小习惯：每建一张带外键的表，我都会在旁边写一段注释，注明这个外键的删除策略和理由。比如“订单明细随订单级联删除，因为明细无独立生命周期；商品外键用 RESTRICT，因为商品不物理删除只下架”。半年后回头看，或者交接给别人的时候，这几行注释能省掉无数次“当初为什么这么设计”的追问。数据库设计这件事，写下来比记在脑子里靠谱得多。