很多年前第一次面试面试官问“候选码和主码有什么区别”我张口就来候选码是能唯一标识一条记录的最小属性集合主码是从候选码里挑出来的一个。他接着问“那员工表里手机号和身份证号都是候选码为什么主码还要单独搞一个自增 id”我当场卡住。这个问题表面是概念辨析实际上牵扯到业务唯一性、索引设计、外码引用、甚至隐私合规。后来在真实项目里做用户中心和订单系统才知道这三个概念没吃透设计的表几乎注定返工。这篇文章就从这几个问题切入把候选码、主码、外码掰开揉碎讲清楚配上 SQL 实例和排障经验适合正在学数据库的朋友、准备面试的同学以及被外键约束坑过无数次的后端开发。1. 先搞清楚数据库里的“码”解决的是什么问题1.1 没有码表就是一团乱麻先看一张最朴素的员工表只有姓名、部门、性别三个字段。张三入职两次或者李四从研发部调到财务部你想更新某条记录用姓名定位会怎样重名的人会一起被改。更麻烦的是如果允许插入两条完全一样的记录这张表在逻辑上就不是“集合”了因为集合不允许重复元素关系模型也默认表里不该有重复行。缺了唯一标识删除会删错更新会更新多行关联查询更无从谈起。码的作用就是给每一行一个确定的“身份”。这里的“码”就是教材里常说的 Key开发同学更习惯叫“键”。候选码Candidate Key、主码Primary Key、外码Foreign Key是一套完整的身份标识体系候选码负责“哪些属性组合可以唯一确定一行”主码负责“我们最终选哪个作为正式身份”外码负责“一张表怎么引用另一张表的身份”。三个概念相互独立又紧密相关很多开发对它们的理解只停留在 SQL 语法层面一旦面对真实建模场景就容易翻车。1.2 函数依赖是码的理论起点要理解码绕不开函数依赖。函数依赖说的是属性集合 X 的值一旦确定属性集合 Y 的值也跟着确定记作 X→Y。比如学号确定后姓名、年级、系别都能确定学号→姓名就成立。当 X 能推出关系里的所有属性时X 就是超码如果 X 是最小的超码——去掉任何一个属性都不能推出全集——那 X 就是候选码。这里有个常见误区很多人以为“能唯一确定一行”就够了但候选码还要求“最小性”。比如在学号唯一的表里(学号, 姓名)也能唯一确定一行但它不是候选码因为去掉姓名后学号依然可以推出其他所有属性。候选码必须是“不能再删掉任何属性的超码”这一个“最小性”条件是后续所有键选型判断的起点。凡是跳过函数依赖直接背定义的人遇到复合键和复杂依赖关系基本都会迷路。1.3 一句话区分候选码、主码、外码如果只能记住三句话那就背这三句候选码能唯一确定一行记录的最小属性集合一个表可以有多个候选码。主码从候选码里挑出来一个当“正式身份”的码一个表只能有一个主码。外码子表里用来引用父表主码或唯一键的列组合负责把两张表的关系钉死。这三个概念很容易混淆因为它们都在说“键”但作用维度完全不同。候选码和主码是在“一张表内部”讨论唯一性外码是“跨表”讨论引用关系。用一个不太严谨但很好记的类比候选码是“待选身份证号池”主码是“发出去的那张身份证”外码是“你填在表格上的别人家身份证号”。对比维度候选码主码外码所属范围单表内部单表内部表与表之间数量可以有多个只能有一个可以有多个是否允许 NULL不允许不允许通常允许核心作用标识唯一性的所有候选方案最终被选中的唯一标识维护参照完整性SQL 体现UNIQUE 约束或主键候选PRIMARY KEYFOREIGN KEY2. 候选码唯一且最小还常常不止一个2.1 手工判定候选码的完整步骤遇到复杂表结构时不能靠肉眼猜要按属性闭包的思路一步步算。我给一个可以直接抄的步骤列出关系 R 上的全部函数依赖 F。把所有属性分成几类只出现在函数依赖左侧的、只出现在右侧的、左右都出现的、左右都没出现的。只出现在左侧的属性一定属于每个候选码只出现在右侧的属性一定不属于任何候选码。先拿“只在左侧出现的属性集合”算闭包看能不能推出全部属性。如果能并且去掉其中任何属性都不行那它就是一个候选码。如果推不出全集就把左右都出现过的属性或左右都没出现的属性逐个加进来试对每组组合算闭包然后检查最小性。举个例子。关系 R(A, B, C, D)函数依赖 F {A→B, B→C, D→B}。属性分类A、D 只出现在左侧C 只出现在右侧B 左右都出现。候选码必须包含 A 和 D不能包含 C。先试 (AD)A→B 推出 BB→C 推出 CD→B 推出 B最终 A、D、B、C 全齐说明 AD 是超码。再检查最小性单独的 (A) {A, B, C}缺 D单独的 (D) {D, B, C}缺 A。所以去掉 AD 中任何一个属性都不能推出所有属性候选码就是 AD。再举个例子学生表学号 S#身份证号 IDCard姓名 Name系别 Dept函数依赖 S#→Name/DeptIDCard→Name/Dept。左右分类后能很快看出候选码有两个S# 单独一个IDCard 单独一个。这就是“一个表可以有多个候选码”的典型场景后续选主码时才需要做取舍。2.2 候选码与超码、备用码的边界很多人分不清超码和候选码。超码是“能唯一确定记录”的属性集合候选码是“最小超码”。{(学号, 姓名)} 是超码但去掉姓名后“学号”依然是超码所以它不是候选码。{(学号)} 才是候选码。理论上候选码是最小约束超码可以任意加冗余属性。再说“备用码”Alternate Key。一个表可能有好几个候选码被选为主码的那个是主码剩下没被选中的候选码统称备用码。在 SQL 里备用码通常用 UNIQUE 约束来实现。要注意的是备用码不是“不重要”它一样承担着业务唯一性校验的职责。比如用户表里主码是自增 id身份证号和手机号都是候选码虽然没被选为主码但必须加 UNIQUE 约束否则就会出现两个用户共用一张身份证的脏数据。2.3 业务上怎么找候选码理论讲完了落地时第一件事就是找候选码。这里有几个实操原则第一候选码必须来自真实的业务唯一性。身份证号、手机号、邮箱、车牌号、订单号、合同编号这类业务标识天然就是候选码。第二允许为 NULL 的列永远不能做候选码因为 NULL 的语义是“未知”未知值之间的唯一性判断没有意义。第三要警惕“伪唯一”。比如一个表里存了历史订单业务上说“同一个用户一天只能下一单”听起来 (user_id, order_date) 是候选码但如果系统允许补单、售后单、异常单这个组合往往不可靠。第四多列组合的候选码要特别小心。订单明细表里的 (order_id, item_id) 就是一个典型的复合候选码单靠 order_id 推不出明细的唯一性单靠 item_id 更不行两个列合起来才行。这种复合候选码在实际设计里非常常见也是最容易在后续外码引用时出问题的点。3. 主码从候选码里选一个“当家的”3.1 选主码的四个原则候选码可能有多个但主码只能有一个。到底选谁我的经验是看四个维度最小性能单列就不要用多列单列主码在索引存储、外码引用、SQL 写法上都更简单。稳定性主码一旦确定理论上终身不变。用户名会改身份证号几乎不变自增 id 从生成那天就定死所以自增 id 天然占优。简洁性主码会被二级索引反复存储列越长索引越大查询越慢。VARCHAR(64) 的随机字符串主键性能和维护成本都不如 BIGINT。非敏感主码经常出现在 URL、日志、外码引用里。拿手机号当主码等于把隐私字段散落在各个角落不管从隐私合规还是防爬角度都是灾难。拿用户表举例候选码有 user_id自增代理键、id_card身份证号、mobile手机号。按这四个原则主码选 user_id 是压倒性优势。那身份证号和手机号怎么办加 UNIQUE 约束让它们继续承担“业务唯一性校验”的职责但不再作为被引用的身份标识。3.2 用 SQL 定义主码与备用码定义主码最直接的方式是建表时加 PRIMARY KEY。以 MySQL 为例CREATE TABLE app_user ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, id_card VARCHAR(18) NOT NULL UNIQUE, mobile VARCHAR(20) NOT NULL UNIQUE, nickname VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL ) ENGINEInnoDB;这里 user_id 是主码id_card 和 mobile 是备用码。PostgreSQL 推荐用GENERATED ALWAYS AS IDENTITYOracle 12c 之前用 SEQUENCE TRIGGER12c 之后也支持 IDENTITY。生产环境多数情况不建议继续用AUTO_INCREMENT 手动序列这种容易埋坑的写法能用数据库原生 IDENTITY 就用原生。复合主码的 SQL 写法略有不同。订单明细表的主码通常由 (order_id, item_id) 共同组成CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_id INT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, item_id) );这里 order_id 和 item_id 都不能为 NULL否则违反主码约束。复合主码在逻辑上没问题但带来的问题是所有希望引用这条明细的外码都要同时携带两个列查询和连接会变啰嗦。所以实际项目中也有团队会给明细表加一个无业务含义的自增 id 当主码然后给 (order_id, item_id) 加 UNIQUE 约束让这个组合退化为候选码。两种设计都能跑关键在于你要想清楚哪个维度更重要。3.3 主码选不好索引和存储都会遭殃以 MySQL InnoDB 为例这是最容易踩坑的地方。InnoDB 是聚簇索引表整张表的数据行物理存储在主键索引聚簇索引的 B 树叶子节点上。主码选得好不好直接决定数据写入和查询的性能。主码是自增序列时新插入的行基本是追加到索引末尾页分裂少写入效率高。主码是随机 UUID 时插入位置随机B 树频繁分裂数据和索引文件碎片化严重写入放大明显。更麻烦的是InnoDB 的二级索引叶子节点不存整行的物理地址而是存主键值。主码是 UUID 这种 36 个字符的字符串意味着每个二级索引都会凭空变大很多磁盘和内存成本全跟着涨。如果建表时没有显式定义主键InnoDB 会按顺序找第一个非空的 UNIQUE 列做聚簇索引实在找不到就生成一个隐藏的 6 字节 rowid。这种“没主键也能跑”的表一旦数据量大起来性能、日志、同步工具都会变得不可控。所以我写建表语句的第一条纪律就是每个表先检查有没有主码没有就先补上再谈索引优化。3.4 自然键和代理键之争开发圈里一直在吵“主码到底用自然键还是代理键”。自然键就是有业务含义的键比如身份证号、手机号、国家代码代理键就是纯粹为标识而生的值比如自增 id、UUID、雪花 id。自然键的优点是业务上可读、外部系统容易理解比如国家代码表用 ISO 标准代码做主码就非常合理。缺点是业务一旦变化就牵一发动全身手机号可能注销换绑身份证号涉及隐私字符串本身又长又占空间。代理键的优点是稳定、简短、生成可控、不暴露隐私缺点是没有业务含义业务唯一性必须额外建唯一约束兜住。我的默认建议是内部业务表优先代理键外部标准化字典表优先自然键。用户表、订单表、评论表这类会被大量引用、且业务标识可能发生变化的表用代理键当主码国家代码、币种、省份、枚举词典这类稳定且业务含义明确的表用自然键当主码更顺手。下面的对照表可以帮助快速决策方案优点缺点适用场景自增主键写入紧凑、索引小、实现简单分布式不好生成、可被遍历枚举单库单表、内部系统UUID 主键全局唯一、客户端可生成写入随机、索引膨胀分布式环境但别直接当聚集主键雪花 id 类全局唯一且大致有序需要生成组件、时钟回拨处理分布式高并发写自然键业务可读、无需额外列变更风险高、可能过长、有隐私稳定字典表、外部强约定的标识4. 外码数据库级的关系锁4.1 外码到底“锁”住了什么外码解决的问题是参照完整性子表中的外码值要么等于父表中某个主码/唯一键的值要么是 NULL不允许指向一个不存在的父行。这条约束如果只靠应用层代码保证很容易出漏洞。比如用户下单后立刻注销账号如果业务代码先删用户再插入订单一个并发问题就可能产生僵尸数据。外码的意义在于把“子表必须引用存在的父表记录”这条规则下沉到数据库引擎层面。有了外码约束任何破坏参照完整性的 INSERT、UPDATE、DELETE 都会被数据库拒绝执行错误在源头就被拦住而不是等脏数据进表之后靠对账任务清洗。4.2 外码 SQL 与级联行为的选择创建外码的标准 SQL 长这样CREATE TABLE order ( order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES app_user(user_id) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINEInnoDB;外码的核心是 REFERENCES 子句后面的 ON DELETE 和 ON UPDATE 定义了父表数据变化时子表怎么处理。四种行为看起来简单选错才是大坑RESTRICT / NO ACTION默认行为父表有子记录引用时禁止删除或更新。MySQL 中两者行为几乎一致。CASCADE父表删除子表相关行跟着删父表主键更新子表外码跟着改。SET NULL父表删除后子表外码置为 NULL。前提是外码列没有 NOT NULL 约束。SET DEFAULT少数数据库支持父表删除后子表外码改成默认值。生产上业务流水类表强烈不建议用 ON DELETE CASCADE。一个用户删除账号系统把他名下所有历史订单全删了财务、审计、报表全都会崩。更稳妥的做法是逻辑删除user 表加一个 deleted 字段用户“删除”后只是标记失效订单数据永不移除。如果确实想保留子表行但不再指向父表就设计外码列允许 NULL使用 ON DELETE SET NULL。4.3 外码的 NULL 问题与复合外码很多新手看到外码下意识认为外码列必须 NOT NULL其实不是。外码列的 NULL 含义是“这条记录没有引用任何父行”。比如订单表的 coupon_id 外码不是所有订单都用了优惠券没有用券的订单coupon_id 存 NULL 是合法且合理的。这里的重点是 NULL 和“引用了一个不存在的值”是两回事NULL 是允许的引用不存在的父键则会被外码约束直接拒绝。复合外码的规则也容易被忽略外码引用的列组合必须在父表上有唯一索引主码或 UNIQUE 约束而且子表的列数量、顺序要和父表被引用的列完全一致。举个例子如果订单明细表想用 (order_id, product_id) 去引用商品表商品表就必须有 (order_id, product_id) 这样的复合唯一键否则外码根本建不出来。实际项目里这种跨表复合外码会让数据写入和查询变得非常别扭能用单列尽量用单列。4.4 外码对性能的真实影响说完正确性说性能。外码不是免费午餐它带来的代价经常被低估。插入或更新子表时数据库要检查父表引用是否存在这个过程会给父表行加共享锁高并发写入时锁等待和锁冲突就可能变成瓶颈。删除或更新父表时数据库也要扫子表检查是否还有记录引用这一行。如果子表外码列没建索引这个检查就是全表扫描数据量一大删除父表一行能拖垮整个库。所以有一条铁律外码列必须建索引。不光是性能问题InnoDB 在检查外码时如果子表没有索引会锁住扫描范围造成比预期大得多的锁范围并发和死锁概率直线上升。我之前在一个订单系统里排查过一次“删除用户超时”最后发现就是子表订单表没给 user_id 建索引删除一个用户触发了全表扫描加锁直接把连接池打满。5. 一单到底订单系统里三个码怎么落地5.1 从需求到建表理论说再多不如一套完整的建表场景。现在模拟一个小型电商订单系统的表设计用户表、商品表、订单表、订单明细表把它们之间候选码、主码、外码一次理清。用户表已经建过主码是 user_id备用码是 id_card 和 mobile。商品表类似主码用自增 product_idproduct_code 业务编码加 UNIQUE。核心是订单表和明细表CREATE TABLE order ( order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL UNIQUE, user_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES app_user(user_id) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINEInnoDB; CREATE TABLE order_item ( order_id BIGINT NOT NULL, item_id INT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(12,2) NOT NULL, PRIMARY KEY (order_id, item_id), CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES order(order_id) ON DELETE CASCADE, CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT ) ENGINEInnoDB;这里的候选码分布订单表的候选码是 order_id 和 order_no主码选了 order_idorder_no 用 UNIQUE 兜住“一个业务单号不能重复”。订单明细表的候选码是 (order_id, item_id)主码直接就是它如果想把明细表外码引用变得更灵活可以给明细表加一个自增 id 当主码再把 (order_id, item_id) 设为 UNIQUE。两种都合规只是权衡点不同。5.2 现场演练哪些组合是候选码很多初级开发在设计订单表时会提出“用 user_id created_at 当唯一键行不行”。这个想法听起来好像能唯一标识一笔订单但实际上完全不可靠同一用户在同一秒下了两笔单怎么办同一秒内一单一退、业务上又允许重复提交怎么办用户 id 和订单创建时间组合既无法从业务上证明唯一也无法在数据库层写成候选码因为数据库只能保证这两个字段的组合不重复保证不了“业务上一秒只能下一单”。正确的候选码判断逻辑是先看业务规则里哪些字段或字段组合具备“唯一确定一条订单”的潜力再验证它是不是最小超码。order_no 是唯一的业务单号满足order_id 是自增代理键满足(user_id, created_at) 不具备业务唯一性不满足。所以最后能进入主码候选池的只有 order_id 和 order_no 两个。这就是候选人筛选宁可少不能滥。5.3 每次评审都该过的检查清单我自己在设计评审时会对着一个检查清单逐条过每张表强烈建议你也存一份每个表都有主码吗没有就是设计缺陷。主码是否满足稳定、简短、尽量单列不满足就考虑换代理键。业务上的其他候选码是否都建了 UNIQUE 约束否则业务唯一性没有数据库保证。每个外码列都建索引了吗没建先补再提性能优化。外码的 ON DELETE / ON UPDATE 行为是否符合业务语义误用 CASCADE 删数据是线上事故级别的问题。有没有循环外码A 表引用 B 表、B 表又引用 A 表是设计坏味道要尽早拆。有没有一张表被多个表引用却没有稳定主键这是外码设计失败的前兆。这套清单在需求评审阶段花五分钟过一遍能省掉后面数不清的脏数据和返工。6. 面试高频题和上线后排障实录6.1 五道经典辨析题把面试和实战里最常被问到的辨析题整理成一个表方便复习问题简明答案候选码和主码什么区别候选码是可能被选为唯一标识的最小属性集合可以多个主码是从候选码中选出的那个正式标识只能一个。外码一定引用主码吗不一定外码可以引用父表的任意唯一键但被引用的列必须有唯一索引。一张表最多有几个主码一个。一张表只能有一个主码但可以有多个候选码和多个 UNIQUE 约束。主码列可以为空吗不可以。主码的每个列都隐式带有 NOT NULL 约束。外码列可以为空吗可以。NULL 表示没有引用父表是合法状态但造成“孤儿引用”的是非 NULL 且不存在的值。这些问题看着基础但面试官要听的不是定义背诵而是你能不能结合实际例子解释。比如回答候选码和主码区别时能顺带说一个“用户表有 id_card 和 mobile 两个候选码、主码选 id”的例子说服力瞬间不一样。6.2 四个真实踩坑现场第一个坑删除父表行报Cannot delete or update a parent row: a foreign key constraint fails。排查思路是先找哪些子表引用了这张父表把引用数据查出来要么先删/改子表数据要么改用逻辑删除不要在不确定业务语义时暴力关闭外码检查。SET FOREIGN_KEY_CHECKS0这种命令适合数据迁移的特定场景生产环境执行要极度谨慎。第二个坑自增主键用完。曾经见过一张表用 INT 自增主键到 21 亿上限后插入直接报主键重复。解决方式是 ALTER TABLE 把列类型改成 BIGINT但大表改列类型会重建表耗时很长。更早的防范是在新表上直接用 BIGINT或者申请更大的自增范围。这个坑提醒我主码选型必须考虑未来十年数据量INT 在互联网场景下已经越来越不够用了。第三个坑外码列没建索引导致删除超时。前面讲过父表删除时要扫子表子表没有索引就是全表扫描。有一次业务反馈“删除一个用户卡了 5 分钟”查慢日志发现子订单表 user_id 没索引加了索引后删除从分钟级降到毫秒级。凡是外码列建索引这句话值得写进团队规范。第四个坑UNIQUE 约束在 NULL 上失灵。MySQL 里 UNIQUE 约束允许多个 NULL 并存用户表 mobile 加了 UNIQUE但没加 NOT NULL结果系统里出现了好几条 mobile 为 NULL 的“无手机号用户”。真正要做的是把 mobile 设为 NOT NULL没有手机号时给空字符串占位或者根据数据库能力用部分索引。这个坑的本质是候选码的语义不允许 NULL实现时就要一并处理 NOT NULL。6.3 关于码的实用经验数据库设计里很多问题追根溯源都能落到码的选择上。我在实际项目里的默认组合是“代理主键 业务唯一约束 外码建索引”。代理主键保证引用稳定唯一约束兜住业务唯一性外码索引保证关联查询和父表操作性能。这套组合不一定适合所有业务字典表用自然键、时序表不要主键的场景也有但它确实帮我躲过了大多数返工和线上事故。如果只留一句话我会说先想清楚哪几个属性组合是候选码再从候选码里挑一个当主码最后用外码把关系说清楚。这三个问题想透了表结构设计就成功了大半。