PostgreSQL UNIQUE INDEX vs PRIMARY KEY
UNIQUE INDEX vs PRIMARY KEY一句话结论PRIMARY KEY UNIQUE NOT NULL 只能有一个。UNIQUE INDEX 可以有多个 允许 NULL 更灵活。核心对比表特性PRIMARY KEYUNIQUE INDEX底层结构B-tree 索引B-tree 索引占用空间相同相同是否允许 NULL❌ 不允许✅ 允许NULL 之间不触发冲突每张表数量限制只能有1 个可以有多个支持ON CONFLICT✅✅隐式创建唯一约束✅ 自动需手动CREATE UNIQUE INDEX语义业务主键行的唯一标识业务唯一性约束辅助索引外键引用✅ 可被外键引用✅ 也可被外键引用底层是同一个东西PostgreSQL 中 PRIMARY KEY 本质上就是NOTNULLUNIQUEINDEX自动命名为 表名_pkey所以两者在存储、查询性能上完全一致没有谁更省空间的说法。NULL 的行为差异重要-- UNIQUE INDEX 允许多行 NULL以下插入不会冲突INSERTINTOt(sell_in_record_id)VALUES(NULL);INSERTINTOt(sell_in_record_id)VALUES(NULL);-- 不报错-- PRIMARY KEY 第一行就会报错INSERTINTOt(sell_in_record_id)VALUES(NULL);-- ERROR: null value in column violates not-null constraint结论如果字段是业务唯一标识且不能为空用 PRIMARY KEY 更安全。ON CONFLICT 写法对比使用 PRIMARY KEYINSERTINTOmagellan_sell_in_summary_raw(sell_in_record_id,qty,...)VALUES(#{sellInRecordId}, #{qty}, ...)ONCONFLICT(sell_in_record_id)DOUPDATESETqtyexcluded.qty,update_timenow();使用 UNIQUE INDEX-- 完全一样的写法PostgreSQL 自动找到对应的唯一索引INSERTINTOsome_table(unique_col,other_col)VALUES(#{val}, #{other})ONCONFLICT(unique_col)DOUPDATESETother_colexcluded.other_col;两种写法语法完全相同ON CONFLICT (列名)会自动匹配对应的唯一约束无论是 PK 还是 UNIQUE INDEX。什么时候用哪个场景推荐业务主键如sell_in_record_id不允许为空全表唯一标识PRIMARY KEY需要多列组合唯一但不是主键如(version, geo, date)联合唯一UNIQUE INDEX字段可能为 NULL但有值时必须唯一UNIQUE INDEX利用 NULL 不冲突特性需要条件唯一如WHERE delete_flag falseUNIQUE INDEX支持WHERE分区条件需要函数唯一如lower(email)UNIQUE INDEX支持函数索引进阶UNIQUE INDEX 的独特能力1. 条件唯一索引Partial Unique Index-- 只对未删除的记录保证唯一已删除的不限制CREATEUNIQUEINDEXux_email_activeONusers(email)WHEREdelete_flagfalse;PRIMARY KEY 无法做到这一点。2. 函数唯一索引-- 邮箱不区分大小写唯一CREATEUNIQUEINDEXux_email_lowerONusers(lower(email));3. 多列组合唯一不作为主键-- 同一 version geo 组合唯一但主键是自增 idCREATEUNIQUEINDEXux_version_geoONsome_table(version_number,geo_cd);magellan_sell_in_summary_raw 的选择-- 调整前两个索引浪费空间CREATETABLEmagellan_sell_in_summary_raw(id bigserialPRIMARYKEY,-- 索引1on idsell_in_record_idvarchar(267),-- 无约束...);CREATEUNIQUEINDEXux_si_summary_raw_record_idONmagellan_sell_in_summary_raw(sell_in_record_id);-- 索引2on sell_in_record_id-- 调整后一个索引语义清晰CREATETABLEmagellan_sell_in_summary_raw(sell_in_record_idvarchar(267)PRIMARYKEY,-- 索引1唯一on sell_in_record_id...);好处减少 1 个 B-tree 索引节省存储 写入性能更好去掉无业务意义的id字段和bigserial序列对象ON CONFLICT (sell_in_record_id)直接走 PK语义清晰自带NOT NULL保护防止意外写入空值
