老规矩先说个我常遇到的场景新接手一个 PostgreSQL 项目打开建表脚本所有表都是裸奔的——没有主键、没有外键、唯一性全靠应用层 if else 硬撑。早期数据量小没感觉等用户量上来重复订单、孤儿记录、负数金额全冒出来了。主键、外键与约束的基础配置这件事其实不是“写几行 SQL”那么简单它决定了你的数据库到底能不能在混乱的业务逻辑里守住数据底线。这篇文章我会从约束原理、主键配置、外键配置、常用约束、常见坑、性能取舍这几个角度把我在项目里实际验证过的做法完整写出来。适合刚入门 PostgreSQL 的开发者也适合用了很多年但主要拿它当 MySQL 用的同学。1. 约束其实是一套“数据库自己的业务规则”1.1 约束到底解决什么问题你可以把数据库约束理解成大楼门禁的保安。每次数据要进表约束会先检查它是不是符合规则这条数据的身份证号是不是唯一关联的客户 ID 到底存不存在价格是不是负数如果业务代码漏写了校验或者有同事用了\copy绕过应用直连数据库最后挡住脏数据的只有约束这一道关。我在第一次独立设计表结构时也犯过“约束写不写无所谓”的错后来被一个幽灵 Bug 折磨了三天。最后查出来的原因特别蠢某张订单表里有两个一模一样的订单号业务代码里查LIMIT 1永远返回先插入的那一条另一条成了永远更新不到的“僵尸数据”。这种问题靠应用层很难根治因为每个人的代码风格不同写漏一处就埋雷。而主键约束一旦建立数据库本身就不允许重复值存在。PostgreSQL 把约束作为数据库对象持久化在系统表pg_constraint中可以使用\d 表名快速查看一张表上绑定的所有约束。理解这一点很重要因为约束不光负责“阻止错误数据”它还会影响执行计划、锁行为、以及后续迁移脚本的写法。说白了约束不是可有可无的附注它直接参与数据库的运行。1.2 PostgreSQL 支持的约束类型速览PostgreSQL 原生支持的约束类型比很多人想的多基础配置阶段最常用的是这六种约束类型关键字核心作用主键PRIMARY KEY唯一标识一行不允许空值自动建唯一索引唯一UNIQUE保证列或列组合不重复允许空值并可多次出现非空NOT NULL禁止该列为空检查CHECK按表达式校验列值比如价格必须大于零外键FOREIGN KEY确保子表引用值在父表中真实存在排除EXCLUDE针对范围类型、时间区间等做冲突检测属于进阶用法其中主键、外键是最容易被提及的两个但真正把数据质量托底的往往是 CHECK 和 UNIQUE。我在后面会分别展开讲。配置约束的时间点也很讲究最理想是在建表时一起定义但很多项目是后面发现数据脏了才回头补所以我也会重点讲“在已有表上补约束”的正确姿势。2. 主键配置不能只满足于“加一行”2.1 三种定义主键的写法由你自己选择主键是一个表最重要的约束。它不单单是“唯一标识”还承担着索引定位、外键引用基础、复制和分区时的参照点等职责。在 PostgreSQL 里建表时定义主键有三种常见写法。第一种是字段级约束代码最简短CREATE TABLE users ( id bigint PRIMARY KEY, email text NOT NULL, nickname text );第二种是表级约束适合需要给整个约束显式命名的场景CREATE TABLE orders ( order_id bigint, customer_id bigint, order_no varchar(32) NOT NULL, CONSTRAINT pk_orders PRIMARY KEY (order_id) );第三种写法本质上和第二种一样只是把命名省略直接写PRIMARY KEY (order_id)让数据库自动生成一个形如orders_pkey的约束名。手动命名最大的好处是后续维护时一眼能看懂比如报错信息里出现pk_orders比orders_pkey意义更明确我个人的建议是重要业务表全部显式命名。还有一种更推荐的自增列写法。很多老项目习惯用bigserial做自增主键但现代 PostgreSQL 更推荐 SQL 标准里的 identity 列CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL );区别在于bigserial实际上是创建一个序列并设置默认值因此允许用户手动插入指定 ID 覆盖自增逻辑而GENERATED ALWAYS AS IDENTITY直接禁止手工写入只有显式使用OVERRIDING SYSTEM VALUE才能例外。对于严格保证主键不被人为污染的金融、订单类系统identity 更可靠。2.2 在已存在的表上添加、修改、删除主键现实项目很少给你“完美建表”的机会。最常见的是发现某张表一直没有主键或者原来的主键选错了列现在需要修改。在 PostgreSQL 中给已存在表添加主键很简单ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);但是执行前必须先确认两点。第一这列不能有空值否则ERROR: column id contains null values第二不能有重复值否则会报重复键错误。所以在大表上加主键前我一般会先跑一遍SELECT id, count(*) FROM users GROUP BY id HAVING count(*) 1 LIMIT 5;有脏数据就清理没有再执行ALTER TABLE这样能避免一个需要回滚的长事务。删除主键同样有讲究。如果有人外键引用了这张表直接删除会报错提示其他对象依赖该约束。此时不要顺手加CASCADE草草了事因为CASCADE会连带把子表上的外键约束一起删除影响面可能超过预期。正确的做法是先查看依赖关系决定是保留子表外键重新配置还是确实要把这条引用关系彻底解除。如果需要修改主键列比如从单列改为复合主键要分三步执行先删旧主键再重建唯一索引或新主键最后验证执行计划是否使用了新索引。一旦删除主键数据库会自动删除主键默认创建的索引对应的查询性能会临时下降建议在低峰期操作。2.3 复合主键什么时候该用什么时候别硬用复合主键就是由多列共同组成的主键比如订单明细表用(order_id, line_no)联合定位一行。它的优势是能在不引入额外代理键的情况下保持业务逻辑完整性但代价也很大。第一所有引用这张表的子表外键都必须同时包含这些列导致外键冗长第二复合索引的列顺序直接影响查询性能把等值条件列放前面范围条件列放后面这个顺序选错了索引效果会打折扣。我自己更倾向的做法是对于核心业务实体用单列代理键做主键比如id bigint identity同时用UNIQUE约束守住业务唯一键。比如用户表主键是自增id但邮箱必须唯一那么再加一个唯一约束。这样既方便外键关联也保证了业务上不允许重复的列不会产生脏数据。复合主键只用在真正的“关系事实表”里比如订单行项目、权限映射这类没有独立业务实体的表。3. 外键配置关联关系不是画在 ER 图上的是要写进数据库的3.1 外键语法与引用动作真的都弄懂了吗外键就是数据库层面的引用完整性约束。它的作用一句话保证子表中每一行的外键列要么是空值要么真实存在于父表的主键或唯一键中。如果没有外键应用层删除了一个客户订单表里残留的customer_id就会让报表 join 出半张空表。建表时添加外键的常见写法如下CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE );重点是ON DELETE和ON UPDATE后面的动作这是很多新手会忽略的设计点。PostgreSQL 支持五种动作动作行为NO ACTION默认行为删除/更新父表时会检查子表若有引用则报错可延迟检查RESTRICT立即阻止删除/更新与 NO ACTION 类似但检查时机更早CASCADE父表删除时级联删除子表引用行父表更新时级联更新子表外键值SET NULL父表删除/更新时子表外键列被置为空该列必须允许 NULLSET DEFAULT父表删除/更新时子表外键列被设为默认值该列必须有默认值NO ACTION和RESTRICT的区别在 PostgreSQL 里比较微妙。RESTRICT 会立即阻止操作而 NO ACTION 如果约束是可延迟的DEFERRABLE INITIALLY DEFERRED可以推迟到事务提交前才检查。默认情况下二者几乎一样但在复杂事务里NO ACTION 配合延迟约束可以解决“先删父表、后清子表”的顺序问题。CASCADE是最容易引起误会的动作。它虽然省事但可能造成连锁删除把一个百万行的父表删除操作扩散到十几张子表。我在生产环境只会在明确需要级联清理的配置表中使用业务核心表基本不用。3.2 在大表上添加外键NOT VALID 是最实用的技巧如果你的应用已经上线现在发现忘了加外键直接执行ALTER TABLE会锁住整张表并扫描所有数据校验线上业务可能会被打爆。PostgreSQL 提供了一个很好的折中方案先以NOT VALID状态添加约束之后在业务低峰期再校验存量数据ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID; ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;NOT VALID的意思是新增或修改的行会立刻受外键约束但已有的历史数据不会在添加约束时被扫描校验。等系统不忙了执行VALIDATE CONSTRAINT时同样会扫描全表但锁需求更小不会直接阻塞写入。这个技巧我几乎每次给生产环境补约束都会用踩过的坑是有人忘了执行第二步VALIDATE导致约束一直处于“只信任新数据”的状态报表里偶尔还是会冒出关联不上的旧数据。3.3 外键列一定要建索引不然删父表就是灾难PostgreSQL 官方文档和很多老 DBA 都会强调外键列上不会自动创建索引。很多人建外键时只盯语法却忽略了子表外键列的索引于是父表删除一行或者更新主键时数据库要在子表里做一次全表扫描来确认引用关系。数据量小没事一旦子表几千万元素删除父表一行的操作可能把数据库拖到超时。所以我的建表习惯是每个外键列都对应一个索引如果业务查询的模式固定就直接建复合索引把外键列和其他常用过滤条件放在一起。例如订单表经常按customer_id status查询就建(customer_id, status)复合索引既能支撑外键检查又能加速业务查询。另外多个外键列组合引用父表复合主键时同样需要按引用顺序创建一个包含全部外键列的索引。外键还有一个比较隐蔽的问题批量导入大数据量时如果子表数据先导入父表数据后导入可能会在约束检查上消耗不少时间。合理的做法是父表数据先入库子表再导入让外键检查尽量命中内存中的父表索引减少随机磁盘读取。4. 其他约束UNIQUE、CHECK、NOT NULL 的配合技巧4.1 UNIQUE 约束和唯一索引别搞混也不要错过UNIQUE 约束和唯一索引经常被混着说其实两者高度相关PostgreSQL 里建立一个 UNIQUE 约束会自动创建一个相同列的唯一索引。之所以你既能看到unique constraint又看到index是因为约束本质上依赖索引来快速判断重复值。常见的“坑”出现在 NULL 值的处理上。标准 PostgreSQL 中NULL 被视为未知值多个 NULL 不会互相冲突所以一张表可以插入多行email NULL数据。这在很多业务里是期望行为但如果你希望“该列只允许一个 NULL”就需要借助 PostgreSQL 15 新增的UNIQUE NULLS NOT DISTINCTALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE NULLS NOT DISTINCT (email);要是用的还是 15 以下的老版本可以用部分唯一索引模拟同样的效果CREATE UNIQUE INDEX uq_users_email_notnull ON users(email) WHERE email IS NOT NULL;这个写法会把所有“正常值”的重复问题拦死但不影响 NULL 的存储。唯一约束还有一个容易忽略的点它需要能利用好现有索引。如果一张表上原来就有功能相同的普通索引添加 UNIQUE 约束时会额外创建一个新索引造成重复存储和写入开销建议先删掉旧索引让约束接管唯一性检查。4.2 CHECK 约束轻量级的数据质量守门员CHECK 约束允许你用任意布尔表达式校验值是主键外键之外最灵活也最实用的约束。它适合做范围检查、格式检查、多列之间的逻辑关系检查。比如商品价格CREATE TABLE products ( id bigint PRIMARY KEY, price numeric(10,2) NOT NULL, sale_price numeric(10,2) NOT NULL, CONSTRAINT chk_products_price_nonnegative CHECK (price 0), CONSTRAINT chk_products_sale_not_higher_than_price CHECK (sale_price price) );第二个约束就很有意思它保证了促销价不可能高于原价这种逻辑写在应用层需要你记得每次更新都校验写进数据库里则无论谁改数据都无法绕过去。注意到没有CHECK 表达式里可以用多个列但不能引用其他表也不能调用非 immutable 的函数。所谓 immutable 函数就是同样的输入永远得到同样输出比如now()不可以直接用在 CHECK 里因为时间会变。正则表达式可以例如校验手机号格式CHECK (phone ~ ^1[3-9][0-9]{9}$)。但我不建议在 CHAR/VARCHAR 上放过于复杂的格式正则因为正则匹配和业务规则强绑定后期格式调整反而要迁移数据。和主键外键一样CHECK 也支持NOT VALID加速在线添加但实际业务中使用频率不如外键高。因为 CHECK 一般很轻量扫描一遍也很快除非表特别大或者表达式特别重。4.3 NOT NULL 和默认值最容易被忽视的两个“伪约束”NOT NULL 在 PostgreSQL 里被归为列的属性很多文档会把它单拎出来但它本质上就是一个限定“空值不可出现”的约束。使用上没什么玄学直接把NOT NULL写在列定义里即可。如果你想要一个带条件的非空校验比如“当类型是个人用户时身份证号必填”可以用 CHECK 来处理但大部分场景还是直接用 NOT NULL。默认值DEFAULT不是约束但和约束经常协同工作。比如订单表创建时间一般这样写created_at timestamptz NOT NULL DEFAULT now()这行定义同时做了三件事设置默认值禁止空值让缺失字段的应用层代码也能得到合理的当前时间。养成“能默认就默认 不能空就 NOT NULL”的好习惯能省掉很多后续数据清洗工作。有一个容易踩的坑是给已有表添加 NOT NULL 约束时如果表里有 NULL 行会直接报错。此时需要先把 NULL 改成可靠的值再执行ALTER COLUMN SET NOT NULL。对大表来说这个操作会短暂锁表建议同样放在低峰期执行。5. 常见问题与排查技巧实录5.1 删除主键报错先搞清楚是哪一种数据库的报错很多从 Oracle 转到 PostgreSQL 的同事会带着过去的口头禅来排查问题。比如热词里出现的“oracle删除主键时报错ora-03113”这个ORA-03113实际上是 Oracle 客户端与数据库进程通信中断的经典错误常见原因是网络闪断、服务进程崩溃或监听异常和“删除主键”本身没有直接关系。如果真在 PostgreSQL 环境里看到这个错误码首先要怀疑是不是有人把连接串指向了 Oracle或者应用中间件在捣乱。PostgreSQL 里删除主键更常见的是下面这个错误ERROR: cannot drop constraint pk_orders because other objects depend on it DETAIL: constraint fk_orders_customer on table order_items depends on index pk_orders这代表有子表通过外键引用该主键。不要条件反射用CASCADE我建议先执行SELECT conrelid::regclass AS table_name, conname AS constraint_name FROM pg_constraint WHERE confrelid orders::regclass;把依赖该表的约束全部列出来再判断需要保留、删除或重建哪些关系。这种“先看依赖、再动手”的习惯能救很多次生产事故。5.2 外键导致的死锁与批量导入顺序问题外键约束自身的检查逻辑会触发对父表的锁定这在高并发写入时容易引发死锁。比较典型的场景是并发事务 A 插入订单同时依赖客户表事务 B 也在插入客户和订单如果两个事务都以不同顺序操作同一批数据就可能互相等待对方释放行锁。更常见的低配版问题出在批量导入。如果你用INSERT子表数据后再补父表数据外键检查会非常吃力因为子表引用到的父表行还没有存在要么被约束拒绝要么需要额外处理。我实践下来的最佳顺序是先导入父表数据再导入子表数据确实需要先子后父的就把外键约束设为可延迟在事务提交前统一检查ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED;这样在同一个事务里先插入订单再插入对应客户也可以成功因为约束检查推迟到了COMMIT时。但注意别滥用延迟外键会让错误在事务提交时才暴露回滚成本更大。5.3 临时禁用约束和触发器小心别把自己关在门外数据清洗或者大批量迁移时新手常想“能不能先关掉约束”。在 PostgreSQL 里约束和触发器有很深的绑定关系尤其是外键和 CHECK 约束内部都是由系统触发器实现的。因此ALTER TABLE ... DISABLE TRIGGER ALL这条看起来只能禁用触发器的命令实际上也会禁用一部分约束检查具体表现可能让人莫名其妙。我很少建议直接禁用约束。如果实在需要绕过外键做数据修复可以优先考虑使用session_replication_role replica这个会话级设置。它会禁用当前会话里所有非用户自定义触发器和约束的默认行为但需要超级用户权限而且只影响当前会话。执行后数据层面的外键完整性不再被校验所以只能在非常确定的修复场景下使用结束前一定要把角色改回origin。另一个诡异的问题是禁用触发器并不能禁用主键唯一性检查因为主键是通过唯一索引实现的和触发器不是一个机制。如果你发现删了重复值时主键还在报错请不要怀疑是触发器没关干净先检查是不是唯一索引本身存在或约束名捣乱。5.4 在 Navicat 里配置唯一约束图形化不等于可以不动脑不少同学习惯用 Navicat 管理 PostgreSQL遇到“怎么设置唯一约束”时会点开字段属性窗口乱勾一顿。其实在 Navicat 中你可以先打开表设计器切到“索引”标签页点击添加索引把索引类型选为Unique保存后 PostgreSQL 会自动生成对应的 UNIQUE 约束。如果只是想快速给列加唯一限制直接在设计表中该字段的属性里勾选“唯一”效果也一样。但我更推荐用 SQL 脚本完成这个操作因为图形化工具很容易让你忽略约束命名和索引可读性。比如在 Navicat 查询窗口里写ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);这样在执行记录、版本迁移、问题排查时你能精确控制每一步变化。图形化界面的价值是快速查看不是取代对 SQL 语义的理解。6. 约束配置的全局视角别为了外键而外键6.1 外键与性能什么时候可以舍弃外键前面说了很多外键的好话但外键不是银弹。在高写入量的数据仓库、日志流水表、或者定期清理的分区表中外键约束的额外校验成本往往大于收益。ETL 过程可以通过先清洗数据、再入库的方式保证完整性此时外键更像是给性能上了枷锁。另一个更具体的限制是分区表。PostgreSQL 中分区表上的主键和唯一索引必须包含分区键否则无法保证全局唯一性。这就导致你很难在分区表上定义一个不含分区键但业务唯一的主键。外键引用同样受限制引用列上必须有匹配的唯一索引所以分区表作为父表时引用约束设计务必提前规划否则后期加不了外键只能靠应用层保证。所以我的判断标准是这样的在线交易系统OLTP优先全量使用外键数据仓库、分析库、日志系统可以只做主键和检查约束把外键完整性交给 ETL 管道同时凡是不确定要不要外键的地方先用NOT VALID加上再观察一段时间反正它可以随时校验也不会拦新数据。6.2 约束与索引协同不要让主键成为“一次性投资”主键约束会自动创建唯一索引这当然是好事。但复合主键的索引设计需要额外留意。假设主键是(order_id, line_no)那么数据库会为这个顺序建复合索引WHERE order_id 1能走索引WHERE line_no 2通常就不能高效使用了。如果业务有大量按line_no单独过滤的需求就需要额外建一个第二列的索引。外键列索引我在前面提过这里再强调一次不是在建外键时自动生成必须在设计阶段就规划好。很多 DBA 会写一个自动巡检脚本去查所有外键列是否都有对应索引。这个思路值得借鉴。唯一约束的索引同理它会隐式帮查询提速但多个唯一约束叠加在同一张表写入时的检查成本也会上升。这些成本不是坏事可你得心里有数而不是等数据库卡了再抱怨“约束拖慢性能”。6.3 一次合理的约束配置长什么样最后分享一个我在订单系统中常用的建表片段作为你配置约束时的参考模板CREATE TABLE customers ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_no varchar(32) NOT NULL, email varchar(128) NOT NULL, phone varchar(20), created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT uq_customers_no UNIQUE (customer_no), CONSTRAINT uq_customers_email UNIQUE (email) ); CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_no varchar(32) NOT NULL, customer_id bigint NOT NULL, total_amount numeric(12,2) NOT NULL, status varchar(16) NOT NULL, shipped_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), CONSTRAINT uq_orders_no UNIQUE (order_no), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT chk_orders_total_amount_nonnegative CHECK (total_amount 0), CONSTRAINT chk_orders_status_valid CHECK (status IN (NEW, PAID, SHIPPED, CLOSED)) ); CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);这个片段里主键、唯一约束、外键、CHECK、NOT NULL、默认值都覆盖到了。外键没加级联删除是因为订单属于核心交易数据客户被误删时宁可报错也不让订单悄无声息消失。订单表上还手动建了一个兼容外键检查的复合索引(customer_id, status)业务上也常用这个组合查订单。这些决策不是随手的而是结合业务容错、查询路径、运维成本折中的结果。我个人这些年最大的体会是约束配置做得早后面省的事比你想象得多。每次加约束都要考虑锁表、校验、数据清洗所以新建表时多花一分钟写清楚约束比上线后熬夜补数据干净太多。另一个小技巧是在迁移脚本里把每个约束的添加拆成独立事务这样某个约束因为数据问题失败时不会影响前面已经成功的约束。最后约束命名规范也建议统一比如主键用pk_表名外键用fk_子表_父表唯一用uq_表名_列名检查用chk_表名_描述这样团队协作时看错误信息就能直击问题不用再逐条查系统表。