当前位置：首页 > news >正文

【PostgreSQL从零到精通】第15篇：约束与数据完整性——让数据库帮你守住数据质量的底线

news 2026/5/4 2:38:25

上一篇【第14篇】表的高级特性——分区表、继承表与临时表
下一篇【第16篇】触发器(Trigger)深度指南——数据库的自动响应机制

标签：PostgreSQL、主键、外键、唯一约束、CHECK约束、NOT NULL、DEFERRABLE、级联操作
摘要：数据质量是数据库的生命线。PostgreSQL 提供了完整的约束体系来保证数据的完整性和一致性。本文从 NOT NULL 到主键、从外键级联到 DEFERRABLE 延迟检查，结合真实场景全面讲解约束的使用方法，帮你构建坚不可摧的数据质量防线。

一、开篇引言

你有没有遇到过这样的"数据事故"：

两个用户注册了相同的手机号，导致短信发错人
订单引用了一个不存在的商品ID，导致对账对不上
有人往"年龄"字段里填了 -5，还有填了 99999 的
删除了一个部门，但部门下的员工记录还在，成了"孤儿数据"

这些问题的根源只有一个：约束没做好。很多开发者习惯在应用层做数据校验，但应用层校验有两个致命弱点：一是可能被绕过（直接操作数据库、SQL注入），二是在并发场景下可能有竞态条件。

数据库约束是数据质量的最后一道防线——无论数据从哪里进来（应用、批量导入、手动SQL），都会被检查。本文将系统讲解 PostgreSQL 的五大约束类型，以及它们的高级用法。

二、五大约束类型总览

PostgreSQL 约束体系 ├── NOT NULL — 非空约束，最基础 ├── PRIMARY KEY — 主键，唯一 + 非空 ├── UNIQUE — 唯一约束，值不重复 ├── CHECK — 检查约束，自定义规则 └── FOREIGN KEY — 外键约束，引用完整性

三、NOT NULL 非空约束

最简单也最常用的约束，确保字段不为空：

-- 建表时定义CREATETABLEusers(idSERIALPRIMARYKEY,usernameVARCHAR(50)NOTNULL,emailVARCHAR(100)NOTNULL,phoneVARCHAR(20),-- 可以为 NULLbioTEXT);-- ALTER TABLE 添加非空约束ALTERTABLEusersALTERCOLUMNphoneSETNOTNULL;-- ALTER TABLE 移除非空约束ALTERTABLEusersALTERCOLUMNphoneDROPNOTNULL;

踩坑提醒：给已有数据的列添加 NOT NULL 时，如果存在 NULL 值会报错。需要先处理 NULL 值：
UPDATEusersSETphone='00000000000'WHEREphoneISNULL;ALTERTABLEusersALTERCOLUMNphoneSETNOTNULL;

四、PRIMARY KEY 主键

主键 = UNIQUE + NOT NULL，一张表只能有一个主键：

-- 建表时定义主键CREATETABLEcategories(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL);-- 复合主键（多列组成唯一标识）CREATETABLEorder_items(order_idINTEGERNOTNULL,product_idINTEGERNOTNULL,quantityINTEGERNOTNULL,priceNUMERIC(10,2),PRIMARYKEY(order_id,product_id));-- ALTER TABLE 添加主键ALTERTABLEproductsADDCONSTRAINTpk_productsPRIMARYKEY(id);-- 查看表的主键信息\d products

五、UNIQUE 唯一约束

确保列值或列组合不重复：

-- 单列唯一约束CREATETABLEusers(idSERIALPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100)NOTNULL,phoneVARCHAR(20));-- 添加唯一约束（带名称）ALTERTABLEusersADDCONSTRAINTuk_users_emailUNIQUE(email);-- 复合唯一约束（手机号 + 类型组合唯一）CREATETABLEcontacts(idSERIALPRIMARYKEY,phoneVARCHAR(20)NOTNULL,typeVARCHAR(20)NOTNULL,-- 'mobile' / 'work' / 'home'contact_nameVARCHAR(50),UNIQUE(phone,type)-- 同一手机号可以有不同类型的记录);

5.1 唯一约束 vs 唯一索引

-- 唯一约束（推荐，语义更明确）ALTERTABLEusersADDCONSTRAINTuk_users_emailUNIQUE(email);-- 唯一索引（效果类似，但语义不同）CREATEUNIQUEINDEXidx_users_emailONusers(email);

