在数据库里折腾主键、外键和约束我踩过的坑比很多人写过的代码都多。PostgreSQL 在这方面的能力非常扎实但正因为功能强配置不当的后果也更隐蔽——比如外键没建索引导致父表删除慢到怀疑人生或者大批量导入数据时被自增序列拖到崩溃。这篇文章把 PG 里主键、外键、唯一约束、检查约束、排他约束的配置方法一次性讲透每个方案都会解释背后的原因适合刚接触 PG 的开发者也给正在优化存量表结构的 DBA 提供一些可以落地的经验。1. 先理清约束体系主键外键到底在管什么很多新手以为约束就是加个PRIMARY KEY完事实际上 PostgreSQL 的约束体系是一套完整的防线。主键管的是“每一行都能被唯一确定”外键管的是“引用关系不会悬空”检查约束管的是“字段值必须落在合法范围内”唯一约束管的是“业务上不允许重复的数据不出现重复”非空约束管的是“关键字段必须有值”。这五类约束合在一起才是数据完整性的全貌。从实现机制上看主键和唯一约束在 PG 内部会创建唯一索引来加速唯一性检查外键则是通过内部触发器机制来维护检查约束在每行插入或更新时执行表达式求值。这意味着约束不只是“建表时的几行声明”而是写进系统表、由优化器和执行器共同参与的底层设施。理解这一点后面很多性能问题的排查思路就清晰了。和 MySQL 对比的话PG 的约束更贴近 SQL 标准支持延迟约束INITIALLY DEFERRED、支持NOT VALID加约束后异步校验数据、外键更新动作也分成五档。MySQL 的 InnoDB 虽然也有外键但禁外键用SET FOREIGN_KEY_CHECKS0PG 压根没有这个开关替代方案是删约束重建或者用DISABLE TRIGGER。这也是我见过很多人迁库时卡住的第一个点。整套约束体系讲究的是“提前设计、按需启用”。业务初期为了快速上线表结构怎么简单怎么来但数据量上来之后缺约束造成的脏数据清理成本远高于当初建索引建约束的成本。反过来说约束也不是越多越好——过度设计会在写入链路上增加额外检查合理取舍才是关键。2. 主键的三种创建姿势与选型原则2.1 主键的三种创建方式PostgreSQL 里给表加主键有三条路建表时直接在列上写、建表时在表级别声明、表建好后用ALTER TABLE补加。三种写法在最终元数据上等价但使用场景完全不同。列级写法最简单直接在字段类型后面跟PRIMARY KEYCREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() );表级写法适合复合主键或多列组合CREATE TABLE order_items ( order_id BIGINT NOT NULL, product_id BIGINT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (order_id, product_id) );存量表补主键是线上最常见的操作注意 PG 在补主键时会ACCESS EXCLUSIVE锁表锁的时长取决于表大小和索引创建耗时ALTER TABLE users ADD PRIMARY KEY (id);2.2 主键的幕后行为自动索引和 NOT NULLPRIMARY KEY在 PG 里等价于“唯一约束 非空约束”并且自动创建一个同名的唯一索引名字一般是表名_pkey。比如users表加完主键后索引名就是users_pkey。这个行为很多人没注意但它解释了为什么主键字段天然能加速按主键的查询。主键和唯一索引的差异值得单独说说。两者从查询优化角度看几乎一样但语义上主键多了非空要求而且在pg_constraint系统表里有专门记录。从约束管理的角度主键是约束可以DROP CONSTRAINT唯一索引不是约束直接DROP INDEX就行。另外 PG 里UNIQUE约束也允许 NULL 重复因为标准 SQL 里 NULL 表示“未知”未知不等于未知所以多个 NULL 不冲突。主键则不允许任何 NULL。2.3 主键字段怎么选自增、UUID 还是复合键主键选型是设计阶段最值得花时间讨论的问题。我见过太多项目因为主键选错后期分库分表、数据合库、系统间同步时付出惨痛代价。自增主键用serial或bigserial最省事底层是序列sequence插入数据时取序列值。PG 10 之后更推荐标准写法GENERATED BY DEFAULT AS IDENTITY语义更清晰也解决了serial在pg_dump恢复时序列重置的隐患。bigserial是 64 位数据量再大也比较难耗尽serial是 32 位最大 21 亿左右对很多高速写入场景不够看。UUID 主键适合分布式场景和防止枚举遍历的场景。PG 13 之后内置了gen_random_uuid()无需扩展旧版需要CREATE EXTENSION pgcrypto。UUID 的代价是索引更大插入时随机 IO 更多。如果确实要用通常可以搭配uuid-osp插件生成顺序 UUID 改善索引写入性能但一般应用根本不需要到这一步。复合主键要多想一步未来如果有其他表引用这张表外键列也必须是相同的多列组合。比如订单明细表用(order_id, product_id)做主键那么任何想关联它的表都得背上至少两列外键索引也会跟着变大。能用单列代理主键解决的就别用复合键把业务唯一性交给唯一约束去保证。3. 外键配置引用完整性的正确姿势3.1 外键的基础语法与命名习惯外键在建表时写法有两种简化形式。最省事的是列级引用直接在字段后面加REFERENCESCREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(id), amount NUMERIC(10,2) NOT NULL, status TEXT NOT NULL DEFAULT pending, created_at TIMESTAMPTZ NOT NULL DEFAULT now() );表级写法更正式还能显式命名约束和自己指定匹配动作CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, amount NUMERIC(10,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT );命名约束这一步我强烈建议做。如果不写名字PG 会生成类似orders_user_id_fkey的默认名本身还算有规律但当你后续要DROP CONSTRAINT时依赖默认名很容易出问题。3.2 ON DELETE / ON UPDATE 五种动作怎么选外键的参照动作是最容易踩坑的地方。PostgreSQL 支持五档CASCADE、SET NULL、SET DEFAULT、RESTRICT、NO ACTION。前三个是“主动处理”后两个是“拦截拒绝”。动作行为典型场景CASCADE父表行删/改子表同步删/改主数据删除时明细一并清掉SET NULL父表行删/改子表引用列置 NULL外键列允许为空的历史记录保留SET DEFAULT父表行删/改子表引用列回默认值很少用到需注意默认值指向的记录存在RESTRICT有子表引用时拒绝删除订单主数据不可删NO ACTION与 RESTRICT 类似但检查时机可延后配合延迟约束做事务内调整RESTRICT和NO ACTION的差别很微妙RESTRICT在命令执行时立刻检查NO ACTION默认也是立即检查但如果约束声明为DEFERRABLENO ACTION可以推迟到事务提交时检查。日常用RESTRICT更符合直觉。实际操作里我发现电商场景最常用RESTRICT保护订单、CASCADE清理过期数据、SET NULL保留操作日志。关键原则是删除父表数据前先想清楚子表怎么办而不是让数据库替你乱处理。3.3 外键性能陷阱子表索引不是可选项这是本文最想强调的实操点PostgreSQL 不会自动在外键列上建索引。删除父表一行数据时数据库必须检查子表里有没有引用这行的记录。如果子表外键列没有索引这个检查就是对子表的全表扫描。数据量小的时候没感觉子表百万行的时候就等着被老板骂吧。正确的做法是在子表外键列上手动建索引CREATE INDEX idx_orders_user_id ON orders(user_id);如果是复合外键索引的列顺序要和外键列顺序一致否则索引完全帮不上忙。我排查过一起事故生产库 order_items 表外键是(order_id, product_id)但索引只建了product_id单列删除订单时依然触发全表扫描把 IO 打满。后来改成(order_id, product_id)复合索引问题立刻消失。外键维护还有一个锁层面的坑更新父表主键时PG 需要对子表引用行加FOR KEY SHARE锁在高并发下可能与子表更新产生锁等待甚至死锁。这也是为什么我极少建议把业务主键定义为可更新字段主键一旦落地尽量把它当成永久标识来对待。3.4 级联删除的底层行为和事务边界ON DELETE CASCADE看起来是“一条记录删到底”实际上 PG 通过内部触发器逐层找出所有子记录并删除涉及的行可能非常多。如果级联链很长A - B - C删除 A 的一行可能瞬间产生几万次内部操作每次操作都在同一个事务里。事务越大提交时刷 WAL 和清理死元组的压力越大锁持有时间也越长。我的实践建议是级联删除只用于数据量可控的层级大量数据清理别依赖级联而是在应用层分批删每批几百条配合pg_sleep或任务调度来控制节奏。数据库设计应该让简单操作保持简单而不是把所有逻辑都推给数据库去兜底。4. 唯一约束、检查约束与排他约束的实战细节4.1 唯一约束多列联合唯一和 NULL 行为唯一约束在 PG 里最常见的坑就是 NULL。UNIQUE约束允许 NULL并且多个 NULL 不互相冲突。这在业务上既有好处也有风险好处是软删场景能把“唯一标识 删除标记”组合使用风险是如果你想让 NULL 也参与唯一性判断PG 14 之前没有直接办法。PG 15 引入了NULLS NOT DISTINCT可以做到“多个 NULL 也视为重复”。多列唯一约束也很常用比如同一个人不能对同一商品评价两次CREATE TABLE product_reviews ( user_id BIGINT NOT NULL REFERENCES users(id), product_id BIGINT NOT NULL REFERENCES products(id), rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5), CONSTRAINT uq_review_user_product UNIQUE (user_id, product_id) );这类约束天然会创建对应复合唯一索引所以能同时服务“按 user 查评价”和“按 userproduct 精确查”两类查询建索引时不用重复建。4.2 检查约束数据域校验的守门员检查约束是最容易被低估的类型。CHECK能约束字段取值范围还能约束多列之间的关系。比如订单金额必须大于 0或者结束时间必须晚于开始时间CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, starts_at TIMESTAMPTZ NOT NULL, ends_at TIMESTAMPTZ NOT NULL, CONSTRAINT chk_event_time_range CHECK (ends_at starts_at) );CHECK的表达式是在数据库执行引擎里跑的所以不要在表达式里写特别复杂的函数调用那会让每行写入的检查成本变高。表达式尽量用简单比较运算符和基础字段。另外CHECK可以在已有表上立即加但对于大数据量表推荐先NOT VALID后VALIDATE这样可以避免加约束时扫描整张表造成长时间锁表。4.3 排他约束数据库能管的重叠冲突排他约束是 PG 的独门绝技。它比CHECK更强能表达“不允许两行在某个维度上重叠”。最常见的应用是会议室预订、酒店房间订房、时间段排他等场景。基础用法需要btree_gist扩展CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE bookings ( id BIGSERIAL PRIMARY KEY, room_id INT NOT NULL, started_at TIMESTAMPTZ NOT NULL, ended_at TIMESTAMPTZ NOT NULL, EXCLUDE USING gist ( room_id WITH , tstzrange(started_at, ended_at) WITH ) );这个约束的核心原理是GIST 索引把时间段存成区间表示区间重叠。两个预订如果房间相同且时间段重叠插入就会直接失败数据库层面拦截了并发抢订的脏数据。类似的思路还能用在 IP 段分配、GPS 轨迹防重叠等场景。排他约束的代价是索引写入更重所以只在真正需要区间冲突检测的表中使用。4.4 约束的修改、删除与元数据查询约束不是一次性建完就不动的。业务变化后改约束是常态。删除约束用DROP CONSTRAINT必须知道约束名查约束名可以从pg_constraint里翻SELECT conname, contype FROM pg_constraint WHERE conrelid orders::regclass;contype的取值是p主键、u唯一、f外键、c检查、x排他。改约束没有独立的“修改”语法通常就是先删再加。如果担心删了重建期间产生数据问题可以先加新约束再删旧约束业务不断。给约束改名的命令是ALTER TABLE ... RENAME CONSTRAINT ...同样需要原约束名。5. 常见问题排查与避坑实录5.1 违反约束的错误码速查表应用日志里标注违反约束时PostgreSQL 会返回特定 SQLSTATE 码。熟悉这些码能让你少看很多堆栈SQLSTATE常量名含义23502not_null_violation违反非空约束23503foreign_key_violation违反外键约束23505unique_violation违反唯一约束23514check_violation违反检查约束23P01exclusion_violation违反排他约束遇到duplicate key value violates unique constraint这类报错第一反应不是去select全表找数据而是查最近写入的批次是不是有重复逻辑。唯一约束报错极少是数据库问题绝大多数是应用层幂等没做好。5.2 外键删除慢与死锁的根源分析外键删除慢90% 的原因是子表外键列没索引。删除父表记录时PG 为了确认引用关系必须去子表跑一次扫描找到引用行。如果扫描是全表那就像是没索引的淘宝搜索——数据量越大越痛苦。排查方法很直接EXPLAIN ANALYZE DELETE FROM users WHERE id 1;看有没有走idx_orders_user_id的Index Scan如果出现Seq Scan且行数巨大问题一目了然。执行完建索引的命令后再次EXPLAIN对比耗时。死锁方面外键字段在高并发更新下容易出现锁竞争。比如事务 A 插入订单明细要引用用户记录、事务 B 同时更新用户记录主键字段极少见但可能两个事务互相等待对方的锁就会死锁。PG 检测到死锁后会直接回滚其中一个事务应用层需要做好重试。我的经验是别在事务里混着“先更新父表、再操作子表”这种顺序所有事务尽量按相同顺序访问父子表。5.3 大批量数据加约束的两步法给一张已经有几百上千万行的表加外键或检查约束直接ALTER TABLE ADD CONSTRAINT会立刻扫描全表校验所有数据期间表被锁住线上业务直接卡死。PG 提供了一条更平滑的路径先加NOT VALID约束只记录约束定义不校验存量数据再在业务低峰期执行VALIDATE CONSTRAINTALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;NOT VALID加约束的速度是毫秒级的只改系统表VALIDATE CONSTRAINT则会扫描数据校验但只持有SHARE UPDATE EXCLUSIVE锁不阻塞日常读写只是可能和某些 DDL 互斥。这个技巧我几乎每周都在用特别是接手的业务库整体加约束时把校验放在凌晨脚本里跑业务零感知。5.4 导入导出和 ETL 场景下的约束开关差异MySQL 用户切到 PG 最容易踩的坑是搜不到FOREIGN_KEY_CHECKS。PG 没有这个全局变量。PG 里禁用外键触发器的常规方案是删除约束再重建但这种方式对大规模 ETL 不太友好。PG 提供的替代手段是把外键约束改成DEFERRABLE然后在一个大事务里SET CONSTRAINTS ALL DEFERRED或者用超级用户执行ALTER TABLE ... DISABLE TRIGGER ALL这个操作会禁用表上所有触发器外键内部也依赖触发器操作完成后记得ENABLE TRIGGER ALL恢复。如果用pg_dump导出再导入PG 会自动按依赖顺序生成通常不需要手动关约束。手工作业时最稳妥的还是“先删约束导入数据再重建约束”配合第 5.3 节的NOT VALID两步法速度和安全性都能兼顾。5.5 约束命名规范和自查 SQL最后分享一个容易忽略的细节约束命名规范直接影响运维效率。我的惯用格式是主键pk_表名_字段唯一uq_表名_字段外键fk_表名_引用表检查chk_表名_业务含义每个业务模块在交付时我建议附一段自查 SQL把不规范的约束名找出来SELECT n.nspname AS schema_name, c.relname AS table_name, con.conname AS constraint_name, con.contype AS constraint_type FROM pg_constraint con JOIN pg_class c ON c.oid con.conrelid JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname NOT IN (pg_catalog, information_schema) AND con.conname LIKE %$% ORDER BY n.nspname, c.relname;PG 自动化生成的约束名里有时候会带$其实它本身有名字但纯粹为了省事而依靠默认名等到查问题、写迁移脚本时总要先去翻系统表白白消耗注意力。命名这东西看起来是小事但在大团队、长生命周期的项目里它是隐性效率的一部分。我个人在实际操作中的体会是约束不是写论文用的花架子定位是“系统崩溃时最后一道保险”。没有约束的库像是不设防的院子任由应用层的数据随意写入但约束设置过头又会成为业务迭代的绊脚石。真正顺畅的方案是先定业务规则再映射为约束类型最后把命名、索引、校验时机这些细节补全。希望这篇文章能帮你少走一些我已经走过的弯路。