MySQL表约束详解：主键、外键、唯一约束与数据质量

1. 约束到底是什么——先想明白为什么要给表“上锁”做 MySQL 开发或者运维写建表语句的时候大部分人第一反应是定字段、选类型然后就没下文了。直到某天线上出现一条业务非法数据或者 join 查询对不上号才回头补约束。我在处理过的不少历史库表里经常看到一张表连主键都没有外键更是一个不建全靠应用层硬扛结果就是数据越滚越脏统计报表怎么对都对不平。所以聊 MySQL 的表的约束本质上不是聊语法是聊数据质量的兜底方案。简单说表的约束就是数据库在写入数据时强制执行的一组“门槛规则”。它不依赖任何上层代码哪怕有人用命令行直接 INSERT绕过所有业务逻辑只要约束在脏数据就进不来。这一点我非常看重因为应用层过滤永远会漏上线时漏一个空值判断后面就是一条 NULL 数据躺在库里分析时到处踩雷。MySQL 里常用的约束分六类非空约束NOT NULL、默认值约束DEFAULT、唯一约束UNIQUE、主键约束PRIMARY KEY、外键约束FOREIGN KEY以及检查约束CHECK。其中主键和唯一约束还会自动创建索引理解这个特性很关键因为它直接影响查询速度。这篇文章我打算从约束的定位讲起接着逐个拆解每一类约束的建表写法、适用场景、运维时怎么改、踩过哪些坑最后给一组排查问题的思路。不管你是刚接触数据库的新手还是已经写了好几年 SQL 的老手只要你想把表结构设计得干净、可控、可持续维护这篇都值得看完。1.1 约束不是限制是数据质量的底线很多人一听“约束”两个字就觉得是束缚好像加了约束写入就变慢、业务就变僵。实际恰恰相反约束是把错误数据挡在门外的最廉价手段。你想想如果没有约束一条订单记录可以没有订单号、没有用户ID甚至数量是负数那后续所有对账、报表、推荐逻辑全是建立在流沙上。我习惯把约束比作安检闸机——乘客可以有很多种但没票的、带违禁品的必须在入口就被拦下来。数据库的约束扮演的就是这个角色。它把校验点从“事后补救”提前到“写入瞬间”这个前置成本比任何清洗脚本都便宜。而且约束还有一个隐性收益它是表结构自带的自文档。后人接手这张表看到 user_id 上有 NOT NULL 和 FK立刻就知道业务上这个字段必须有值、必须引用用户表。不用翻几十页接口文档表结构本身就把规则讲清楚了。1.2 六类约束一句话定位先给一个整体的认知框架后面再逐类展开。如果你赶时间记住这张表就够了。约束类型关键字核心作用是否自动建索引非空约束NOT NULL字段禁止NULL否默认值约束DEFAULT未显式赋值时给默认值否唯一约束UNIQUE字段或组合字段值不重复是主键约束PRIMARY KEY唯一标识一行记录是外键约束FOREIGN KEY保证引用完整性否但通常配合索引使用检查约束CHECK限定字段取值范围否这里有个点容易被忽略主键和唯一约束会创建索引但它们本质是“约束”不是普通索引。很多新人以为加个 PRIMARY KEY 只是为了唯一其实它还承担了 InnoDB 聚簇索引的组织角色。这个深层次含义后面我单独讲主键时再展开。2. 最常用的四个约束——从建表到写入一次讲透2.1 NOT NULL 和 DEFAULT最基础的写入门槛非空约束和默认值约束属于“最不起眼但最救命”的组合。一个典型的用户表建表语句可以这样写CREATE TABLE user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1-正常 0-禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;在这个例子里id 和 username 不允许为空status 和 created_at 不填也有默认值email 则允许为空。这套设计背后的逻辑是业务上“必须有”的字段用 NOT NULL可以“晚点再填”的字段用 DEFAULT NULL同时带上注释。我见过不少项目把手机号、邮箱设置成 NOT NULL结果老用户注册时没有填手机号导致业务无法上线。所以非空约束一定要结合存量数据和上线节奏来评估不是在建模阶段拍脑袋定的。还有一个很容易踩的坑MySQL 里空字符串和 NULL 是完全不同的概念。很多人以为 NOT NULL 就挡住了空值其实 INSERT 一个 照样能进表。如果你要禁止的不仅是 NULL还包括空串那就得上 CHECK 约束或者靠应用层校验。这一点在做用户昵称、商品编码这类字段时尤其要注意。2.2 UNIQUE 唯一约束业务幂等性的守护者唯一约束解决的是“这条记录的业务标识不能重复”的问题。比如用户手机号、身份证号、订单流水号这些字段即使不是主键也必须有唯一性保证。组合唯一约束是很多人没用好的一块。举个例子一个收藏表用户可以对同一篇文章收藏多次吗业务上一般不允许那就要建联合唯一索引CREATE TABLE user_favorite ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, article_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_article (user_id, article_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户收藏表;uk_user_article 这个联合唯一约束的作用就是同一个 (user_id, article_id) 组合只能出现一次。如果没有它应用层需要先 SELECT 再 INSERT中间只要并发稍微一高大量重复数据就涌进去了。而有了这条约束重复写入直接报 1062 错误应用层捕获后做幂等处理即可。这里扩展一个性能视角唯一约束自带索引所以对 uk_user_article 的查询也会走索引。很多慢查询排查到最后发现缺的不是独立索引而是业务上本来就应该存在的唯一约束。索引不是越多越好但业务唯一性约束对应的索引基本都有价值。2.3 主键约束每张表的身份证主键是 InnoDB 表的核心。它有两个层面的作用第一是保证每一行都能被唯一标识第二是作为聚簇索引的组织依据。聚簇索引的意思是数据行本身物理上就是按照主键顺序排列的而不是像 MyISAM 那样数据文件和索引文件分离。这意味着主键的选择会直接影响写入性能。如果主键是自增整型新纪录永远追加在叶子节点末尾写入是顺序的几乎不会触发页分裂。如果主键是 UUID 字符串因为 UUID 完全无序每次插入都要在 B 树中间找位置大概率触发页分裂、页合并性能会明显劣化单表数据量上来以后尤其明显。所以我一般建议能用自增 BIGINT 就用自增 BIGINT别把 UUID 当主键。CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, order_no VARCHAR(64) NOT NULL COMMENT 业务订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;在这个表里id 是自增主键order_no 是业务唯一键。两者分工明确id 管物理组织和性能order_no 管业务查找。这是一个非常稳的建模习惯我在很多生产表里都是这么设计的。2.4 主键选择时常见的坑主键这个坑值得单独拿出来说。第一个坑是“无主键表”。有些历史表为了省事没建主键InnoDB 会自动选一个非空唯一索引作为聚簇索引如果连唯一索引都没有就会生成一个隐藏的 ROWID。隐藏主键意味着你无法通过主键高效定位数据binlog 基于行的恢复也可能变得麻烦。对这种表我会建议在主键缺失的情况下选取一个业务稳定唯一的字段后置添加主键。第二个坑是“主键频繁更新”。主键一旦参与聚簇索引更新主键值就相当于搬动一整行物理位置代价很高。所以主键应该是稳定不变的一般不建议把“可变的业务编号”设为主键。第三个坑是“联合主键滥用”。有些团队喜欢把多个业务字段拼成联合主键比如 (user_id, commodity_id)。数据量小的时候没问题但一旦子表需要引用这张表时外键就要引用全部主键列连表查询和关联都会变得冗长。更推荐的做法是加一个自增 id 做单列主键再把 (user_id, commodity_id) 设置为唯一约束。3. CHECK 约束和外键约束——容易被忽略但很关键3.1 CHECK 约束版本差异要搞清楚CHECK 约束在 MySQL 里是个“历史遗留坑”。在 8.0.16 之前MySQL 虽然支持写 CHECK 语法但它只解析不执行等于白写。很多人建表时写了 CHECK以为数据库会帮忙校验结果数据照样乱进上线后才发现问题。从 8.0.16 开始MySQL 才真正执行 CHECK 约束。所以如果你还在用 5.7 版本写 CHECK 是没意义的要靠应用层校验或者改成 ENUM 类型来限定取值范围。如果你的版本是 8.0.16 及以上就可以放心用CREATE TABLE product_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, product_name VARCHAR(128) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 1, PRIMARY KEY (id), CONSTRAINT chk_price CHECK (price 0), CONSTRAINT chk_stock CHECK (stock 0), CONSTRAINT chk_status CHECK (status IN (0, 1)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT商品表;chk_price 和 chk_stock 能挡住负数价格和负数库存chk_status 把状态限定在 0 和 1 之间。这种约束在业务上非常管用尤其是防住那些手工刷数据、导入脚本漏校验的场景。需要注意 CHECK 也是一个“隐藏陷阱”因为开发环境、测试环境、生产环境版本不一致时你写的 CHECK 可能在某个环境生效、某个环境不生效。上线前我会用SELECT VERSION();确认一下各环境版本避免出现同一条 SQL 在不同环境行为不一致的坑。3.2 外键约束关联完整性的双刃剑外键约束解决的是“这张表的字段引用必须指向另一张表的有效记录”。比如订单表的 user_id必须存在于用户表。加了外键约束任何无效引用都会被数据库拒绝。但外键在互联网场景下的口碑非常分裂。很多大厂会明确禁用外键理由是外键会让每次写入都触发对父表的校验在高并发写入时产生额外锁开销分库分表后外键也直接失效。这个说法确实有道理尤其是单表千万级以上的核心链路外键的代价会被放大。不过这不代表外键一无是处。对于后台管理系统、低频写入、数据一致性要求高的内部系统外键是非常省心的东西。它能保证你永远不会有“孤儿记录”对账和审计省一大半功夫。下面是一个标准的外键写法CREATE TABLE order_detail ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity INT NOT NULL DEFAULT 1, PRIMARY KEY (id), CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES order_info (id) ON DELETE CASCADE, CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES product_info (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单明细表;这里有两点要说明。第一外键列本身必须建索引否则 MySQL 会为外键自动创建一个索引这通常没问题但如果你已经在别的联合索引里覆盖了该列就要留意外键自动建索引的事防止多余索引浪费空间。第二ON DELETE CASCADE不能乱加比如订单删除级联删除明细但如果明细还有业务留痕需求就不应该用 CASCADE而是用一个 delete_flag 做软删除。3.3 外键的实际使用心得我在实际项目里对外键的态度是核心交易链路不用内部管理后台适度用跨库一律不用。为什么核心链路不用因为核心链路并发高外键约束带来的父表校验和锁等待在峰值时可能就是拖垮数据库的那根稻草。而且一旦需要分库分表外键基本是第一个要拆的东西不如从一开始就不建。内部后台系统则相反数据量不大、并发不高外键能防止业务人员误操作产生乱七八糟的数据价值很高。如果你在建表时没加外键后来想加MySQL 是支持 ALTER TABLE 后置添加的ALTER TABLE order_detail ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES order_info (id);但执行之前一定要注意存量数据里如果有 order_id 在 order_info 中不存在这个 ALTER 会直接报错。所以做这种操作之前最好先跑一条检查语句SELECT COUNT(*) FROM order_detail od LEFT JOIN order_info oi ON od.order_id oi.id WHERE oi.id IS NULL;只要返回 0说明数据干净可以放心加外键。返回大于 0就得先修复脏数据再操作。这个检查步骤我每次都会做别抱侥幸心理。4. 约束的日常管理与后期修改4.1 查看一张表已有的约束接手老项目时最要紧的事是盘清楚这张表到底有哪些约束。MySQL 里查约束的方法有几种按需选择。最直观的是SHOW CREATE TABLE它会把建表语句完整打印出来约束定义全都在里面SHOW CREATE TABLE user_info\G输出里能看到PRIMARY KEY、UNIQUE KEY、CONSTRAINT ... FOREIGN KEY、CONSTRAINT ... CHECK等定义一目了然。如果只想看索引和约束的概览用SHOW INDEX FROM user_info;。想用标准查询方式MySQL 8.0 里可以直接查 information_schemaSELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA your_database AND TABLE_NAME user_info;这个查询可以精确列出每张表的约束类型适合写自动化巡检脚本或者批量盘点库表结构。我每次做数据迁移前都会先跑一遍这个查询把源库的约束清单导出迁移后在目标库逐一核对。4.2 后置添加约束与删除约束约束不是只能在建表时定义后期业务变化经常需要加约束或者取消约束。常用的 ALTER 语句整理如下。-- 添加非空约束 ALTER TABLE user_info MODIFY COLUMN username VARCHAR(32) NOT NULL; -- 添加默认值 ALTER TABLE user_info ALTER COLUMN status SET DEFAULT 1; -- 添加唯一约束 ALTER TABLE user_info ADD UNIQUE KEY uk_email (email); -- 删除唯一约束 ALTER TABLE user_info DROP INDEX uk_email; -- 添加检查约束 ALTER TABLE product_info ADD CONSTRAINT chk_stock CHECK (stock 0); -- 删除检查约束 ALTER TABLE product_info DROP CONSTRAINT chk_stock; -- 删除主键约束 ALTER TABLE order_info DROP PRIMARY KEY;在线上执行这些 ALTER 前有两个提醒。第一MODIFY COLUMN在添加 NOT NULL 时会导致全表扫描加元数据锁如果表数据量大、业务不能停一定要用在线 DDL 工具或者选业务低峰期操作否则容易把线上业务卡死。第二添加唯一约束之前必须先检查现有数据是否已经违反唯一性否则 ALTER 会直接失败而且报错信息一般都比较直白——Duplicate entry xxx for key。另外MySQL 8.0 支持原子 DDL一个 ALTER 语句要么全部成功、要么全部回滚比 5.7 的“改一半失败”要安全得多。如果公司还在用 5.7涉及约束变更的脚本一定要先在测试库完整跑一遍。4.3 约束变更时如何处理存量数据约束变更最大的阻碍从来不是语法而是存量数据。我遇到过很多次类似的场景某天业务要求某个字段改成唯一约束但表里早就因为历史 bug 存了几十条重复数据。处理思路其实很固定先查重复再决定保留哪条最后清理完再添加约束。比如要清理 user_info 表里重复的 emailSELECT email, COUNT(*) AS cnt FROM user_info GROUP BY email HAVING COUNT(*) 1;查出重复组之后根据业务规则决定保留最新一条还是最早一条然后删除多余记录。清理完再执行 ALTER 添加约束。这个过程还有一个细节如果重复数据特别多delete 操作可能锁大量行建议分批删除每次删除几千条间隔几秒再继续。直接一个大 DELETE 干下去可能把主库的复制延迟拉高甚至引发锁等待超时。5. 约束相关的常见报错与排查实录5.1 唯一键冲突ERROR 1062这是最常见的约束报错没有之一。错误信息大概是这样的ERROR 1062 (23000): Duplicate entry xxx for key user_info.uk_email排查思路按照三步走。第一步确认冲突源数据把报错的字段值拿出来查一遍。第二步判断应用逻辑是否真的产生了重复提交如果是因为没有先查后插导致的说明应用层幂等没做好要加唯一约束或改逻辑。第三步检查是不是唯一约束定义错了比如本应该做联合唯一结果做成了单列唯一导致不同用户不能使用一样的头像 URL。这个报错在数据导入、批量脚本、消息队列重复消费场景下尤其容易踩。处理消息队列重复消费时不要盲目重试代码里要捕获 1062 然后做幂等返回而不是打错误日志继续抛异常。5.2 外键创建失败ERROR 1215报错信息通常是ERROR 1215 (HY000): Cannot add foreign key constraint这个报错的原因很多最常见的几个是父表和子表的字段类型不一致。比如父表 id 是 BIGINT UNSIGNED子表 order_id 是 BIGINTMySQL 做严格匹配时会拒绝。父表中没有对应的索引。虽然 MySQL 会自动给外键列创建索引但父表被引用的列必须是索引列。存量数据有孤儿记录。就像前面说的子表里有父表不存在的值ADD FK 时直接失败。字符集不一致。父表字段是 utf8mb4子表字段是 utf8外键关联时也可能失败。排查时一条条核对上面四项基本都能找到原因。最隐蔽的是字符集不一致表面看字段类型一模一样其实 COLLATION 不同所以建表时统一 DEFAULT CHARSETutf8mb4 能规避大量这类问题。5.3 修改列类型时被约束阻挡想把一个字段从 VARCHAR 改成 INT但如果这个字段参与约束定义或者有外键引用ALTER 可能直接报错。比如ERROR 1830 (HY000): Column user_id cannot be NOT NULL这通常是字段在某条约束里要求非空但你新类型默认允许 NULLMySQL 需要你显式指定 NOT NULL。遇到这种情况修改的语句要写完整ALTER TABLE order_info MODIFY COLUMN user_id BIGINT UNSIGNED NOT NULL;而不是简单写一个MODIFY COLUMN user_id BIGINT UNSIGNED。这个细节很不起眼但非常容易在改表的时候卡住一半。5.4 常用排查工具与整体思路遇到约束相关问题时我的排查链路一般是这样的先看报错编号再用 SHOW CREATE TABLE 看当前表结构然后查 information_schema 核对约束元数据最后针对存量数据做 COUNT 检查。这一套下来90% 的约束问题都能定位。另外一个工具层面的建议开发环境和测试环境要保证和线上同版本的 MySQL否则 CHECK 约束、外键校验行为都可能不一致很多问题在测试环境“复现不出来”上了线就炸。这种环境差异问题比约束本身更难排查所以我的经验是先把版本统一再谈约束调优。6. 约束设计的几个经验之谈这几轮实战下来我对约束设计有了几点比较明确的体会不一定适用于所有项目但至少能帮你少走弯路。第一个体会是“约束要按数据重要性分层”。不是所有表都追求最强的约束。核心交易表、资金表、用户主数据表主键、非空、唯一、检查约束能上就上它们是地基日志表、流水表、临时表约束可以适度放松因为这类表的主要诉求是写入吞吐过度约束只会拖慢入库速度。分开对待比一刀切合理得多。第二个体会是“命名规范非常重要”。我见过很多库里的约束名叫PRIMARY、id_2、uk_1这种名字在排查问题的时候特别痛苦都不知道这个唯一约束到底约束哪些列。建议大家从建表第一天就统一命名规则主键就是 PRIMARY唯一约束统一uk_表名缩写_字段名外键统一fk_表名缩写_引用表缩写检查约束统一chk_字段名。这个习惯越早养成后期成本越低。第三个体会是“约束变更一定要走变更流程”。我处理过不止一次线上事故都是因为有人直接在线上执行 ALTER TABLE 加唯一约束结果遇到重复数据报错的同时还把表锁了。正确的姿势是先备份再检查存量数据再到低峰期执行加完约束验证一下效果。如果没有变更流程的团队至少要在执行前用EXPLAIN和相关计数查询确认排障时间避免直接影响业务。第四个体会也是我个人最想强调的一点约束不是越全越好而是越贴合业务越好。一张表每个字段都加 CHECK、每个关联都加外键表面看起来“很安全”实际可能把正常的业务迭代卡得死死的。做约束设计时多问自己一句这个字段真的不允许 NULL 吗这个取值以后真的不会扩张吗想清楚了再写进建表语句比我给你任何模板都更有价值。MySQL 表的约束说到底就是一套“把话说在前面”的规则。它帮你把数据质量的底线画清楚让写入方、读取方、维护方在同一套规则下工作。只要你理解了每一类约束的定位、适用场景和版本差异剩下的就是根据业务需求在性能和数据安全之间做权衡。这个权衡没有标准答案但方向对了表结构就不会出大乱子。