两者的主要区别：

唯一约束是 SQL 标准的，出现在\d输出中，可以被外键引用
唯一索引可以在表达式或部分列上创建
唯一约束底层会自动创建唯一索引

5.2 唯一约束允许 NULL

-- 注意：UNIQUE 约束允许多个 NULL 值-- 也就是说，email 列有多个 NULL 是合法的INSERTINTOusers(username,email)VALUES('user1',NULL);INSERTINTOusers(username,email)VALUES('user2',NULL);-- 两条记录的 email 都是 NULL，不违反 UNIQUE 约束

六、CHECK 检查约束——自定义业务规则

CHECK 约束是最灵活的约束类型，可以定义任意业务规则：

6.1 基础用法

-- 年龄约束CREATETABLEemployees(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,ageINTEGERCHECK(age>=18ANDage<=70),salaryNUMERIC(12,2)CHECK(salary>0));-- 给约束命名（推荐，错误信息更清晰）CREATETABLEproducts(idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,priceNUMERIC(10,2),discount_priceNUMERIC(10,2),stockINTEGER,CONSTRAINTchk_price_positiveCHECK(price>0),CONSTRAINTchk_discountCHECK(discount_price>0),CONSTRAINTchk_discount_vs_priceCHECK(price>discount_price),CONSTRAINTchk_stock_nonnegativeCHECK(stock>=0));

6.2 表级 CHECK 约束

-- 引用多列的约束必须是表级约束CREATETABLEmeetings(idSERIALPRIMARYKEY,titleVARCHAR(100),start_timeTIMESTAMPNOTNULL,end_timeTIMESTAMPNOTNULL,room_noVARCHAR(20),CONSTRAINTchk_meeting_timeCHECK(end_time>start_time),CONSTRAINTchk_meeting_durationCHECK(EXTRACT(EPOCHFROM(end_time-start_time))<=480-- 最长8小时));-- 验证INSERTINTOmeetings(title,start_time,end_time,room_no)VALUES('产品评审','2024-01-15 09:00','2024-01-15 10:00','A301');-- 成功INSERTINTOmeetings(title,start_time,end_time,room_no)VALUES('Bug回顾','2024-01-15 10:00','2024-01-15 09:00','A301');-- ERROR: new row for relation "meetings" violates check constraint "chk_meeting_time"

6.3 实战：常见 CHECK 约束模式

-- 身份证号格式（18位）CONSTRAINTchk_id_cardCHECK(id_card~'^\d{17}[\dXx]$')-- 手机号格式CONSTRAINTchk_phoneCHECK(phone~'^1[3-9]\d{9}$')-- 邮箱格式CONSTRAINTchk_emailCHECK(email~'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')-- 评分范围CONSTRAINTchk_ratingCHECK(rating>=1ANDrating<=5)-- 日期范围CONSTRAINTchk_birth_dateCHECK(birth_date>='1900-01-01'ANDbirth_date<CURRENT_DATE)-- 枚举值检查（比 CHECK 约束更好的做法是用 ENUM 类型）CONSTRAINTchk_statusCHECK(statusIN('pending','active','closed','cancelled'))-- 百分比CONSTRAINTchk_discountCHECK(discount_pct>=0ANDdiscount_pct<=100)

NULL 与 CHECK 约束：当 CHECK 表达式的计算结果为 NULL 时，约束被认为是满足的。所以如果字段允许 NULL，NULL 值会绕过 CHECK 约束。如果需要确保字段值满足条件且不为空，要同时加 NOT NULL。

七、FOREIGN KEY 外键约束

外键约束保证引用完整性——子表中的值必须在父表中存在。

7.1 基本语法

-- 创建父表CREATETABLEdepartments(idSERIALPRIMARYKEY,dept_nameVARCHAR(50)NOTNULLUNIQUE);-- 创建子表（外键引用父表）CREATETABLEemployees(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,dept_idINTEGERREFERENCESdepartments(id),salaryNUMERIC(12,2));-- 插入数据INSERTINTOdepartments(dept_name)VALUES('研发部');INSERTINTOdepartments(dept_name)VALUES('市场部');-- 正常插入（dept_id = 1 存在于 departments 表中）INSERTINTOemployees(name,dept_id,salary)VALUES('张三',1,15000);-- 成功-- 错误插入（dept_id = 999 不存在）INSERTINTOemployees(name,dept_id,salary)VALUES('李四',999,12000);-- ERROR: insert or update on table "employees" violates foreign key constraint-- DETAIL: Key (dept_id)=(999) is not present in table "departments".

