MySQL建表约束实战：从主键外键到CHECK，避开脏数据陷阱

上周帮某项目的同事评审建库脚本，我翻到订单表那段时愣了几秒：一张十来列的表，一个约束都没写。没有主键，没有唯一约束，状态字段连默认值都没有。问了一句为什么，对方说“约束条件不是可选的么，先建表，后面有空再加”。这个“后面有空再加”，后来在测试环境里替他演示了一遍后果：一天之后，表里出现了两条一模一样的订单号，状态字段被写进去一个不存在的数字，删了一个客户之后，订单表里剩下的全是孤儿单。那个下午全在写数据修复脚本，比当初建表多花了几十倍时间。

今天想聊的就是MySQL创建数据表及其约束这件事。标题里的“可选”两个字，我特别想掰开揉碎讲清楚：约束规则在语法上确实可以省略，但省略只是你把业务规则从数据库的责任列表里划掉了，它不会凭空消失，只会变成跨过应用层合规检查的脏数据概率。这篇文章会从约束设计的思路、引擎与字段类型、主键唯一外键检查约束的细节，一直讲到实际报错的排查链路，内容偏实战，适合正在设计表结构、或者已经被线上脏数据教育过的开发者慢慢看。

1. 建表之前先回答三个问题，约束自然会浮出来

1.1 把约束当作业务规则的合同，而不是可有可无的备注

很多新手把约束理解成“建表语法的附加题”，能写就写，不能写就拉倒。但约束的本质是业务规则在数据库层的固化，它跟你写入接口里的校验参数是同一件事，只是落点不同。

我习惯把约束理解成数据库和业务规则签的合同。接口里的校验是口头约定，今天A同事记得，明天B同事离职了，后天新来的C同事可能就漏了；数据库表上的约束是白纸黑字，谁都改不掉。任何入口写入的数据，不管是正常业务接口、临时脚本、手工SQL，还是半夜跑的数据修复任务，都要过这一关。

所以建表之前，先问自己三个问题：第一，这张表里哪些数据不允许重复？第二，这张表和其他表是什么关系，孤立数据能不能存在？第三，哪些字段的值域是可枚举的，哪些字段不允许为空，哪些字段要有合理的默认值？这三个问题想清楚了，约束清单基本就成型了，根本不需要背语法。

1.2 从一个简化版的订单模块看约束清单

为了让后面讨论不至于悬空，我用一个简化版的订单模块贯穿全文，涉及三张表：客户表、订单表、订单明细表。这三张表的约束需求非常典型，几乎覆盖了MySQL里所有常用约束类型。

看到没有，约束不是从语法里反推出来的，是从业务规则里正推出来的。先有规则，后有约束。后面所有的章节，都是在讲这些约束落到CREATE TABLE语句时，有哪些细节和坑。

2. 引擎、字符集、字段类型：三个在CREATE TABLE里一错就埋雷的选型

约束写对之前，得先把表的基础配置写对。引擎、字符集、字段类型这三个选型看似基础，实际上项目里大量“建表带病上线”的案例都栽在这里。

2.1 引擎：绝大部分场景选InnoDB，外键约束也只有在InnoDB上才可能成立

MySQL里常见的存储引擎是InnoDB和MyISAM。InnoDB支持事务、支持行级锁、支持崩溃恢复，最重要的是，外键约束只有InnoDB才支持。MyISAM连事务都没有，外键写了也会被忽略，加上表级锁在并发写入时表现很差，除了极少数只读历史表、日志表，我已经很多年没有在生产环境建过MyISAM表了。

MySQL 8.0里默认引擎就是InnoDB，如果建表时不写ENGINE=InnoDB，默认也是它。但我还是建议在CREATE TABLE语句里显式写出来，一方面是清晰表达设计意图，另一方面是防止未来有人把参数调到别的引擎去。外键、事务、并发写入这些需求，都默认InnoDB，基本不用犹豫。

2.2 字符集：别让唯一约束在大小写上“开小差”

字符集这个问题，很多团队直到线上出现乱码才想起来检查。字符集不统一导致的乱码只是表象，更隐蔽的是它对约束判断的影响。MySQL里utf8字符集最多只有3个字节，存不了emoji这类4字节字符，而utf8mb4才是完整的UTF-8实现。现在新建表我基本无脑选utf8mb4。

字符集后面带排序规则，这个容易被忽略。排序规则决定字符怎么比较，也就决定了唯一约束怎么判断“重复”。utf8mb4_general_ci和utf8mb4_0900_ai_ci都是大小写不敏感的，也就是说，在这种排序规则下，davy和Davy会被唯一索引判定为同一个值。如果业务上希望邮箱、用户名不区分大小写，这个行为正好合适；但如果你的业务要求严格区分大小写，就必须用utf8mb4_bin。

