尧图网页设计 
昨天帮同事review建表脚本他看到一张用户表上又挂唯一约束又挂主键而且主键是用了有业务含义的员工编号当时我就觉得这块儿得好好捋一捋。他还理直气壮地说他特地问过DeepSeekDeepSeek告诉他主键和唯一约束都是PostgreSQL里最重要的数据完整性约束所以干脆都加上。这话本身没毛病但问题是——如果分不清这两个约束到底各自在管什么建出来的表要么冗余要么早晚线上翻车。我借着DeepSeek把PostgreSQL约束体系重新梳理了一遍又翻了不少实际生产案例这篇文章就是这次梳理的完整记录。内容围绕两个核心点展开主键和唯一约束分别是什么、底层怎么实现、实际建表怎么选以及那些报错和排查经验。适合正在写业务表的后端开发、刚接手数据库维护的运维以及还在纠结“主键和唯一约束到底啥区别”的学习者。1. 约束体系里主键和唯一约束为什么被单独拎出来1.1 数据完整性约束的四个维度与真实翻车场景PostgreSQL里的数据完整性约束不只有主键和唯一约束完整一套其实是五类NOT NULL、UNIQUE、PRIMARY KEY主键、FOREIGN KEY外键、CHECK。这五类约束分别对应数据完整性里的不同维度NOT NULL管的是“不能为空”UNIQUE管的是“不能重复”PRIMARY KEY管的是“每条记录都要有唯一标识且不能为空”FOREIGN KEY管的是“引用关系的合法性”CHECK管的是“字段值必须满足特定规则”。很多人觉得约束是开发阶段的负担能少建就少建。但真实生产环境里没有约束的后果通常不会立刻暴露而是在某次数据迁移、接口重放、人工补录时集中爆雷。我遇到过一张没有唯一约束的订单表因为上游接口超时重试同一个订单号被插了三次结果财务对账的时候多出来两笔重复流水排查了大半天。还有一张业务表的主键用了自增序列但序列值被手工改过插入时直接撞上已有主键报错信息又不够明显最后只能停机修数据。约束的本质不是限制开发而是把“数据必须是可靠的”这件事交给数据库去保证而不是依赖每个人写代码时都记得做判断。PostgreSQL把主键和唯一约束设计成“既能防脏数据又能顺带提升查询性能”的机制这一点是其他很多约束不具备的所以它俩才会被单独拎出来讲。1.2 主键和唯一约束的共同点与根本差异主键和唯一约束的共同点非常直观都能保证某一列或某几列的组合值不重复而且PostgreSQL在创建这两个约束时都会自动在对应列上建立一个唯一索引。这个自动索引不仅是约束的“裁判”也是查询时的加速器。换句话说只要你对一列建了主键或唯一约束后续拿这列做等值查询、Join连接基本都能走索引。但两者的根本差异在于“是否允许NULL”和“一张表能建几个”。主键约束隐含了NOT NULL语义也就是主键列绝对不允许出现空值唯一约束则允许NULL值而且PostgreSQL默认允许多个NULL并存因为NULL在SQL语义里代表“未知”未知与未知并不被视为相等。另外一张表只能有一个主键但可以有无数个唯一约束。用表格对比会更清楚对比项主键约束唯一约束是否允许NULL不允许默认允许多个NULL每张表数量只能有一个可以有多个自动创建索引是是是否可作为外键父表引用列可以可以核心语义行的唯一身份标识列值的非重复保证最简单的记忆方式主键是“唯一约束 NOT NULL”的组合升级版而唯一约束是一个更宽松的“不允许重复”规则。建表时如果一列既不能为空又要唯一那它就是主键的天然候选人如果一列可以为空只是希望在“有值”时保持唯一那就用唯一约束。2. 主键实操从建表语句到主键索引的全流程解析2.1 三种建主键的方式与选择PostgreSQL里建主键的方式有三种分别适用不同场景。第一种是列级约束在创建表时直接写在字段定义里CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, username text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );这种方式最简洁适合单列主键也是我日常建表的首选。GENERATED ALWAYS AS IDENTITY是SQL标准写法和传统serial相比更规范它不允许用户手工插入id值必须由系统生成能够避免序列和显式插入产生冲突。第二种是表级约束适合复合主键CREATE TABLE order_items ( order_id bigint NOT NULL, item_no int NOT NULL, product_id bigint NOT NULL, quantity int NOT NULL, CONSTRAINT order_items_pkey PRIMARY KEY (order_id, item_no) );复合主键的意思是由多列共同决定一行数据的唯一性。order_id item_no组合起来唯一单独一列可以重复。表级约束可以给约束起名字后期维护、定位报错信息时会更方便。第三种是后期追加适用于表已经存在且需要补主键的情况ALTER TABLE users ADD PRIMARY KEY (id);很多人会遇到“表建完了才发现没有主键”的情况这时候用ALTER TABLE补上即可。但如果表里已经有重复数据或者NULL值这个命令会直接报错必须先清洗数据。选择建议很简单能在一开始设计时确定的就在建表语句里写清楚不确定的宁可在应用层先用唯一约束顶着也别草率选一个会变的业务字段当主键。2.2 主键索引的B-Tree实现逻辑与序列、IDENTITY的选择PostgreSQL的主键约束会自动创建一个B-Tree唯一索引。B-Tree索引的特点是数据按顺序存储查找、范围查询、排序都非常高效。当一个表使用自增列或IDENTITY列作为主键时新插入的数据主键值递增B-Tree索引会直接在末尾追加叶子节点插入效率极高索引页也不会频繁分裂。这也是为什么我一直强调主键最好选单调递增的数字列。反过来如果选UUID作为主键虽然全局唯一性很好但UUID是随机字符串插入时B-Tree索引要频繁做节点分裂和重平衡写入性能会有明显损耗表越大越明显。PostgreSQL 12之后支持了UUID的无序性问题可以通过uuidv7()这类方法生成按时间排序的UUID但普通场景下用自增bigint仍然是最省心的方案。关于数值生成方式serial和IDENTITY的区别需要单独讲。serial是PostgreSQL早期的写法本质是创建一个序列并把它绑定到列的默认值上而GENERATED ALWAYS AS IDENTITY是标准SQL写法更严格不允许显式插入值还支持OVERRIDING SYSTEM VALUE来强行导入。CREATE TABLE t1 ( id serial PRIMARY KEY ); CREATE TABLE t2 ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY );从新项目开发的角度我更推荐IDENTITY。除了规范它还避免了一个serial常见的坑如果表后来做了序列重置显式插入过id导致序列落后再插入数据时就会撞主键。GENERATED ALWAYS直接掐断了“手工插id”这条路这类问题天然少很多。2.3 主键运维中的几个关键注意事项主键虽然建起来简单但运维层面有几个细节特别容易出问题。第一不要选可变业务字段做主键。手机号、邮箱、身份证这类字段虽然稳定但并非完全不可变。一旦业务上允许用户修改手机号而这个手机号又被多个子表引用主键变更会引发连锁更新代价极高。正确做法是引入代理主键用一个和业务无关的自增id做主键把手机号、邮箱做成唯一约束或普通索引。第二主键列一定不要用浮点类型。浮点数存在精度问题自增逻辑在浮点上并不可靠而且float做主键在等值查询时容易因为精度差异匹配不上这不是PostgreSQL特有是所有数据库的通病。第三复合主键的列顺序会影响索引效率。B-Tree索引在最左列等值、第二列等值的情况下效率最高。如果业务经常以item_no单独查询而复合主键是(order_id, item_no)这个索引就无法满足item_no的单独查询需求需要额外建索引。第四主键约束创建时自动生成的索引不建议手动删除。很多人看到系统自动创建了tablename_pkey索引觉得多余就删了结果主键约束直接报错或者索引自动重建。PostgreSQL里主键约束和它的索引是绑定关系手动删索引会用其他方式表露问题最好别碰。3. 唯一约束实操别把NULL和多列唯一想简单了3.1 唯一约束与唯一索引的关系PostgreSQL的实现细节PostgreSQL里有一个容易让新手困惑的点唯一约束和唯一索引到底是不是同一个东西严格来说唯一约束是逻辑层面的约束而唯一索引是物理层面的索引结构。当你执行ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username);PostgreSQL会做两件事在pg_constraint系统表里记录一条唯一约束元数据同时自动创建一个users_username_key唯一索引。如果你直接用CREATE UNIQUE INDEX idx_users_username ON users (username);那只有唯一索引没有约束元数据。两者的差别体现在几个方面唯一约束可以作为外键引用的目标而普通唯一索引在有些场景下也能匹配外键但不规范唯一约束可以被ALTER TABLE DROP CONSTRAINT删除普通唯一索引要用DROP INDEX删除information_schema里的约束查询也只认唯一约束。这个区别在业务上的影响是如果你只是想“这列不能重复”用哪个都行如果你还希望别的表能通过外键引用这一列或者希望约束管理更规范那就用唯一约束。我的习惯是凡是业务规则层面的“不能重复”一律建唯一约束而不是裸的唯一索引因为约束语义更清晰也能被导航到元数据里后期维护时不容易看漏。3.2 NULL 处理与 NULLS NOT DISTINCT唯一约束对NULL的处理是很多人栽过跟头的地方。默认情况下PostgreSQL认为NULL表示“未知”两个“未知”之间是不相等的所以如下建表CREATE TABLE users ( phone text UNIQUE );可以插入两行、三行甚至更多phone为NULL的记录。这在某些场景下是正确的一个用户还没填手机号不应当因为别的用户也没填手机号就冲突。但有些业务逻辑正好相反你希望“手机号要么不填一旦填了就必须全局唯一”而且只允许一个人不填。PostgreSQL 15开始提供了NULLS NOT DISTINCT选项ALTER TABLE users ADD CONSTRAINT users_phone_key UNIQUE NULLS NOT DISTINCT (phone);这样多个NULL就会被当成重复值整个表里只能有一条记录的phone为NULL。这个特性在软删除、唯一业务字段等场景里非常有用。需要特别提醒的是如果用的是普通唯一索引想达到同样的效果可以这样写CREATE UNIQUE INDEX idx_users_phone ON users (phone) NULLS NOT DISTINCT;很多老版本PG不支持NULLS NOT DISTINCT的情况下业界常用“唯一索引 基于NULL的表达式”来做替代方案比如用COALESCE(phone, )但空字符串本身也可能和业务数据冲突需要自己权衡。3.3 复合唯一约束与部分唯一索引的真实案例复合唯一约束是最容易被低估的工具。比如在订单明细表里业务规则是“同一张订单中同一个商品不能出现两次”那就需要约束(order_id, product_id)组合唯一ALTER TABLE order_items ADD CONSTRAINT uk_order_product UNIQUE (order_id, product_id);这时候单独看order_id列可以重复单独看product_id列也可以重复但组合起来必须唯一。它和复合主键的区别就是允许列中有NULL且不承担“行身份标识”的职责。部分唯一索引解决的是“只在特定条件下保持唯一”的需求最典型的就是软删除场景。假设用户表里email是登录凭证删除用户不是物理删除而是给deleted_at字段打时间戳那已删除用户和被删除用户的邮箱其实应该允许重复否则删除一次后这个邮箱就永远无法被新用户注册了。普通唯一约束做不到这一点但部分唯一索引可以CREATE UNIQUE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;这个索引的含义是只在deleted_at IS NULL的存活用户里保持email唯一。已删除用户再多同邮箱都不受影响。这是PostgreSQL一个非常有价值的特性MySQL就没有这么优雅的解法通常要靠冗余状态列再加复合唯一来实现。不过要注意部分唯一索引不是约束所以它不能作为外键的引用目标也不能出现在ON CONFLICT的冲突目标里至少不能直接用。如果业务里既要软删除邮箱唯一又要其他表引用email那需要另外设计不能指望一个部分唯一索引全包。4. 选型指南什么时候用主键什么时候用唯一约束外键怎么指4.1 主键选型的业务键与代理键之争几乎所有数据库设计讨论到后期都会绕到“自然键还是代理键”这个问题上。自然键就是业务里真实存在且有唯一性的字段比如国家代码、身份证号、邮箱代理键则是和业务无关的纯粹标识符比如自增id、UUID。我的立场非常明确核心业务表的物理主键一律用代理键。理由有三点第一自然键可能存在变更一旦变了所有引用它的外键、缓存、日志标识都会跟着变第二自然键通常不是紧凑的数值类型比如邮箱是字符串做主键意味着所有子表外键都要存一个长字符串存储和索引开销成倍放大第三自然键的唯一性往往有额外条件比如“同一租户内邮箱唯一”而不一定是全局唯一这种语义用唯一约束加租户来实现更合理。具体到选型主键bigint GENERATED ALWAYS AS IDENTITY承担行的身份标识被子表外键引用。唯一约束自然业务键如手机号、邮箱、用户名。这些字段可能需要登录、合并、变更但它们不能重复。这样分工之后主键和唯一约束各司其职不会混成一锅粥。4.2 外键REFERENCES指向主键还是唯一键外键设计有个常见疑问子表引用父表时到底必须指向主键还是可以指向唯一约束列答案是两者都可以PostgreSQL允许外键引用的只是父表上“唯一约束或主键约束”涉及的列。比如用户表的主键是id同时username上有唯一约束那么登录日志表既可以引用id也可以引用usernameCREATE TABLE user_login_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username text REFERENCES users(username) );虽然能这样做但我通常建议只要不是特别需要语义化外键就优先引用主键id。原因在于被引用的主键是代理键值稳定且类型精简而username虽然唯一但仍属业务键一旦用户改名PostgreSQL的ON UPDATE CASCADE虽然能自动更新子表但级联更新大表时会产生大量锁和WAL日志线上执行一次会让人提心吊胆。引用id就没有这个问题因为id永远不会变。4.3 唯一约束在软删除与多租户场景下的落地技巧软删除和多租户是唯一约束最能发挥价值的两个场景。多租户系统里常见需求是“不同租户下的用户可以拥有相同的手机号或邮箱”但同一租户内不能重复。用复合唯一约束就能直接解决ALTER TABLE tenant_users ADD CONSTRAINT uk_tenant_phone UNIQUE (tenant_id, phone);这个约束既保证了数据不串租户又允许手机号在不同租户间复用。一套约束搞定不需要应用层加分布式锁。软删除场景需要配合部分唯一索引这在3.3节已经讲过了。这里补充一个操作技巧如果业务既需要软删除邮箱唯一又需要多个已删除用户使用同一邮箱来重新注册建议把“最近一条未删除记录”的邮箱唯一性放在部分唯一索引上同时在应用层保证删除动作和插入动作的事务顺序否则并发下可能有极小概率插入重复数据。实际压测中我用高并发插入验证过单靠部分唯一索引在极端并发下偶尔会产生冲突报错需要在业务代码里对duplicate key做兜底重试而不是默认它一定不会发生。5. 高频报错与排查经验实录5.1 duplicate key value violates unique constraint的几种成因与处理PostgreSQL最经典也最容易遇到的报错就是ERROR: duplicate key value violates unique constraint users_username_key DETAIL: Key (username)(admin) already exists.这个报错的本质有两种可能一是真的插入了重复值二是序列或ID生成逻辑出现了回退导致新插入的值撞上了历史数据。排查时先看DETAIL字段它明确告诉你哪个字段冲突了、冲突值是什么。如果是并发插入场景比如用户同时注册同一个用户名数据库层会拒绝其中一个。处理方式一般是让应用捕获这个唯一冲突异常返回给前端“用户名已存在”或者在INSERT语句里直接使用ON CONFLICT指定冲突行为INSERT INTO users (username, email) VALUES (admin, adminexample.com) ON CONFLICT (username) DO UPDATE SET email EXCLUDED.email;这个写法等于把“检测冲突并选择保留哪条”的逻辑下沉到了数据库省掉一次先查后插的竞态窗口。如果是序列回退导致的主键冲突比如手动插入过id、导入过数据、序列重置过那要在恢复序列之前先查最大值SELECT setval(users_id_seq, (SELECT MAX(id) FROM users));这个操作要谨慎在从库或复制环境上不能直接乱改。5.2 约束添加的锁问题大规模表加约束会锁多久对已有大表添加唯一约束或主键时默认需要扫描全表做唯一性校验并且过程中加的锁是ACCESS EXCLUSIVE也就是说整个表会变成只读甚至不可读写。一张千万级甚至亿级的大表直接执行ALTER TABLE big_table ADD PRIMARY KEY (id);可能阻塞业务几分钟甚至更久这在线上是完全不能接受的。PostgreSQL官方推荐的在线做法是分两步走先并发创建唯一索引再用这个索引去给表加约束。CREATE UNIQUE INDEX CONCURRENTLY idx_big_table_id ON big_table (id); ALTER TABLE big_table ADD CONSTRAINT big_table_pkey PRIMARY KEY USING INDEX idx_big_table_id;CREATE UNIQUE INDEX CONCURRENTLY不会长时间锁表它会以较低的锁级别扫描建索引。索引建好后再通过USING INDEX把普通唯一索引“升级”为约束这一步是元数据操作非常快。唯一注意点是CONCURRENTLY不能放在事务块里执行而且如果建索引过程中发生了冲突会留下一个无效索引需要手动清理。小表无所谓直接加约束即可大表务必走并发索引路线这是我在生产环境里反复验证过的最稳做法。5.3 大批量导入时如何绕过约束检查大批量导入数据时约束检查会成为明显的瓶颈尤其是唯一约束和主键每插入一行都要查一次索引。常见优化路线是导入前删除约束和索引导入后重建。具体步骤大概是这样先把主键约束或唯一约束删除或者明确知道导入数据里没有重复值。用COPY批量导入比一条条INSERT快很多。导入完成后重新创建约束或唯一索引同时可以做一次ANALYZE更新统计信息。这个方案的风险在于导入过程中表处于“无约束”状态如果导入脚本中途出错重跑可能留下重复数据。所以一定要在导入前对数据源做一次去重或者导入到一个临时表/新表校验通过后再做交换改名。如果是超大数据量建议导入前用窗口函数或GROUP BY提前检测重复SELECT username, COUNT(*) FROM users GROUP BY username HAVING COUNT(*) 1;不要抱有“应该不会有重复”的侥幸心理批量导入撞唯一约束的报错会直接断掉整个COPY过程。5.4 排查速查表为了节省排查时间我把上面几个高频问题整理成表格遇到类似情况可以直接对照处理症状可能原因处理方式插入报duplicate key并发插入真实重复捕获冲突或使用ON CONFLICT主键冲突且数值跳跃回退序列落后于已有idsetval到当前MAX(id)大表加约束导致业务卡死默认全表扫描加锁先CONCURRENTLY建索引再用索引加约束多个NULL被唯一约束拦截业务要求NULL也唯一PG15使用NULLS NOT DISTINCT删除用户后邮箱无法复用普通唯一约束限制改为部分唯一索引WHERE deleted_at IS NULL多租户数据串号缺少租户维度约束使用(tenant_id, phone)复合唯一约束COPY大批量导入超慢每个插入都走约束导入前删除约束导入后重建这套速查表是我在实际数据库问题处理中积累出来的覆盖了我见过的绝大多数唯一性相关线上事故。结尾最后想分享的实操习惯用DeepSeek辅助梳理这一轮PostgreSQL约束知识之后我自己最大的收获不是记住了多少语法而是沉淀出一套建表时的判断顺序。现在我每建一张核心业务表都会先问自己三个问题这张表用什么列作为稳定不变的物理主键哪些自然业务键需要保证唯一、且允许为空有没有“特定条件下才唯一”的软删除或租户场景三个问题回答完主键和唯一约束基本就确定了不需要在建模阶段反复纠结。另外我发现跟着DeepSeek这类工具学习数据库基础概念时尽量不要只让它给结论一定要追问一个“为什么”。比如它说“主键自动建索引”那你就追问“这个索引是B-Tree吗为什么有序自增写入更快”。把原理问透比记十条结论有用得多。希望这篇围绕主键和唯一约束的实操总结能帮你少踩几个坑。 PostgreSQL主键与唯一约束：从底层原理到线上避坑实操
昨天帮同事review建表脚本他看到一张用户表上又挂唯一约束又挂主键而且主键是用了有业务含义的员工编号当时我就觉得这块儿得好好捋一捋。他还理直气壮地说他特地问过DeepSeekDeepSeek告诉他主键和唯一约束都是PostgreSQL里最重要的数据完整性约束所以干脆都加上。这话本身没毛病但问题是——如果分不清这两个约束到底各自在管什么建出来的表要么冗余要么早晚线上翻车。我借着DeepSeek把PostgreSQL约束体系重新梳理了一遍又翻了不少实际生产案例这篇文章就是这次梳理的完整记录。内容围绕两个核心点展开主键和唯一约束分别是什么、底层怎么实现、实际建表怎么选以及那些报错和排查经验。适合正在写业务表的后端开发、刚接手数据库维护的运维以及还在纠结“主键和唯一约束到底啥区别”的学习者。1. 约束体系里主键和唯一约束为什么被单独拎出来1.1 数据完整性约束的四个维度与真实翻车场景PostgreSQL里的数据完整性约束不只有主键和唯一约束完整一套其实是五类NOT NULL、UNIQUE、PRIMARY KEY主键、FOREIGN KEY外键、CHECK。这五类约束分别对应数据完整性里的不同维度NOT NULL管的是“不能为空”UNIQUE管的是“不能重复”PRIMARY KEY管的是“每条记录都要有唯一标识且不能为空”FOREIGN KEY管的是“引用关系的合法性”CHECK管的是“字段值必须满足特定规则”。很多人觉得约束是开发阶段的负担能少建就少建。但真实生产环境里没有约束的后果通常不会立刻暴露而是在某次数据迁移、接口重放、人工补录时集中爆雷。我遇到过一张没有唯一约束的订单表因为上游接口超时重试同一个订单号被插了三次结果财务对账的时候多出来两笔重复流水排查了大半天。还有一张业务表的主键用了自增序列但序列值被手工改过插入时直接撞上已有主键报错信息又不够明显最后只能停机修数据。约束的本质不是限制开发而是把“数据必须是可靠的”这件事交给数据库去保证而不是依赖每个人写代码时都记得做判断。PostgreSQL把主键和唯一约束设计成“既能防脏数据又能顺带提升查询性能”的机制这一点是其他很多约束不具备的所以它俩才会被单独拎出来讲。1.2 主键和唯一约束的共同点与根本差异主键和唯一约束的共同点非常直观都能保证某一列或某几列的组合值不重复而且PostgreSQL在创建这两个约束时都会自动在对应列上建立一个唯一索引。这个自动索引不仅是约束的“裁判”也是查询时的加速器。换句话说只要你对一列建了主键或唯一约束后续拿这列做等值查询、Join连接基本都能走索引。但两者的根本差异在于“是否允许NULL”和“一张表能建几个”。主键约束隐含了NOT NULL语义也就是主键列绝对不允许出现空值唯一约束则允许NULL值而且PostgreSQL默认允许多个NULL并存因为NULL在SQL语义里代表“未知”未知与未知并不被视为相等。另外一张表只能有一个主键但可以有无数个唯一约束。用表格对比会更清楚对比项主键约束唯一约束是否允许NULL不允许默认允许多个NULL每张表数量只能有一个可以有多个自动创建索引是是是否可作为外键父表引用列可以可以核心语义行的唯一身份标识列值的非重复保证最简单的记忆方式主键是“唯一约束 NOT NULL”的组合升级版而唯一约束是一个更宽松的“不允许重复”规则。建表时如果一列既不能为空又要唯一那它就是主键的天然候选人如果一列可以为空只是希望在“有值”时保持唯一那就用唯一约束。2. 主键实操从建表语句到主键索引的全流程解析2.1 三种建主键的方式与选择PostgreSQL里建主键的方式有三种分别适用不同场景。第一种是列级约束在创建表时直接写在字段定义里CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, username text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() );这种方式最简洁适合单列主键也是我日常建表的首选。GENERATED ALWAYS AS IDENTITY是SQL标准写法和传统serial相比更规范它不允许用户手工插入id值必须由系统生成能够避免序列和显式插入产生冲突。第二种是表级约束适合复合主键CREATE TABLE order_items ( order_id bigint NOT NULL, item_no int NOT NULL, product_id bigint NOT NULL, quantity int NOT NULL, CONSTRAINT order_items_pkey PRIMARY KEY (order_id, item_no) );复合主键的意思是由多列共同决定一行数据的唯一性。order_id item_no组合起来唯一单独一列可以重复。表级约束可以给约束起名字后期维护、定位报错信息时会更方便。第三种是后期追加适用于表已经存在且需要补主键的情况ALTER TABLE users ADD PRIMARY KEY (id);很多人会遇到“表建完了才发现没有主键”的情况这时候用ALTER TABLE补上即可。但如果表里已经有重复数据或者NULL值这个命令会直接报错必须先清洗数据。选择建议很简单能在一开始设计时确定的就在建表语句里写清楚不确定的宁可在应用层先用唯一约束顶着也别草率选一个会变的业务字段当主键。2.2 主键索引的B-Tree实现逻辑与序列、IDENTITY的选择PostgreSQL的主键约束会自动创建一个B-Tree唯一索引。B-Tree索引的特点是数据按顺序存储查找、范围查询、排序都非常高效。当一个表使用自增列或IDENTITY列作为主键时新插入的数据主键值递增B-Tree索引会直接在末尾追加叶子节点插入效率极高索引页也不会频繁分裂。这也是为什么我一直强调主键最好选单调递增的数字列。反过来如果选UUID作为主键虽然全局唯一性很好但UUID是随机字符串插入时B-Tree索引要频繁做节点分裂和重平衡写入性能会有明显损耗表越大越明显。PostgreSQL 12之后支持了UUID的无序性问题可以通过uuidv7()这类方法生成按时间排序的UUID但普通场景下用自增bigint仍然是最省心的方案。关于数值生成方式serial和IDENTITY的区别需要单独讲。serial是PostgreSQL早期的写法本质是创建一个序列并把它绑定到列的默认值上而GENERATED ALWAYS AS IDENTITY是标准SQL写法更严格不允许显式插入值还支持OVERRIDING SYSTEM VALUE来强行导入。CREATE TABLE t1 ( id serial PRIMARY KEY ); CREATE TABLE t2 ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY );从新项目开发的角度我更推荐IDENTITY。除了规范它还避免了一个serial常见的坑如果表后来做了序列重置显式插入过id导致序列落后再插入数据时就会撞主键。GENERATED ALWAYS直接掐断了“手工插id”这条路这类问题天然少很多。2.3 主键运维中的几个关键注意事项主键虽然建起来简单但运维层面有几个细节特别容易出问题。第一不要选可变业务字段做主键。手机号、邮箱、身份证这类字段虽然稳定但并非完全不可变。一旦业务上允许用户修改手机号而这个手机号又被多个子表引用主键变更会引发连锁更新代价极高。正确做法是引入代理主键用一个和业务无关的自增id做主键把手机号、邮箱做成唯一约束或普通索引。第二主键列一定不要用浮点类型。浮点数存在精度问题自增逻辑在浮点上并不可靠而且float做主键在等值查询时容易因为精度差异匹配不上这不是PostgreSQL特有是所有数据库的通病。第三复合主键的列顺序会影响索引效率。B-Tree索引在最左列等值、第二列等值的情况下效率最高。如果业务经常以item_no单独查询而复合主键是(order_id, item_no)这个索引就无法满足item_no的单独查询需求需要额外建索引。第四主键约束创建时自动生成的索引不建议手动删除。很多人看到系统自动创建了tablename_pkey索引觉得多余就删了结果主键约束直接报错或者索引自动重建。PostgreSQL里主键约束和它的索引是绑定关系手动删索引会用其他方式表露问题最好别碰。3. 唯一约束实操别把NULL和多列唯一想简单了3.1 唯一约束与唯一索引的关系PostgreSQL的实现细节PostgreSQL里有一个容易让新手困惑的点唯一约束和唯一索引到底是不是同一个东西严格来说唯一约束是逻辑层面的约束而唯一索引是物理层面的索引结构。当你执行ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username);PostgreSQL会做两件事在pg_constraint系统表里记录一条唯一约束元数据同时自动创建一个users_username_key唯一索引。如果你直接用CREATE UNIQUE INDEX idx_users_username ON users (username);那只有唯一索引没有约束元数据。两者的差别体现在几个方面唯一约束可以作为外键引用的目标而普通唯一索引在有些场景下也能匹配外键但不规范唯一约束可以被ALTER TABLE DROP CONSTRAINT删除普通唯一索引要用DROP INDEX删除information_schema里的约束查询也只认唯一约束。这个区别在业务上的影响是如果你只是想“这列不能重复”用哪个都行如果你还希望别的表能通过外键引用这一列或者希望约束管理更规范那就用唯一约束。我的习惯是凡是业务规则层面的“不能重复”一律建唯一约束而不是裸的唯一索引因为约束语义更清晰也能被导航到元数据里后期维护时不容易看漏。3.2 NULL 处理与 NULLS NOT DISTINCT唯一约束对NULL的处理是很多人栽过跟头的地方。默认情况下PostgreSQL认为NULL表示“未知”两个“未知”之间是不相等的所以如下建表CREATE TABLE users ( phone text UNIQUE );可以插入两行、三行甚至更多phone为NULL的记录。这在某些场景下是正确的一个用户还没填手机号不应当因为别的用户也没填手机号就冲突。但有些业务逻辑正好相反你希望“手机号要么不填一旦填了就必须全局唯一”而且只允许一个人不填。PostgreSQL 15开始提供了NULLS NOT DISTINCT选项ALTER TABLE users ADD CONSTRAINT users_phone_key UNIQUE NULLS NOT DISTINCT (phone);这样多个NULL就会被当成重复值整个表里只能有一条记录的phone为NULL。这个特性在软删除、唯一业务字段等场景里非常有用。需要特别提醒的是如果用的是普通唯一索引想达到同样的效果可以这样写CREATE UNIQUE INDEX idx_users_phone ON users (phone) NULLS NOT DISTINCT;很多老版本PG不支持NULLS NOT DISTINCT的情况下业界常用“唯一索引 基于NULL的表达式”来做替代方案比如用COALESCE(phone, )但空字符串本身也可能和业务数据冲突需要自己权衡。3.3 复合唯一约束与部分唯一索引的真实案例复合唯一约束是最容易被低估的工具。比如在订单明细表里业务规则是“同一张订单中同一个商品不能出现两次”那就需要约束(order_id, product_id)组合唯一ALTER TABLE order_items ADD CONSTRAINT uk_order_product UNIQUE (order_id, product_id);这时候单独看order_id列可以重复单独看product_id列也可以重复但组合起来必须唯一。它和复合主键的区别就是允许列中有NULL且不承担“行身份标识”的职责。部分唯一索引解决的是“只在特定条件下保持唯一”的需求最典型的就是软删除场景。假设用户表里email是登录凭证删除用户不是物理删除而是给deleted_at字段打时间戳那已删除用户和被删除用户的邮箱其实应该允许重复否则删除一次后这个邮箱就永远无法被新用户注册了。普通唯一约束做不到这一点但部分唯一索引可以CREATE UNIQUE INDEX idx_users_email_active ON users (email) WHERE deleted_at IS NULL;这个索引的含义是只在deleted_at IS NULL的存活用户里保持email唯一。已删除用户再多同邮箱都不受影响。这是PostgreSQL一个非常有价值的特性MySQL就没有这么优雅的解法通常要靠冗余状态列再加复合唯一来实现。不过要注意部分唯一索引不是约束所以它不能作为外键的引用目标也不能出现在ON CONFLICT的冲突目标里至少不能直接用。如果业务里既要软删除邮箱唯一又要其他表引用email那需要另外设计不能指望一个部分唯一索引全包。4. 选型指南什么时候用主键什么时候用唯一约束外键怎么指4.1 主键选型的业务键与代理键之争几乎所有数据库设计讨论到后期都会绕到“自然键还是代理键”这个问题上。自然键就是业务里真实存在且有唯一性的字段比如国家代码、身份证号、邮箱代理键则是和业务无关的纯粹标识符比如自增id、UUID。我的立场非常明确核心业务表的物理主键一律用代理键。理由有三点第一自然键可能存在变更一旦变了所有引用它的外键、缓存、日志标识都会跟着变第二自然键通常不是紧凑的数值类型比如邮箱是字符串做主键意味着所有子表外键都要存一个长字符串存储和索引开销成倍放大第三自然键的唯一性往往有额外条件比如“同一租户内邮箱唯一”而不一定是全局唯一这种语义用唯一约束加租户来实现更合理。具体到选型主键bigint GENERATED ALWAYS AS IDENTITY承担行的身份标识被子表外键引用。唯一约束自然业务键如手机号、邮箱、用户名。这些字段可能需要登录、合并、变更但它们不能重复。这样分工之后主键和唯一约束各司其职不会混成一锅粥。4.2 外键REFERENCES指向主键还是唯一键外键设计有个常见疑问子表引用父表时到底必须指向主键还是可以指向唯一约束列答案是两者都可以PostgreSQL允许外键引用的只是父表上“唯一约束或主键约束”涉及的列。比如用户表的主键是id同时username上有唯一约束那么登录日志表既可以引用id也可以引用usernameCREATE TABLE user_login_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username text REFERENCES users(username) );虽然能这样做但我通常建议只要不是特别需要语义化外键就优先引用主键id。原因在于被引用的主键是代理键值稳定且类型精简而username虽然唯一但仍属业务键一旦用户改名PostgreSQL的ON UPDATE CASCADE虽然能自动更新子表但级联更新大表时会产生大量锁和WAL日志线上执行一次会让人提心吊胆。引用id就没有这个问题因为id永远不会变。4.3 唯一约束在软删除与多租户场景下的落地技巧软删除和多租户是唯一约束最能发挥价值的两个场景。多租户系统里常见需求是“不同租户下的用户可以拥有相同的手机号或邮箱”但同一租户内不能重复。用复合唯一约束就能直接解决ALTER TABLE tenant_users ADD CONSTRAINT uk_tenant_phone UNIQUE (tenant_id, phone);这个约束既保证了数据不串租户又允许手机号在不同租户间复用。一套约束搞定不需要应用层加分布式锁。软删除场景需要配合部分唯一索引这在3.3节已经讲过了。这里补充一个操作技巧如果业务既需要软删除邮箱唯一又需要多个已删除用户使用同一邮箱来重新注册建议把“最近一条未删除记录”的邮箱唯一性放在部分唯一索引上同时在应用层保证删除动作和插入动作的事务顺序否则并发下可能有极小概率插入重复数据。实际压测中我用高并发插入验证过单靠部分唯一索引在极端并发下偶尔会产生冲突报错需要在业务代码里对duplicate key做兜底重试而不是默认它一定不会发生。5. 高频报错与排查经验实录5.1 duplicate key value violates unique constraint的几种成因与处理PostgreSQL最经典也最容易遇到的报错就是ERROR: duplicate key value violates unique constraint users_username_key DETAIL: Key (username)(admin) already exists.这个报错的本质有两种可能一是真的插入了重复值二是序列或ID生成逻辑出现了回退导致新插入的值撞上了历史数据。排查时先看DETAIL字段它明确告诉你哪个字段冲突了、冲突值是什么。如果是并发插入场景比如用户同时注册同一个用户名数据库层会拒绝其中一个。处理方式一般是让应用捕获这个唯一冲突异常返回给前端“用户名已存在”或者在INSERT语句里直接使用ON CONFLICT指定冲突行为INSERT INTO users (username, email) VALUES (admin, adminexample.com) ON CONFLICT (username) DO UPDATE SET email EXCLUDED.email;这个写法等于把“检测冲突并选择保留哪条”的逻辑下沉到了数据库省掉一次先查后插的竞态窗口。如果是序列回退导致的主键冲突比如手动插入过id、导入过数据、序列重置过那要在恢复序列之前先查最大值SELECT setval(users_id_seq, (SELECT MAX(id) FROM users));这个操作要谨慎在从库或复制环境上不能直接乱改。5.2 约束添加的锁问题大规模表加约束会锁多久对已有大表添加唯一约束或主键时默认需要扫描全表做唯一性校验并且过程中加的锁是ACCESS EXCLUSIVE也就是说整个表会变成只读甚至不可读写。一张千万级甚至亿级的大表直接执行ALTER TABLE big_table ADD PRIMARY KEY (id);可能阻塞业务几分钟甚至更久这在线上是完全不能接受的。PostgreSQL官方推荐的在线做法是分两步走先并发创建唯一索引再用这个索引去给表加约束。CREATE UNIQUE INDEX CONCURRENTLY idx_big_table_id ON big_table (id); ALTER TABLE big_table ADD CONSTRAINT big_table_pkey PRIMARY KEY USING INDEX idx_big_table_id;CREATE UNIQUE INDEX CONCURRENTLY不会长时间锁表它会以较低的锁级别扫描建索引。索引建好后再通过USING INDEX把普通唯一索引“升级”为约束这一步是元数据操作非常快。唯一注意点是CONCURRENTLY不能放在事务块里执行而且如果建索引过程中发生了冲突会留下一个无效索引需要手动清理。小表无所谓直接加约束即可大表务必走并发索引路线这是我在生产环境里反复验证过的最稳做法。5.3 大批量导入时如何绕过约束检查大批量导入数据时约束检查会成为明显的瓶颈尤其是唯一约束和主键每插入一行都要查一次索引。常见优化路线是导入前删除约束和索引导入后重建。具体步骤大概是这样先把主键约束或唯一约束删除或者明确知道导入数据里没有重复值。用COPY批量导入比一条条INSERT快很多。导入完成后重新创建约束或唯一索引同时可以做一次ANALYZE更新统计信息。这个方案的风险在于导入过程中表处于“无约束”状态如果导入脚本中途出错重跑可能留下重复数据。所以一定要在导入前对数据源做一次去重或者导入到一个临时表/新表校验通过后再做交换改名。如果是超大数据量建议导入前用窗口函数或GROUP BY提前检测重复SELECT username, COUNT(*) FROM users GROUP BY username HAVING COUNT(*) 1;不要抱有“应该不会有重复”的侥幸心理批量导入撞唯一约束的报错会直接断掉整个COPY过程。5.4 排查速查表为了节省排查时间我把上面几个高频问题整理成表格遇到类似情况可以直接对照处理症状可能原因处理方式插入报duplicate key并发插入真实重复捕获冲突或使用ON CONFLICT主键冲突且数值跳跃回退序列落后于已有idsetval到当前MAX(id)大表加约束导致业务卡死默认全表扫描加锁先CONCURRENTLY建索引再用索引加约束多个NULL被唯一约束拦截业务要求NULL也唯一PG15使用NULLS NOT DISTINCT删除用户后邮箱无法复用普通唯一约束限制改为部分唯一索引WHERE deleted_at IS NULL多租户数据串号缺少租户维度约束使用(tenant_id, phone)复合唯一约束COPY大批量导入超慢每个插入都走约束导入前删除约束导入后重建这套速查表是我在实际数据库问题处理中积累出来的覆盖了我见过的绝大多数唯一性相关线上事故。结尾最后想分享的实操习惯用DeepSeek辅助梳理这一轮PostgreSQL约束知识之后我自己最大的收获不是记住了多少语法而是沉淀出一套建表时的判断顺序。现在我每建一张核心业务表都会先问自己三个问题这张表用什么列作为稳定不变的物理主键哪些自然业务键需要保证唯一、且允许为空有没有“特定条件下才唯一”的软删除或租户场景三个问题回答完主键和唯一约束基本就确定了不需要在建模阶段反复纠结。另外我发现跟着DeepSeek这类工具学习数据库基础概念时尽量不要只让它给结论一定要追问一个“为什么”。比如它说“主键自动建索引”那你就追问“这个索引是B-Tree吗为什么有序自增写入更快”。把原理问透比记十条结论有用得多。希望这篇围绕主键和唯一约束的实操总结能帮你少踩几个坑。