7.2 外键的级联操作

外键最强大的功能是级联操作，定义父表数据变更时子表的行为：

-- 级联删除（删除部门时，自动删除该部门的所有员工）CREATETABLEemployees(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,dept_idINTEGERREFERENCESdepartments(id)ONDELETECASCADE);-- 级联更新（修改部门ID时，自动更新员工的 dept_id）CREATETABLEemployees(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,dept_idINTEGERREFERENCESdepartments(id)ONUPDATECASCADEONDELETESETNULL-- 部门被删除时，员工的 dept_id 设为 NULL);-- 级联操作选项：-- ON DELETE CASCADE — 父表删除时，子表对应行也删除-- ON DELETE SET NULL — 父表删除时，子表对应外键设为 NULL-- ON DELETE SET DEFAULT — 父表删除时，子表对应外键设为默认值-- ON DELETE RESTRICT — 父表有子表引用时，阻止删除（默认行为）-- ON DELETE NO ACTION — 与 RESTRICT 相同

7.3 级联操作实战：电商场景

-- 商品分类表CREATETABLEcategories(idSERIALPRIMARYKEY,nameVARCHAR(50)NOTNULL,parent_idINTEGERREFERENCEScategories(id)ONDELETECASCADE);-- 商品表CREATETABLEproducts(idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,category_idINTEGERNOTNULLREFERENCEScategories(id)ONDELETERESTRICT,priceNUMERIC(10,2)CHECK(price>0));-- 订单表CREATETABLEorders(idSERIALPRIMARYKEY,user_idINTEGERNOTNULL,order_dateTIMESTAMPDEFAULTNOW(),statusVARCHAR(20)DEFAULT'pending');-- 订单明细表CREATETABLEorder_items(order_idINTEGERNOTNULLREFERENCESorders(id)ONDELETECASCADE,product_idINTEGERNOTNULLREFERENCESproducts(id)ONDELETERESTRICT,quantityINTEGERNOTNULLCHECK(quantity>0),priceNUMERIC(10,2)NOTNULL,PRIMARYKEY(order_id,product_id));-- 实际操作效果：-- 1. 删除订单 → 订单明细自动删除（CASCADE）-- 2. 删除被订单引用的商品 → 报错阻止（RESTRICT）-- 3. 删除商品分类 → 如果分类下有商品，阻止删除（RESTRICT）

7.4 复合外键

-- 父表：学生选课表CREATETABLEenrollments(student_idINTEGERNOTNULL,course_idINTEGERNOTNULL,semesterVARCHAR(20)NOTNULL,gradeVARCHAR(2),PRIMARYKEY(student_id,course_id,semester));-- 子表：成绩变更记录CREATETABLEgrade_changes(idSERIALPRIMARYKEY,student_idINTEGERNOTNULL,course_idINTEGERNOTNULL,semesterVARCHAR(20)NOTNULL,old_gradeVARCHAR(2),new_gradeVARCHAR(2),change_timeTIMESTAMPDEFAULTNOW(),FOREIGNKEY(student_id,course_id,semester)REFERENCESenrollments(student_id,course_id,semester)ONDELETECASCADE);

八、DEFERRABLE 约束——延迟检查

默认情况下，约束在每条 SQL 语句执行后立即检查。DEFERRABLE 约束允许推迟到事务结束时再检查，这在某些场景下非常有用。

8.1 基本用法

-- 创建 DEFERRABLE 约束CREATETABLEaccounts(idSERIALPRIMARYKEY,account_noVARCHAR(20)NOTNULLUNIQUEDEFERRABLE,balanceNUMERIC(12,2)CHECK(balance>=0));-- 默认是 INITIALLY IMMEDIATE（立即检查）-- 可以指定 INITIALLY DEFERRED（延迟检查）CREATETABLEaccounts(idSERIALPRIMARYKEY,account_noVARCHAR(20)NOTNULL,balanceNUMERIC(12,2),CONSTRAINTuk_accountUNIQUE(account_no)DEFERRABLE INITIALLY DEFERRED,CONSTRAINTchk_balanceCHECK(balance>=0)DEFERRABLE INITIALLY DEFERRED);