我见过一个真实案例：用户表用户名加了唯一约束，用了大小写不敏感的排序规则，结果用户注册时输入了不同大小写的用户名，系统提示已存在，用户还以为是撞号了。最后排查下来不是bug，是排序规则定的。所以建表时字符集和排序规则要一起想清楚，库、表、列三级尽量统一，不要建完表之后临时改。

2.3 字段类型选错的三类典型问题

字段类型比约束更容易“看着对，实际错”。第一类是主键用字符串，比如直接拿手机号或身份证号当主键。字符串主键占用空间大，插入乱序导致聚簇索引页分裂，查询性能和价值密度都不如整型。第二类是用FLOAT或DOUBLE存金额。浮点数在二进制里无法精确表示，累计几笔订单后再比较就会冒出0.1这样的幽灵误差。订单金额用DECIMAL(10,2)，精确到分，满打满算能支撑千万级别的单笔订单，不够就放大DECIMAL(12,2)或更高精度。第三类是状态字段的长度乱拍，明明只有三五个状态，却开VARCHAR(255)，浪费空间不说，CHECK约束或者应用层枚举都难维护，状态这种值域固定的字段，用TINYINT配合CHECK语义最清晰。

类型选好之后，约束才有讨论的意义。字段类型本身就是一种软约束，INT决定了它装不下小数，VARCHAR(20)决定了它装不下无限长的文本。这个层次想清楚了，后面写约束会顺手很多。

3. 主键和唯一约束：数据的“身份底线”到底该怎么定

3.1 自增主键、UUID、业务主键：没有银弹，只有取舍

主键是表的身份底线，InnoDB里主键还直接决定聚簇索引的物理排列。很多刚入门的开发者喜欢问“主键到底用自增还是UUID”，我的回答是：看你的部署规模和未来迁移方式。

单体项目、数据量可控、不需要跨库合并，自增整型主键就是最优解。它有序、紧凑、索引友好，写起来也直观。缺点是合并数据库时会撞ID，比如A库和B库各自从1开始自增，合并之后主键冲突，得重新分配。所以需要频繁合并多库的分布式场景，一般会用雪花ID或UUID，其中雪花ID是整型且趋势递增，比字符串UUID更适合做索引；如果用UUID，建议用BINARY(16)存而不是VARCHAR(36)，能省不少空间。

业务主键这条路我基本不推荐。把手机号、身份证、订单号当主键，表面上省了一列，实际上业务规则一变就得改表结构，牵一发动全身。核心表的核心原则是：主键越无关业务越好，让它是纯粹的标识符。业务唯一性交给唯一约束去管，而不是把主键本身变成业务字段。

3.2 唯一约束与NULL：多个“没有手机号”的客户为什么可以共存

唯一约束和主键最大的区别是：唯一约束列允许NULL，主键列不允许。但这里有个特别容易踩的坑：MySQL里，同一列的唯一索引允许存在多个NULL值。

回到客户表，phone字段我设计成VARCHAR(20)，允许NULL，同时加UNIQUE KEY。这时候往表里插三条客户记录，phone全部填NULL，三条都能插进去。MySQL的逻辑是：NULL代表未知，每个NULL都是不同的未知值，所以不冲突。

这个行为要结合业务看待。如果你的需求是“客户可以不填手机号，但填了的不能重复”，那这个设计完美。但如果你另有需求：未填手机号的记录全世界只能保留一条，那唯一约束就兜不住了，需要应用层在写入时先把空值统一成一个固定值，或者用生成列把NULL映射成某个唯一的占位符。类似这种“多个NULL怎么办”的语义，设计阶段就要定清楚，不然上线后要写一堆补救SQL。

4. 外键约束：它保护的不是一条数据，而是一整片数据关系

4.1 用订单和明细两张表说明外键到底在拦什么

外键的职责是维护两张表之间引用的完整性。还是用订单模块举例：orders表的customer_id引用customer表的id，这条外键约束一建立，数据库就开始管两件事。

第一件，往orders表插入一条customer_id=999的订单，如果customer表里没有id=999的客户，这条插入直接失败，报ERROR 1452。这就在数据库层面堵住了“订单找不到客户”的孤儿单。第二件，你想删掉customer表里一个客户，如果orders表还有他的订单，默认情况下删除会失败，因为外键默认的约束规则是RESTRICT，不允许直接删有子记录的父亲。

我见过太多没加外键的表：应用层校验漏了一行，删除接口直接删客户，订单表瞬间全是孤儿单，业务跑起来之后查不到归属，修复成本极高。加了外键之后，至少这类低级事故在数据库层面就拦住了。

4.2 级联策略与生产库里的“去外键派”

