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

上一篇【第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 值UPDATEusersSETphone00000000000WHEREphoneISNULL;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 / homecontact_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(age18ANDage70),salaryNUMERIC(12,2)CHECK(salary0));-- 给约束命名推荐错误信息更清晰CREATETABLEproducts(idSERIALPRIMARYKEY,nameVARCHAR(100)NOTNULL,priceNUMERIC(10,2),discount_priceNUMERIC(10,2),stockINTEGER,CONSTRAINTchk_price_positiveCHECK(price0),CONSTRAINTchk_discountCHECK(discount_price0),CONSTRAINTchk_discount_vs_priceCHECK(pricediscount_price),CONSTRAINTchk_stock_nonnegativeCHECK(stock0));6.2 表级 CHECK 约束-- 引用多列的约束必须是表级约束CREATETABLEmeetings(idSERIALPRIMARYKEY,titleVARCHAR(100),start_timeTIMESTAMPNOTNULL,end_timeTIMESTAMPNOTNULL,room_noVARCHAR(20),CONSTRAINTchk_meeting_timeCHECK(end_timestart_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_time6.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(rating1ANDrating5)-- 日期范围CONSTRAINTchk_birth_dateCHECK(birth_date1900-01-01ANDbirth_dateCURRENT_DATE)-- 枚举值检查比 CHECK 约束更好的做法是用 ENUM 类型CONSTRAINTchk_statusCHECK(statusIN(pending,active,closed,cancelled))-- 百分比CONSTRAINTchk_discountCHECK(discount_pct0ANDdiscount_pct100)NULL 与 CHECK 约束当 CHECK 表达式的计算结果为 NULL 时约束被认为是满足的。所以如果字段允许 NULLNULL 值会绕过 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_idCREATETABLEemployees(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(price0));-- 订单表CREATETABLEorders(idSERIALPRIMARYKEY,user_idINTEGERNOTNULL,order_dateTIMESTAMPDEFAULTNOW(),statusVARCHAR(20)DEFAULTpending);-- 订单明细表CREATETABLEorder_items(order_idINTEGERNOTNULLREFERENCESorders(id)ONDELETECASCADE,product_idINTEGERNOTNULLREFERENCESproducts(id)ONDELETERESTRICT,quantityINTEGERNOTNULLCHECK(quantity0),priceNUMERIC(10,2)NOTNULL,PRIMARYKEY(order_id,product_id));-- 实际操作效果-- 1. 删除订单 → 订单明细自动删除CASCADE-- 2. 删除被订单引用的商品 → 报错阻止RESTRICT-- 3. 删除商品分类 → 如果分类下有商品阻止删除RESTRICT7.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(balance0));-- 默认是 INITIALLY IMMEDIATE立即检查-- 可以指定 INITIALLY DEFERRED延迟检查CREATETABLEaccounts(idSERIALPRIMARYKEY,account_noVARCHAR(20)NOTNULL,balanceNUMERIC(12,2),CONSTRAINTuk_accountUNIQUE(account_no)DEFERRABLE INITIALLY DEFERRED,CONSTRAINTchk_balanceCHECK(balance0)DEFERRABLE INITIALLY DEFERRED);8.2 经典场景两个账户互换唯一编号-- 立即检查时无法完成BEGIN;UPDATEaccountsSETaccount_noBWHEREaccount_noA;-- ERROR: duplicate key value violates unique constraint-- 因为 A 还没改成别的B 已经存在了-- DEFERRABLE 约束可以解决SETCONSTRAINTS uk_account DEFERRED;BEGIN;UPDATEaccountsSETaccount_noTEMPWHEREaccount_noA;UPDATEaccountsSETaccount_noAWHEREaccount_noB;UPDATEaccountsSETaccount_noBWHEREaccount_noTEMP;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(price0)9.2 约束管理-- 查看表的所有约束\dproducts-- 通过系统表查看约束SELECTconnameASconstraint_name,contypeASconstraint_type,pg_get_constraintdef(oid)ASdefinitionFROMpg_constraintWHEREconrelidproducts::regclass;-- contype 值cCHECK, f外键, p主键, u唯一, x排除约束-- 删除约束ALTERTABLEproductsDROPCONSTRAINTchk_products_price;-- 重命名约束ALTERTABLEproductsRENAMECONSTRAINTchk_products_priceTOchk_price_positive;9.3 NOT VALID 约束——在线添加约束在大表上添加约束时锁表时间可能很长。PostgreSQL 支持NOT VALID选项-- 在线添加 CHECK 约束不锁表不验证已有数据ALTERTABLEordersADDCONSTRAINTchk_orders_amountCHECK(amount0)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 约束坑 2CHECK 约束中的函数调用-- CHECK 约束中慎用不稳定函数CONSTRAINTchk_dateCHECK(order_dateCURRENT_DATE)-- CURRENT_DATE 在事务中是稳定的没问题-- 但 NOW() 也是稳定的返回事务开始时间所以也没问题-- 然而使用 RANDOM() 之类的函数就不合适了坑 3ALTER TABLE 添加约束前先清理脏数据-- 先查看有多少数据不满足约束SELECTCOUNT(*)FROMproductsWHEREprice0;-- 修复数据UPDATEproductsSETprice0.01WHEREprice0;-- 再添加约束ALTERTABLEproductsADDCONSTRAINTchk_priceCHECK(price0);十一、总结与下篇预告本文全面讲解了 PostgreSQL 的约束体系NOT NULL最基础的约束确保字段不为空PRIMARY KEY唯一标识自动创建唯一索引UNIQUE防止重复值注意 NULL 值的特殊行为CHECK自定义业务规则功能强大但要注意 NULL 的处理FOREIGN KEY引用完整性级联操作是最重要的特性DEFERRABLE延迟检查解决循环依赖和交换值等特殊场景核心原则是能在数据库层面保证的就不要依赖应用层。约束是数据质量的最后一道防线设计表的时候就应该把约束想清楚。下篇预告第 16 篇将深入讲解触发器Trigger——数据库的自动响应机制。触发器可以在数据变更前后自动执行逻辑是实现审计日志、数据校验、级联操作的利器。掌握触发器你的数据库就拥有了自动反应的能力。上一篇【第14篇】表的高级特性——分区表、继承表与临时表下一篇【第16篇】触发器(Trigger)深度指南——数据库的自动响应机制