8.2 经典场景：两个账户互换唯一编号

-- 立即检查时无法完成BEGIN;UPDATEaccountsSETaccount_no='B'WHEREaccount_no='A';-- ERROR: duplicate key value violates unique constraint-- 因为 A 还没改成别的，B 已经存在了-- DEFERRABLE 约束可以解决SETCONSTRAINTS uk_account DEFERRED;BEGIN;UPDATEaccountsSETaccount_no='TEMP'WHEREaccount_no='A';UPDATEaccountsSETaccount_no='A'WHEREaccount_no='B';UPDATEaccountsSETaccount_no='B'WHEREaccount_no='TEMP';COMMIT;-- 事务结束时才检查，此时不违反约束

8.3 手动控制约束检查时机

-- 临时延迟约束检查SETCONSTRAINTSALLDEFERRED;-- 延迟所有 DEFERRABLE 约束SETCONSTRAINTS uk_account DEFERRED;-- 延迟特定约束-- 立即检查约束SETCONSTRAINTSALLIMMEDIATE;-- 立即检查所有约束

九、约束命名规范与最佳实践

9.1 命名规范

-- 好的命名习惯：{constraint_type}_{table_name}_{description}-- pk_：主键-- uk_：唯一约束-- fk_：外键-- chk_：CHECK约束-- nn_：NOT NULL（虽然NOT NULL不能命名，但可以在注释中说明）CONSTRAINTpk_productsPRIMARYKEY(id)CONSTRAINTuk_users_emailUNIQUE(email)CONSTRAINTfk_orders_userFOREIGNKEY(user_id)REFERENCESusers(id)CONSTRAINTchk_products_priceCHECK(price>0)

9.2 约束管理

-- 查看表的所有约束\d+products-- 通过系统表查看约束SELECTconnameASconstraint_name,contypeASconstraint_type,pg_get_constraintdef(oid)ASdefinitionFROMpg_constraintWHEREconrelid='products'::regclass;-- contype 值：c=CHECK, f=外键, p=主键, u=唯一, x=排除约束-- 删除约束ALTERTABLEproductsDROPCONSTRAINTchk_products_price;-- 重命名约束ALTERTABLEproductsRENAMECONSTRAINTchk_products_priceTOchk_price_positive;

9.3 NOT VALID 约束——在线添加约束

在大表上添加约束时，锁表时间可能很长。PostgreSQL 支持NOT VALID选项：

-- 在线添加 CHECK 约束（不锁表，不验证已有数据）ALTERTABLEordersADDCONSTRAINTchk_orders_amountCHECK(amount>0)NOTVALID;-- 后台慢慢验证（不阻塞读写）ALTERTABLEorders VALIDATECONSTRAINTchk_orders_amount;-- 在线添加外键（同样不验证已有数据）ALTERTABLEorder_itemsADDCONSTRAINTfk_order_items_productFOREIGNKEY(product_id)REFERENCESproducts(id)NOTVALID;ALTERTABLEorder_items VALIDATECONSTRAINTfk_order_items_product;

这是大表运维的关键技巧，可以在不停机的情况下添加约束。

十、常见问题与踩坑记录

坑 1：外键循环引用

-- 表A引用表B，表B又引用表A-- 插入时两个表互相等待对方的数据-- 解决：先关闭外键检查，或者使用 DEFERRABLE 约束

坑 2：CHECK 约束中的函数调用

-- CHECK 约束中慎用不稳定函数CONSTRAINTchk_dateCHECK(order_date<=CURRENT_DATE)-- CURRENT_DATE 在事务中是稳定的，没问题-- 但 NOW() 也是稳定的（返回事务开始时间），所以也没问题-- 然而，使用 RANDOM() 之类的函数就不合适了

坑 3：ALTER TABLE 添加约束前先清理脏数据

-- 先查看有多少数据不满足约束SELECTCOUNT(*)FROMproductsWHEREprice<=0;-- 修复数据UPDATEproductsSETprice=0.01WHEREprice<=0;-- 再添加约束ALTERTABLEproductsADDCONSTRAINTchk_priceCHECK(price>0);