外键还能定义父表数据变化时子表怎么跟着变。订单明细表order_items里，order_id引用orders.id，加一句ON DELETE CASCADE，删除订单时它的明细自动跟着删。这样以来，应用层就少写很多“删完主表再删子表”的流水线代码，也不容易出现只删了一半的中间状态。

但CASCADE不是万能的。生产环境里大表级联删除的杀伤力非常大：一条DELETE语句可能瞬间删掉几十万条明细，锁范围拉满，直接拖垮在线业务。所以很多团队会选择逻辑删除而不是物理删除，给订单表加一个is_deleted字段，删除只是改状态，明细永远还在，自然也就不需要级联删除。这种做法规避了外键的一部分性能问题。

关于外键的争议，生产环境里确实有一批“去外键派”，理由不外乎几条：外键写入时要比对父表，性能有损耗；分库分表之后，外键没法跨库建立；微服务各自持有独立库，业务上不再把数据一致性托付给数据库。这些理由都成立，但我想补充一句：外键的缺席不代表约束消失，只是位置变了——变成应用层事务、幂等校验、对账任务。如果你在单体项目里连外键都不敢用，纯粹因为听说“大厂不用”，那就有点因噎废食了。对绝大多数中小规模业务来说，外键是性价比非常高的一致性防线。

5. CHECK约束与非空、默认值：数据库的“边角料规则”值得用好

5.1 CHECK约束：8.0.16是一个分水岭

CHECK约束在MySQL里的命运有点曲折。MySQL 8.0.16之前，CREATE TABLE里的CHECK子句会被解析，但会被静默忽略，表照样能建，约束不会真正执行。这意味着你在5.7版本里写了CHECK (status IN (1,2,3))，数据照样能把4写进去，而且没有任何报错。这个“假约束”最坑人，你以为有保护，实际裸奔。

从8.0.16开始，CHECK约束才在存储引擎层真正生效。MySQL 8.0.19之后还支持ENFORCED和NOT ENFORCED关键字，可以显式开启或忽略某个CHECK约束，方便临时降级检查。

用一个例子看CHECK的实际写法：

CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, customer_id INT UNSIGNED NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(id), CONSTRAINT chk_total_amount CHECK (total_amount >= 0), CONSTRAINT chk_status CHECK (status IN (1,2,3)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

需要注意，CHECK约束只能引用当前表的当前行，不能引用其他表的字段，也不能包含子查询。它适合表达“这个字段必须在某个范围内”这种简单规则，一旦涉及跨表判断，就要改用触发器或者应用层逻辑。如果你还在用5.7版本，又想保证状态字段合法，只能退而求其次用ENUM或者写触发器，心里得有数。

5.2 默认值与“当前时间戳”的配合

非空约束和默认值通常成对出现。一个字段如果业务上必须要有值，就加NOT NULL；如果通常有默认值，就写清楚DEFAULT，别让数据库在“用户没传时”一脸茫然。

时间字段是默认值的重灾区。我强烈建议建表时统一用数据库的当前时间戳来做created_at和updated_at，而不是依赖应用层传入。看上面orders表的写法，created_at的默认值是CURRENT_TIMESTAMP，updated_at额外加ON UPDATE CURRENT_TIMESTAMP。这表示插入时自动写入当前时间，行数据后续发生UPDATE时，updated_at自动刷新。这样应用层少传两个字段，还能保证所有入口写入的时间口径一致。

DATETIME和TIMESTAMP之间，也值得多提一句：TIMESTAMP范围到2038年，同时跟随会话时区；DATETIME范围更大，不随时区漂移。新项目里我更倾向用DATETIME，配合应用层统一管理时区展示，至少不会冒出“2038年问题”。NULL和空字符串的区别也要说清楚：NULL表示“没有这个值”，空字符串表示“知道这个值，内容是空的”，如果一张表里一会儿存NULL一会儿存''，查询条件会被迫写成一大串，业务语义也模糊。设计字段时选定一种语义，全表统一贯彻。

6. 约束、索引与性能：你加的每条规则，数据库都在用索引记账

6.1 约束底层是怎么实现的

网上经常有人问“为什么主键和唯一约束查询那么快”，答案是：它们底层就是索引。约束不是数据库里另存的一张规则表，而是基于索引结构的校验机制。

InnoDB里，PRIMARY KEY直接生成聚簇索引，表数据本身就是按主键顺序物理组织的。UNIQUE约束会自动创建一个唯一二级索引，插入数据时数据库利用索引快速判断“这个值是不是已经存在”。外键做的事情更多一些：父表被引用列必须是一个索引（通常是主键），子表的外键列如果本身没有索引，InnoDB会自动帮你建一个非唯一索引，否则外键删改时要全表扫描判断引用关系，代价太高。

理解这一层之后，再看“加约束会不会拖慢性能”就有谱了：拖慢是必然的，但慢在“多了一次索引查找和校验”，不是慢到不可用。约束的本质是用插入和更新时的查询成本，换来数据的确定性。问题不在于要不要这个成本，而在于你换来的确定性是否值得。

6.2 约束多的表，写放大在哪里

每张表每加一个唯一约束，插入或更新时就要多执行一次唯一性判断；每加一个外键，子表写入时要对父表做一次存在性检查，父表对应行可能被加锁；每加一个CHECK约束，每一行写入都要过一遍表达式，虽然表达式本身很轻，但量大之后都会累积。

外键的级联删除是最典型的写放大场景。ON DELETE CASCADE删除一条父记录，数据库会顺着引用关系把子表所有相关记录删掉，如果子表还有孙子表，一路级联下去，一次删除可能变成几十万次删除。生产大表上这么玩，DBA看了血压都要上来。这也是我前面提到的“约束要精准”的原因：约束不是越多越好，更不该为了存在感堆规则，每个约束都得对应一条明确的业务规则。

还有一个工程上的常见操作：批量导数据时临时SET foreign_key_checks=0，关闭外键检查，导完再开回来。这个操作本身没问题，但要清楚它的代价——关闭期间写入的不合法数据不会被自动拦截，重新开启外键检查时，数据库不会把存量数据重新校验一遍。也就是说，关掉外键导入，导进去的脏数据会安静地躺在表里，直到某次联表查询露出马脚。导完数据一定要主动做一次业务层面的完整性校验，别以为开关一开就万事大吉。

7. 一个建表报错的完整排查链路：ERROR 1215

7.1 复现过程和第一个判断

外键约束相关的报错里，ERROR 1215“Cannot add foreign key constraint”是出现频率最高的。我拿一个很常见的真实场景复现一下。

客户表的id是INT UNSIGNED自增主键：

CREATE TABLE customer ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, customer_no VARCHAR(32) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表的customer_id我一开始写成了普通INT，有符号的：

CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, ... PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后执行：

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer(id);

结果直接报ERROR 1215。这个报错经常把人卡住，因为从语法看，两个字段确实都是INT，为什么加不上外键？

这里就要引出排查的第一步：把两个表的完整定义拉出来对比，重点不是看列名，而是看字段的完整属性。一字不差地看，有没有UNSIGNED、字符集是什么、排序规则是什么、两表引擎是不是一致。很多时候问题不是“类型长得不一样”，而是“类型长得几乎一样，就差一个属性”。

7.2 逐项排除：引擎、类型、符号、字符集与索引

ERROR 1215涉及外键约束，但触发原因不止一个，我按排查顺序整理了一张表。

回到刚才的报错，两边都是INT，但customer.id是INT UNSIGNED，orders.customer_id是INT，UNSIGNED属性不一致，所以报1215。改成INT UNSIGNED之后，外键就加上了。这个坑特别隐蔽，因为SELECT出来看数据无差别，一旦字段值超过有符号INT上限，子表根本存不下，父表却有值，引用完整性无从谈起，所以InnoDB干脆不允许这种字段建立外键。

关于父表列索引的问题，也经常有人踩。如果父表被引用列不是索引，报错通常不是1215，而是ERROR 1822，提示缺少索引。InnoDB要求父表被引用列必须显式存在索引，主键自然满足，但如果你引用了一个普通字段，记得先给那个字段建索引。

7.3 写数据时违反外键的ERROR 1452，是同一个问题的另一面

建表阶段的外键问题排查完，运行阶段还有一个高频报错：ERROR 1452，“Cannot add or update a child row: a foreign key constraint fails”。这个报错的意思是：你往子表插入了一条记录，但引用的父表id不存在。

遇到1452，先别急着怀疑外键写错，按两个方向查：第一，父表里到底有没有这个id，如果父表数据是分批导入的，可能是导数据顺序错了，先导了子表后导父表；第二，子表里插入的那一行，外键列的值是不是被应用层填错了，比如把0当成了默认值来传。0这个值尤其恶心，它不一定存在父表里，却常常被各种语言里“int默认值0”的习惯带进来。

临时处理已经出现问题的场景，可以SET foreign_key_checks=0导数据，但这只是“绕过去”，不是“修好了”。只要外键检查重新开启，之后的新写入依然严格，存量脏数据也不会自动被清理。我个人的习惯是：建表阶段多花十分钟检查类型、符号、字符集，比上线后拿半小时对着一堆1452和1215发愁，要划算得多。

外键这类依赖元数据的报错，排查时最有用的工具就是SHOW CREATE TABLE，把两张表的完整定义并列摆在屏幕上，逐行对照。你多对照几次就会发现，绝大多数约束问题是设计初期的一个小忽略，而这个忽略，恰好会在三个月后的某个深夜变成报警短信。建表这件事，慢即是快，规则先立，后面才省心。