1. 别被表象骗了主键和唯一约束的天壤之别从定义到行为我接触 PostgreSQL 这些年被问得最多的问题之一就是主键不就是加了非空限制的唯一约束吗我建唯一约束的时候顺手也写下 NOT NULL 不就一样了吗每次听到这种话我都想叹气。如果你只是把主键当成非空的唯一约束那设计表结构的时候会踩进去一个非常隐蔽的坑索引结构变了查询计划变了外键关联的语义变了甚至连数据清理的顺序都会受影响。先给结论主键PRIMARY KEY和唯一约束UNIQUE CONSTRAINT确实是 PostgreSQL 里保证数据唯一性的两大核心手段它们都能阻止重复数据写入但两者在数据库内部的行为、对查询性能的影响、可空性规则乃至对继承表/分区表的支持上都存在本质区别。展开说之前我先把两者的官方定义摆出来。维度主键 PRIMARY KEY唯一约束 UNIQUE CONSTRAINT空值允许不允许隐式 NOT NULL允许且多个 NULL 不冲突每表数量最多 1 个可多个索引结构默认创建唯一索引无特殊标记创建唯一索引外键引用可以被任何外键引用可以被外键引用但复合外键引用有坑分区表/继承表需特别注意不支持全局唯一同理不自动全局唯一REPLICA IDENTITY优先使用主键做身份标识可设但与主键语义不同这个表值得反复看。但真正的差异远不是一张表能说清的。我的建议是先理解约束存在的目的再去看索引层的行为最后再回到业务场景里去选型。约束这个东西本质上是数据库替你做的保险。主键给的是这一行是谁的答案唯一约束给的是这一列的值不能重复的答案。前者是实体标识后者是业务规则。想明白这一点大部分设计问题就迎刃而解了。2. 索引层到底发生了什么唯一索引、B-Tree 与 NULL 的三方博弈光知道主键不能为空、唯一约束可以为空这种教科书答案解决不了实际问题。你得看到索引层。PostgreSQL 里无论主键还是唯一约束底层默认都是创建一个唯一索引Unique Index。这个索引通常基于 B-Tree 结构实现。唯一索引的语义是索引键值不能重复重复则插入失败。但注意B-Tree 索引里NULL 与 NULL 彼此不相等这一点处处透露出它对未知哲学的理解。2.1 唯一索引的扫表时机令人意外的 uniqueness violation 检查很多人不知道的一个细节是PostgreSQL 在往有唯一约束或主键的表里插入数据时并不总是直接查索引看一眼有没有重复。当插入一个值A时事务系统需要检查唯一性——它的方式是用快照读去查索引看看有没有已经可见的同值记录如果索引命中但记录是其他事务未提交的那么插入事务会等待那个事务结束再重新判断这里有个 Concurrency Control 层面的好戏PostgreSQL 为了保证并发唯一性会对唯一索引条目加 speculative insertion 锁推测插入锁如果冲突事务回滚当前插入可以继续如果冲突提交则报错duplicate key value violates unique constraint。这些底层细节解释了为什么在并发写入场景里唯一约束/主键检查会成为热点的原因之一。同样一个表在低并发下看不出差别一旦上了大并发主键冲突检测的等待链会让你立刻感受到数据库怎么会这么慢。这不是 PostgreSQL 差是唯一性保障固有的代价谁家都一样。2.2 NULL 为什么能让多个相同值共存回到唯一约束允许 NULL 这件事。逻辑上未知不等于空所以 SQL 标准里NULL 不与任何值相等包括 NULL 自己。于是唯一约束里可以出现任意多行的对应列都是 NULL。这在实际业务里非常有用。举个我真实遇到的场景一张user_email_verification表每用户一条记录但只有部分用户绑定了邮箱未绑定的用户邮箱字段是 NULL。如果用唯一约束UNIQUE(user_id, email)那么同一个 user_id 多行各带一个 NULL 是允许的——因为你不能把两个 NULL 判成重复。但如果你把这个约束改成主键就会立刻报错因为主键的多个 NOT NULL 会在复合键上产生重复冲突。也许你会说那我直接在 UNIQUE 列上再写死一个 NOT NULL 约束不就等效于主键了这样想的话你在性能视角上是等效的但在身份语义上依然不是主键。外键引用、ORM 的识别、复制身份的 REPLICA IDENTITY通通不会自动按主键对待。你自己封装的代码里写注释说这个不是主键但是相当于主键等同事接手的时候这种设计通常成为事故多发地带。2.3 复合唯一约束里 NULL 的非常规用法刚才提到UNIQUE(user_id, email)我再展开讲一个很多人不知道的坑复合唯一约束里只要有一列是 NULL整行就不会参与唯一性判定。也就是说-- 假设已有约束UNIQUE(user_id, email) INSERT INTO t (user_id, email) VALUES (1, NULL); INSERT INTO t (user_id, email) VALUES (1, NULL);上面两条都能成功不会触发唯一冲突。原因是 PostgreSQL 判断复合键的唯一性是按整行比较的只要任意一列为 NULL这条索引条目就不被认为与任何其他条目冲突因为 NULL 不等同任何值。这个行为有时候会带来业务隐患。你原本想的是同一个用户只能有一条未绑定邮箱的记录但实际得到的却是可以为同一个用户插上百条 email 为 NULL 的记录。要封死这种漏洞PostgreSQL 有一个精确的解法使用 COALESCE 或部分索引 表达式约束。例如CREATE UNIQUE INDEX idx_unique_user_email ON user_email_verification (user_id, (email IS NOT NULL), email);或者更复杂的COALESCE(email, )表达式索引。原理是把 NULL 映射成一个确定值让 NULL 也能参与唯一性固化。这种高级做法普通文档里很少写但在生产环境非常实用。3. 从索引与约束到查询计划主键/唯一约束如何改变 PostgreSQL 的行事方式在业务代码里你建的约束会直接影响查询优化器planner的判断。很多人低估了这一点。一个表的约束元数据不仅仅是用来卡写入的它是优化器的事实依据之一。举个直觉的例子如果你在WHERE条件里用了主键等值查询优化器非常确信这里最多返回一行所以很可能直接走 Index Scan 而不是 Append 或 Bitmap Heap Scan而对于唯一约束列同样可以推导出最多一行但前提是列级别带有 NOT NULL 信息。3.1 行数估计差异约束越多估计越准PostgreSQL 的优化器通过统计信息pg_statistic和约束信息共同做行数估算。主键/唯一约束因为自带索引在等值查询WHERE col val时优化器能瞬间估算出结果集为 1。但如果在普通列上即使建了普通索引但没有唯一约束优化器只能根据n_distinct来猜遇到数据分布不均时估计行数可能偏差好几倍选出来的执行计划会让人血压飙升。我曾遇到过一个数据量约 2000 万行的订单表查询条件是order_no SN20240101001其中order_no只是普通索引非唯一结果优化器竟然选择了并行 Seq Scan而不是 Index Scan。当时查了很久最后发现是因为这个表经历了大量 UPDATE 和 DELETEpg_stats 里n_distinct失真优化器认为这个值分布太广走索引不划算。我加上唯一约束之后等值查询立刻稳定走索引。约束在这里不是摆设它是优化器判断唯一性的最强证据。3.2 外键引用对约束类型的依赖外键约束FOREIGN KEY引用一张表时被引用的列必须存在唯一约束或主键。这里有个非常容易忽略的点如果引用的列是主键那么外键的 ON UPDATE / ON DELETE 行为语义非常明确主键值不可更新行不可单独重标识如果引用的是普通唯一约束列这个被引用列本身是可变的非主键那么 ON UPDATE CASCADE 就会有真实效果父表改了唯一键的值子表会跟着改。很多团队把业务表的逻辑唯一键比如身份证号、手机号建成了唯一约束而用自增 id 做主键。这没问题但你要意识到如果两个人同时改同一身份证号唯一约束在父表上会挡如果某人把身份证号改成了另一个已存在的值触发的是父表的 unique violation 而不是子表 cascade。这个语义差异一旦理解不到位做起数据订正脚本容易混乱。还有复合外键引用的陷阱CREATE TABLE parent ( a int, b int, PRIMARY KEY (a, b) ); CREATE TABLE child ( a int, b int, FOREIGN KEY (a, b) REFERENCES parent(a, b) );这个设计没问题。但如果 parent 的唯一性是靠UNIQUE(a, b)实现的而且允许 NULL 呢child 表插入(NULL, 5)时外键检查会因为 NULL 而跳过——这在某些业务下是灾难。3.3 REPLICA IDENTITY 和逻辑复制约束的类型决定 WAL 里记录什么PostgreSQL 的逻辑复制逻辑解码 / CDC依赖 REPLICA IDENTITY 决定在 WAL 中如何标识一行。默认是使用主键如果没有主键你可以设置REPLICA IDENTITY USING INDEX指向一个唯一索引。但是如果表既没有主键也没有唯一约束默认REPLICA IDENTITY DEFAULT是 full——每一行更新都记录整行旧值日志量暴涨同一条 UPDATE 的 WAL 体积甚至能扩大 10 倍以上若你用的是唯一约束索引做身份该索引必须是 NOT NULL 列且不能是表达式/部分索引。这部分的实践经验是我在迁移数据到分析型数仓时总结的。当时有一张大表只是业务宽表没有主键想要逻辑复制出来做实时数仓结果每次 UPDATE 的 WAL 撑到带宽告警。加了个唯一约束并用它做 REPLICA IDENTITY 之后负载肉眼可见地降了。别小看这个细节生产事故往往就是这么省出来的。4. 选型实战什么时候用主键什么时候用唯一约束什么时候两个同时上下面这部分是我最想说的。纯理论讲得再多不如给你一套我复盘多次的选型口诀和实战案例。4.1 身份标识用主键业务规则用唯一约束这个原则听起来很简单但执行起来很多人跑偏。举几个我去企业做优化时遇到的问题用户表users(id BIGSERIAL PRIMARY KEY, phone VARCHAR UNIQUE)——这就是标准姿势id是内部身份标识phone是业务唯一键。删掉一行用户业务上要求释放手机号那没问题如果彻底重做系统不再允许手机号被复用你把它当成业务规则加唯一约束即可主键不动。反例有人会把用户表的手机号直接建成主键。这套设计在早期可能跑得通但一旦业务出现账号注销后手机号可以被他人注册的场景主键和业务规则就打架了——你没法在保留主键的情况下让手机号重复。此时主键要重建外键全要改然后把大坑留给后来者。主键是用来在系统内部稳定标识一条数据的锚点唯一约束是用来表达这个世界不允许重复的一件事。不要混用。4.2 用唯一约束 部分索引Partial Index实现软唯一生产环境最常见的需求是业务上同一项记录只能存在一条生效中的但历史记录允许重复。例如优惠券码表同一个码只能被一个用户兑换一次但记录不删除只是标记statusused设备绑定表同一设备同一时间内只能归属一个账户但历史绑定记录都留着。这种场景下全局唯一约束会误伤历史数据。正确方案是部分索引CREATE UNIQUE INDEX idx_active_bind ON device_account_bind (device_id) WHERE status active;这个部分唯一索引只对statusactive的行生效历史行随便查。实现逻辑唯一靠主键 部分唯一索引的组合既不影响身份标识也不影响查询性能。但要注意部分索引不会被外键引用也不会被 REPLICA IDENTITY 使用。它只是一个优化/校验结构不是约束性元数据。别指望它承担约束之外的身份职责。4.3 延迟约束与大数据导入把唯一约束的检查时间后移PostgreSQL 的约束默认是立即检查IMMEDIATE。但唯一约束和主键也支持DEFERRABLE可延迟和INITIALLY DEFERRED初始延迟。什么意思呢就是你可以把一个多行事务里临时产生的中间状态放过去在事务提交时才做最终唯一性检查。我做过一次大数据清洗把一个 5000 万行的历史表去重后重新灌入。如果目标表带着普通唯一约束导入过程中任何一条数据重复都会立刻中断只能分批去重。而如果把约束定义为CREATE TABLE clean_data ( id serial PRIMARY KEY, biz_key text NOT NULL ); ALTER TABLE clean_data ADD CONSTRAINT unique_biz_key UNIQUE (biz_key) DEFERRABLE INITIALLY DEFERRED;在同一个事务里哪怕中间状态有重复比如先插了重复值再在事务末尾删除多余的那一行提交前只要最终状态唯一就能通过。这个特性在处理 ETL、数据订正、跨表数据对齐时极其好用。注意主键或唯一约束定义为 DEFERRABLE 时会略微增加唯一性检查的开销因为数据库需要在事务结束而不是语句结束时评估约束。常规高并发在线业务不建议全员可延迟只在确需的场景开。4.4 并发插入与唯一约束冲突ON CONFLICT 的正确姿势在 PostgreSQL 9.5 之后有了INSERT ... ON CONFLICT DO UPDATE/NOTHING这个能力依赖的正是主键/唯一约束/唯一索引。这里也有一个容易踩的坑当你有多个唯一约束时ON CONFLICT 只能指定其中一个冲突目标比如ON CONFLICT (phone) DO UPDATE。如果你试图同时处理主键和唯一约束的冲突语法上会含糊实际会报错there is no unique or exclusion constraint matching the ON CONFLICT specification。从工程角度看这个功能的底层策略是插入时先走唯一索引快速判断是否冲突如果冲突则走 UPDATE 分支。由于 PostgreSQL 的插入顺序是先插入索引条目、冲突则处理CPU 开销并不高但如果冲突率极高比如 90% 的写入都会碰到已存在的记录那么建议先做一次SELECT预判或用UPDATE ... WHERE前置减少 speculative insertion 锁带来的竞争。4.5 分区表上的主键/唯一约束假象分区表Partitioned Table在 PostgreSQL 12 之后变化很大但有一个语义在 11 之前被很多人反复坑过当你在分区表上定义主键时仅当分区键包含在主键列中PostgreSQL 才允许该约束。例如CREATE TABLE sales ( id bigint, sale_date date, amount numeric, PRIMARY KEY (id, sale_date) ) PARTITION BY RANGE (sale_date);这里分区键 sale_date 必须出现在主键里这就是 PostgreSQL 的硬性限制。如果是非分区的普通表你随意写主键但你想在分区表上搞一个全局自增 id 且不包含分区键的主键PostgreSQL 会直接拒绝你——因为每个分区的唯一索引是独立的分区之间无法保证全局唯一性。也就是说在分区表上唯一约束/主键的控制范围是分区内而非全局。我见过有人用触发器 序列去硬模拟全局唯一结果触发器处理并发时的性能损耗非常难看。更推荐的做法是业务 ID 不带分区键时把主键定义在全局 id 分区键复合列上用生成列GENERATED ALWAYS AS ... STORED从 id 推导出分区键复杂是复杂点但至少不违反分区表的约束规则。5. 实操避坑记录我遇到过的 5 个主键/唯一约束“隐藏行为”最后这部分说说我在实际项目中踩过的以及帮别人排查过的真实问题。这些内容你在官方文档里也查得到但往往要到触发时才意识到。5.1 约束重复定义导致的多余索引浪费有些开发为了保险在同一列上既写了 PRIMARY KEY 又额外CREATE UNIQUE INDEX或者主键建的约束名和另一个唯一约束指向同列同序。PostgreSQL 不会替你去重结果就是同一列上存在两棵几乎一样的索引树。写操作要维护两棵索引存储要双份查询却只可能用其中一棵。用pg_index查一下冗余索引把多余的 DROP 掉是很容易执行的性能保洁。5.2 ALTER TABLE ADD PRIMARY KEY 的花费比你想象的高给一张大表加主键PostgreSQL 会扫描全表做唯一性校验然后构建索引。在 5000 万行的表上这个操作时长可能是几分钟到几十分钟。很多人在生产环境直接执行结果业务阻塞一大片。经验做法是先CREATE UNIQUE INDEX CONCURRENTLY不锁写但会消耗 IO确认没问题后再ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX ...把已经建好的索引挂成约束避免二次扫描。这是 PostgreSQL 官方支持的两步走方案也是我被生产事故教育后最常用的稳定流程。5.3 缺约束的 修复性唯一化 会隐蔽地改写数据当某列原本无约束后来发现数据已存在重复很多人会试图生成唯一索引。但 PostgreSQL 会直接报错duplicate key value violates unique constraint。此时你不能直接加约束必须先处理重复数据。最安全的办法是SELECT col, count(*) FROM tbl GROUP BY col HAVING count(*) 1;然后按业务规则决定保留哪一行比如保留 id 最大的行删掉其余。我强烈建议在删除之前先备份并输出审计日志到文件。这种数据订正操作一旦执行错误几乎没有后悔药。谨慎再谨慎。5.4 唯一约束的命名会影响迁移工具的兼容性很多人建约束时不给名字让 PostgreSQL 自动生成如tbl_col_key。这在 pg_dump 和某些 ORM 迁移工具里会引发约束名不一致问题。举个例子开发环境和生产环境的表结构相同但因为插入顺序不同自动生成的约束名不一定一样。你在迁移脚本里写DROP CONSTRAINT ...时一旦名字写错直接失败。所以重要表上一律显式命名约束ALTER TABLE users ADD CONSTRAINT uq_users_phone UNIQUE (phone);这个小习惯能省掉很多跨环境同步的麻烦。5.5 基于主键的 ON CONFLICT 更新不能随意改目标列ON CONFLICT (id) DO UPDATE SET col EXCLUDED.col里EXCLUDED代表你准备插入但被冲突拦截的那一行。如果你的业务在并发场景下反复执行同一条 id 的 upsert这个写法是幂等的。但要注意如果 UPDATE 分支里还去修改主键或唯一约束列本身比如SET id EXCLUDED.id在大多数情况下无害因为值没变但如果传入的 id 与已存在行的主键不同那就不再是冲突更新而变成数据换锚极可能触发新的一轮唯一冲突。6. 我对主键和唯一约束的最终使用习惯聊了这么多归纳成一句话PostgreSQL 里主键和唯一约束不是或的关系而是分层的关系。主键负责锚定身份的基石唯一约束负责执行业务规则的边界。我个人的习惯是每张常态数据表无论业务上需不需要都建一个代理主键自增或 UUID/雪花 ID。它能保证每一行都有可稳定标识的方式也能让逻辑复制和外键引用顺畅运行。业务上需要的唯一性全部单独建模为唯一约束或部分唯一索引尽量不塞进主键。分区表上只在包含了分区键的情况下定义主键否则就用业务唯一约束 全局序列 触发器的方案绝不硬造全局主键的假象。大表加唯一性永远先用CREATE UNIQUE INDEX CONCURRENTLY两步走避免长时间锁表。唯一约束涉及 NULL 的业务逻辑时先写一个验证 SQL 清楚确认我这个约束到底允许多少 NULL 并存再决定是否要用表达式索引或部分索引加固。如果你在自己的设计和排障中也遇到过类似体会或者有反直觉的案例——尤其是那种明明加了唯一约束还是出现了重复数据的诡异经历大概率就是 NULL 或部分索引在作怪。回头按我上面的几个维度逐一排查基本都能定位到根因。数据库约束这东西设计时多花五分钟运行期少熬五个小时这笔账怎么算都划算。