DeepSeek 在 PostgreSQL 中，**主键**和**唯一约束**是两种最重要的数据完整性约束

在 PostgreSQL 中主键和唯一约束是两种最重要的数据完整性约束。它们都能保证数据的唯一性但用途和行为有所不同。下面详细讲解如何在 PostgreSQL 表中添加这些约束。一、约束的概念约束类型关键字作用是否创建索引允许 NULL主键PRIMARY KEY唯一标识一行记录是❌ 不允许唯一约束UNIQUE保证列值唯一是✅ 允许一个 NULL二、添加主键约束1. 创建表时定义主键单列主键CREATETABLEusers(user_idSERIALPRIMARYKEY,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100));组合主键多列CREATETABLEorder_items(order_idINT,product_idINT,quantityINT,PRIMARYKEY(order_id,product_id));命名主键约束CREATETABLEusers(user_idSERIAL,usernameVARCHAR(50)NOTNULL,CONSTRAINTpk_users_idPRIMARYKEY(user_id));2. 为已存在的表添加主键基本语法ALTERTABLE表名ADDPRIMARYKEY(列名);示例-- 为已存在的 users 表添加主键ALTERTABLEusersADDPRIMARYKEY(user_id);-- 组合主键ALTERTABLEorder_itemsADDPRIMARYKEY(order_id,product_id);自定义约束名ALTERTABLEusersADDCONSTRAINTpk_usersPRIMARYKEY(user_id);3. 使用现有索引创建主键-- 先创建唯一索引CREATEUNIQUEINDEXidx_users_idONusers(user_id);-- 再将索引转为主键ALTERTABLEusersADDPRIMARYKEYUSINGINDEXidx_users_id;三、添加唯一约束1. 创建表时定义唯一约束单列唯一CREATETABLEusers(user_idSERIALPRIMARYKEY,usernameVARCHAR(50)UNIQUE,-- 方式1简写emailVARCHAR(100)CONSTRAINTusers_email_uniqueUNIQUE-- 方式2命名);多列唯一CREATETABLEusers(user_idSERIALPRIMARYKEY,first_nameVARCHAR(50),last_nameVARCHAR(50),UNIQUE(first_name,last_name)-- 组合唯一);2. 为已存在的表添加唯一约束方式一使用 ALTER TABLE推荐会创建约束对象-- 单列唯一ALTERTABLEusersADDUNIQUE(email);-- 命名约束ALTERTABLEusersADDCONSTRAINTusers_email_uniqueUNIQUE(email);-- 多列唯一ALTERTABLEusersADDCONSTRAINTusers_name_uniqueUNIQUE(first_name,last_name);方式二直接创建唯一索引不创建约束对象-- 创建唯一索引CREATEUNIQUEINDEXidx_users_emailONusers(email);-- 多列唯一索引CREATEUNIQUEINDEXidx_users_nameONusers(first_name,last_name);四、主键与唯一索引的完整示例实际业务场景用户表设计-- 创建用户表CREATETABLEusers(user_idSERIAL,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100)NOTNULL,phoneVARCHAR(20),id_cardVARCHAR(18),statusVARCHAR(20)DEFAULTactive,created_atTIMESTAMPDEFAULTNOW());-- 添加主键ALTERTABLEusersADDCONSTRAINTpk_usersPRIMARYKEY(user_id);-- 添加唯一约束ALTERTABLEusersADDCONSTRAINTusers_username_uniqueUNIQUE(username);ALTERTABLEusersADDCONSTRAINTusers_email_uniqueUNIQUE(email);ALTERTABLEusersADDCONSTRAINTusers_phone_uniqueUNIQUE(phone);ALTERTABLEusersADDCONSTRAINTusers_id_card_uniqueUNIQUE(id_card);-- 查看表结构\d users订单系统示例-- 订单表CREATETABLEorders(order_idSERIAL,order_noVARCHAR(50)NOTNULL,user_idINTNOTNULL,statusVARCHAR(20),created_atTIMESTAMPDEFAULTNOW());-- 订单明细表CREATETABLEorder_items(order_idINT,product_idINT,quantityINTNOTNULL,priceDECIMAL(10,2)NOTNULL);-- 添加主键ALTERTABLEordersADDCONSTRAINTpk_ordersPRIMARYKEY(order_id);ALTERTABLEordersADDCONSTRAINTorders_order_no_uniqueUNIQUE(order_no);ALTERTABLEorder_itemsADDCONSTRAINTpk_order_itemsPRIMARYKEY(order_id,product_id);-- 添加外键约束ALTERTABLEorder_itemsADDCONSTRAINTfk_order_items_ordersFOREIGNKEY(order_id)REFERENCESorders(order_id);五、处理添加约束时的常见问题问题1添加主键时表中有重复数据-- 1. 找出重复数据SELECTuser_id,COUNT(*)FROMusersGROUPBYuser_idHAVINGCOUNT(*)1;-- 2. 删除重复数据保留一条DELETEFROMusers aUSINGusers bWHEREa.user_idb.user_idANDa.ctidb.ctid;-- ctid 是行物理位置保留最新的一条-- 3. 添加主键ALTERTABLEusersADDPRIMARYKEY(user_id);问题2添加唯一约束时有重复值-- 1. 找出重复的 emailSELECTemail,COUNT(*)FROMusersGROUPBYemailHAVINGCOUNT(*)1;-- 2. 处理重复数据例如只保留最新的记录DELETEFROMusersWHERE(email,created_at)NOTIN(SELECTemail,MAX(created_at)FROMusersGROUPBYemail);-- 3. 添加唯一约束ALTERTABLEusersADDCONSTRAINTusers_email_uniqueUNIQUE(email);问题3大表添加约束的性能考虑-- 对大表可以先创建索引再用索引创建约束-- 这样可以更好地控制锁表时间-- 先并发创建唯一索引不阻塞读写CREATEUNIQUEINDEXCONCURRENTLY idx_users_email_tmpONusers(email);-- 再用索引创建约束ALTERTABLEusersADDCONSTRAINTusers_email_uniqueUNIQUEUSINGINDEXidx_users_email_tmp;六、约束的管理和维护查看约束信息-- 查看表的所有约束SELECTconnameASconstraint_name,contypeASconstraint_type,pg_get_constraintdef(oid)ASdefinitionFROMpg_constraintWHEREconrelidusers::regclass;-- contype 说明-- p 主键约束-- u 唯一约束-- f 外键约束-- c 检查约束查看索引信息-- 查看表的所有索引SELECTindexname,indexdefFROMpg_indexesWHEREtablenameusers;删除约束-- 删除主键约束ALTERTABLEusersDROPCONSTRAINTpk_users;-- 删除唯一约束ALTERTABLEusersDROPCONSTRAINTusers_email_unique;-- 删除唯一索引如果是直接创建的索引DROPINDEXidx_users_email;临时禁用约束PostgreSQL 不支持直接禁用约束但可以通过修改约束属性实现类似效果-- 将约束设置为可延迟并在事务中临时推迟检查ALTERTABLEusersALTERCONSTRAINTusers_email_unique DEFERRABLE INITIALLY DEFERRED;-- 在事务中插入数据提交时才检查唯一性BEGIN;SETCONSTRAINTS users_email_unique DEFERRED;INSERTINTOusers(email)VALUES(testexample.com);INSERTINTOusers(email)VALUES(testexample.com);-- 临时重复没问题COMMIT;-- 提交时如果还有重复才会报错七、最佳实践总结设计原则每个表都应该有主键- 通常使用自增列SERIAL 或 IDENTITY业务唯一键用 UNIQUE 约束- 便于管理和文档化组合主键要谨慎- 除非确实需要否则建议用单列代理键 唯一约束索引命名规范- 使用有意义的名称便于维护主键pk_表名唯一约束表名_列名_unique唯一索引idx_表名_列名_unique命名规范示例CREATETABLEproducts(product_idSERIAL,product_codeVARCHAR(50)NOTNULL,product_nameVARCHAR(200)NOTNULL,CONSTRAINTpk_productsPRIMARYKEY(product_id),CONSTRAINTproducts_code_uniqueUNIQUE(product_code));CREATEINDEXidx_products_nameONproducts(product_name);性能考虑大表添加约束- 使用CONCURRENTLY或先用索引再建约束维护窗口- 约束添加会锁表选择业务低峰期操作定期维护- 使用REINDEX重建索引提升性能如果你有具体的表结构或业务需求欢迎告诉我我可以帮你设计合适的约束方案。