企业官网建设流程全解析

前一阵子帮朋友排查一个库存对不上账的问题，查到最后发现订单明细表里有相当一批 order_id 根本找不到对应的订单记录。业务系统跑了好几年，接口层校验明明一直都有，但就是有些历史数据绕过了所有检查。这个场景我见过不止一次了，它让我一直坚持一个观点：PostgreSQL 里的主键、外键和各种约束，从来不是数据库给你添麻烦的功能，而是最后一层谁都不能轻易绕过的数据底线。这篇文章想把这些基础配置一次讲透，包括主键的索引行为、外键的校验机制，以及给老表补约束时真正该注意的事。

1. 为什么约束要放在数据库这一层：业务校验兜不住的场景

很多开发者在刚接触数据库时都产生过类似困惑：既然是主键、外键和约束，那我在程序里写逻辑判断不就行了？字段是否为空，我在接口里校验；关联是否存在，我在 Service 层里查询确认；邮箱格式对不对，我用正则过滤。看起来每一步都覆盖到了，数据库约束似乎只是重复劳动。

但实际线上系统往往不是一个人维护、一个入口写入的。一个商城订单系统，可能有前台用户下单、运营后台手工补单、定时任务批量导入、数据修复脚本直接跑 SQL，还可能对接过老系统的迁移数据。这些入口的服务端校验逻辑未必一致，甚至同一套代码在不同迭代版本里对字段的约束口径都已经变了。我见过最典型的情况是换了个技术负责人，新来的同事觉得“用户备注选填”没必要校验，于是某个入口悄悄放开了，三个月后报表统计时才发现数据已经烂了一片。

约束放在数据库这一层，核心价值在于它是一个强制开关，不依赖任何一段代码逻辑。应用可以换语言、换框架、换团队，但只要表结构和约束定义在，脏数据就会被死死挡在外面。这也是数据库行业常说的“数据完整性”的一部分，完整性不是某个接口的附加功能，而是表结构本身的契约。

1.1 多个写入入口与历史包袱是约束失效的温床

数据库约束还有一个容易被低估的作用：守护历史数据和新代码之间的边界。系统运行时间越长，各种临时脚本、手工修数、爬虫回填就越多。这些操作通常没有经过业务 Service 层，直接连数据库执行 UPDATE 或 INSERT，如果表上没有约束，谁也无法保证写入的数据符合业务规则。

举一个我实际遇到过的例子。某个财务对账表需要引用“付款单”和“报销单”两张表，当初设计时外键确实建了，但因为一次大版本迁移把部分表重建时漏掉了外键定义，只保留了主键。结果消息队列重放旧事件时，对账表写入了一大批引用不存在的付款单记录。后来排查时发现，如果外键还在，这批数据连插入都会被直接拒绝，重放任务会立即报错，问题早就能暴露，不至于跨了三个月的账。

所以我的建议很明确：新表设计时就把约束建好，存量表也要尽快补齐。把约束当成“最后一道防线”，不是“额外负担”，是处理数据问题的最省钱的策略。

1.2 一张表看懂六类基础约束的分工

PostgreSQL 相比其他数据库，约束体系算是很完整的。通过一个表就能快速看清各类约束的位置和作用：

主键、外键、唯一约束、检查约束和非空约束是日常使用频率最高的五类，排除约束通常用在会议室预订、时间排期等场景，用来处理“时间段不能重叠”这类问题，原理上要基于索引做冲突检测，基础不太熟的人可以先放一放。

2. 主键：唯一且非空的背后，是索引与存储行为的选择

主键大概是每个接触数据库的人第一个学会的概念，但很多人的认知停留在“这一列不能重复、不能为空”这个层面。实际上主键在 PostgreSQL 里的行为有两个值得认真理解的部分，第一是约束本身，第二是它默认创建的唯一索引。

2.1 主键约束的本质是两件事叠加

主键约束从语义上说，等价于“非空约束 + 唯一约束”的组合。不过它和普通的唯一约束有一点不太一样：主键约束隐含了“这一列是行的唯一身份”这层业务含义，所以一个表只能定义一个主键，而不限制你建多少唯一约束。

当你在 PostgreSQL 里创建主键时，如果该列上还没有合适的索引，数据库会自动创建一个唯一 B-tree 索引。这个索引一方面用来加速唯一性的检查，另一方面也直接参与后续的查询优化。换句话说，主键不只是规则，它还定义了表上最核心的访问路径。

举一个很简单但实用的例子：

CREATE TABLE t_user ( user_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, user_name text NOT NULL );

这里user_id本身就是主键，所以后续执行WHERE user_id = 123时会直接走主键索引。如果业务查询经常用user_name过滤，那就再单独在user_name上建一个索引，这属于查询优化层面的事。

2.2 主键声明方式的语法差异

主键可以在列定义里直接写，也可以在表定义最后单独写。两者的效果一致，但可读性和扩展性不同。

CREATE TABLE t_order ( order_id bigint PRIMARY KEY, order_no text UNIQUE, user_id bigint NOT NULL ); CREATE TABLE t_order_detail ( detail_id bigint, order_id bigint, sku_code text, CONSTRAINT t_order_detail_pkey PRIMARY KEY (detail_id) );

当主键涉及多列时，必须用第二种写法，也就是“表级约束”：

CREATE TABLE t_order_line ( order_id bigint NOT NULL, line_no int NOT NULL, sku_code text NOT NULL, quantity numeric(12,2) NOT NULL, CONSTRAINT t_order_line_pk PRIMARY KEY (order_id, line_no) );

用 CONSTRAINT 显式命名是一个值得养成的好习惯。如果不在 DDL 里写名称，PostgreSQL 会自动生成类似t_order_line_pkey这样的名字。虽然不影响使用，但后面做迁移或者排查约束冲突时，有意义的名称会让你一眼就明白约束是干嘛的。

2.3 复合主键要能少用就少用

复合主键听起来很合理，比如订单行表用“订单号 + 行号”做联合主键。但很多项目在实战中会因此吃亏，原因在于主键往往会被其他表引用，而复合主键意味着外键也要引用多个列，建外键时麻烦，查询时连接条件也冗长。

更常见的问题出在数据量大的场景。如果一张明细表使用业务字段作为复合主键，而这些业务字段本身会变，比如用“订单号 + SKU 编码”，某天 SKU 编码在商品中心发生合并调整，哪怕只改了一个 SKU 编码，涉及的数据维护成本就很高。

我个人的倾向是：能用一个自增 ID 或 UUID 作为单列主键，就尽量不用复合主键。业务上需要“订单号 + 行号”唯一，完全可以通过单独的唯一约束来实现，没必要和主键绑定。主键只负责稳定地标识一行，业务唯一性交给唯一约束，这两件事分开反而更清晰。

3. 外键：关联完整性怎么生效，以及级联、索引那些细节

外键是最能体现关系型数据库“关系”二字的约束。它解决的核心问题很简单：一张表里的某个字段，必须是指定目标表里真实存在的数据。否则就会出现前面说的那种“订单明细里挂着不存在的订单”的孤儿数据。

3.1 外键的引用目标不一定非得是主键

很多人有个误解，认为外键只能引用另一张表的主键。PostgreSQL 的规则其实更宽松：外键可以引用目标表上任意一组被唯一约束或主键约束覆盖的列。

CREATE TABLE t_user ( user_id bigint PRIMARY KEY, login_name text NOT NULL UNIQUE ); CREATE TABLE t_login_log ( log_id bigint PRIMARY KEY, login_name text REFERENCES t_user(login_name) );

这个例子里的外键引用的是t_user的唯一列login_name，而不是主键user_id。这种写法在特定场景下是有用的，但需要注意，被引用的列上必须存在唯一约束或主键约束，否则外键直接报错。

日常设计里，为了简单一致，还是建议优先引用主键。唯一列引用会让外键和业务耦合得更深，一旦唯一列的业务含义调整，比如允许登录名变更为邮箱，那整条引用链都得跟着动。

3.2 PostgreSQL 是怎么校验外键的

外键的校验不是定义一个规则摆在那里就完了，它会在每次写操作时实时发生。插入或更新子表数据时，PostgreSQL 会检查被引用的列是否在父表中存在。这个过程本质是一次索引查找，所以父表被引用的列几乎总是需要有索引，主键或唯一索引正好满足这个要求。

同样，删除或更新父表数据时，数据库也要看子表里有没有引用。这就是外键列在子表端也需要索引的原因。PostgreSQL 不会自动为外键列创建索引，这是很多性能问题的根源。

举一个非常常见的慢查询例子：

CREATE TABLE t_order ( order_id bigint PRIMARY KEY ); CREATE TABLE t_order_detail ( detail_id bigint PRIMARY KEY, order_id bigint REFERENCES t_order(order_id) );

如果t_order_detail表有数百万行，删除某个历史订单时，数据库需要在t_order_detail.order_id上查找是否有该订单的明细。没有索引就是全表扫描，删一个订单可能卡好几秒，业务直接阻塞。

所以外键字段几乎应该无条件建索引，除非你非常明确这个外键列永远不会被当作过滤条件，且父表删除频率极低。

另外，PostgreSQL 对外键有一套内部的触发机制。也就是说，每个外键约束背后都有内部触发器在工作。默认情况下外键检查是立即执行的，也就是每条语句结束时检查是否符合外键约束。如果你声明了DEFERRABLE，则可以推迟到事务提交时统一检查，这在批量导入、环形引用场景下很有用。

CREATE TABLE t_a ( id bigint PRIMARY KEY, b_id bigint REFERENCES t_b(id) DEFERRABLE INITIALLY DEFERRED );

使用DEFERRABLE INITIALLY DEFERRED后，外键检查会推迟到事务提交。比如先插入子表，再插入父表，在同一事务里就不会报错。这个特性在数据迁移、环形关联的场景中减少了不少痛苦，但要注意它对并发和锁行为有影响，不建议所有外键都默认设为可延迟。

3.3 级联操作的行为选择

外键定义里最常引战的，是ON DELETE CASCADE和ON DELETE SET NULL。两者的语义很直接：

PostgreSQL 的默认值是NO ACTION，这和RESTRICT在非延迟场景下几乎是等价的。区别在于 RESTRICT 永远立即阻止，而 NO ACTION 如果声明为 DEFERRABLE，可以在事务提交时才检查。

CASCADE使用起来痛快，但风险也大。删除一个父表记录，可能悄无声息地删掉几万行子表数据，而且这个过程是数据库内部完成的，不走业务代码，应用层不会感知。恢复成本极高。我的建议是：对于核心业务表，尽量不用 CASCADE；即使要用，也要评估清楚删除链路涉及多少张子表，并配合备份策略。

SET NULL则更适合“保留历史痕迹但切断关联”的场景。比如员工离职后，工单表保留工单记录，但把处理人字段置空，避免历史数据被误删。

3.4 给外键加索引：不是可选项，是必选项

这里值得再强调一次，因为实际踩坑的人太多了。外键列索引不是“优化建议”，而是生产环境里的必选项。

判断外键列要不要建索引，最简单的经验是：如果这张子表经常作为“多”的一方被查询，或者父表有删除、更新操作，那外键列就该有索引。反过来想，外键存在的作用就是让两张表产生关联，而关联查询最常见的路径就是从子表反查父表，或者从父表下钻到子表，这条路径上的索引是基本盘。

如果前期忘了给外键列建索引，通常会在哪一天突然出现一个“删除订单导致数据库 CPU 飙高”的报警。到那时再补索引，虽然也能救火，但不如建表时一步到位。

4. 唯一约束、检查约束、非空约束：三个“常规武器”的边界

主键和外键是大家最熟悉的，但完整的数据完整性还要靠另外三个约束来补位。它们单独看都很简单，细节里却藏着不少门道。

4.1 唯一约束：默认允许多个 NULL 存在

唯一约束确保某一列或一组列的组合不能重复。但很多人在刚开始接触时都会踩一个认知坑：唯一约束不限制 NULL 的重复。

CREATE TABLE t_coupon ( coupon_id bigint PRIMARY KEY, claim_user_id bigint UNIQUE );

上面这个表里，claim_user_id被声明为唯一，但如果你插入多行claim_user_id IS NULL的数据，PostgreSQL 是允许的。原因在于 SQL 标准里 NULL 表示“未知”，两个未知值并不被认为相等，所以多个 NULL 不会触发唯一冲突。

如果你的业务语义是“一个用户只能领取一张优惠券”，那空闲用户也应该是唯一的空值，此时可以在 PostgreSQL 15 及以上版本使用UNIQUE NULLS NOT DISTINCT：

CREATE TABLE t_coupon ( coupon_id bigint PRIMARY KEY, claim_user_id bigint UNIQUE NULLS NOT DISTINCT );

这样写之后，claim_user_id里只能出现一个 NULL，进一步保证了逻辑上的唯一性。

唯一约束在 PostgreSQL 内部会创建一个唯一索引。也就是说，声明UNIQUE相当于自动建索引，这跟主键行为一致。所以如果你发现某列经常用于等值查询，而且业务上本来就要求不重复，使用唯一约束可以一举两得。

4.2 检查约束：把业务规则写进表结构

检查约束是 PostgreSQL 里最灵活的约束类型，它可以写任何返回布尔值的表达式。最常见的用法是校验字段取值范围、格式化字符串、字段间逻辑关系等。

CREATE TABLE t_product ( product_id bigint PRIMARY KEY, price numeric(12,2) NOT NULL CHECK (price >= 0), stock int NOT NULL CHECK (stock >= 0), status text NOT NULL CHECK (status IN ('draft', 'online', 'offline')) );

检查约束可以在列定义里内联，也可以作为表级约束。表级约束最大的优点是能跨列判断：

CREATE TABLE t_activity ( activity_id bigint PRIMARY KEY, start_at timestamptz NOT NULL, end_at timestamptz NOT NULL, CONSTRAINT t_activity_time_check CHECK (end_at > start_at) );

这个约束保证了结束时间必须晚于开始时间，即使前端表单漏校验、后端接口改坏了、甚至运营手工改库，数据库都会拦住不合理数据。

有一点要注意：检查约束只在插入或更新时生效，已经存在于表中的旧数据不会被自动扫描。这个特性也是后面讲“存量表补约束”时的重要基础。

4.3 非空约束：不是独立对象，却比检查约束更常被误改

非空约束在 pg_constraint 系统表里并不会单独出现，它本质上是列定义里的一个属性。但它对表结构的影响并不小。

在 PostgreSQL 里给已存在的列增加非空约束，用的是：

ALTER TABLE t_user ALTER COLUMN user_name SET NOT NULL;

这条语句执行时，数据库会扫描全表检查是否存在 NULL 值。如果表很大，且user_name列上没有索引，扫描成本会很高，期间还会获取表级锁。所以生产环境操作前，务必先自查几个 NULL：

SELECT count(*) FROM t_user WHERE user_name IS NULL;

如果查出任何 NULL，必须先将其处理成有效值，否则 ALTER 语句直接报错。

有人可能想到用检查约束代替非空约束，比如写成CHECK (user_name IS NOT NULL)，这样可以在加约束时配合 NOT VALID 分阶段执行，但大多数场景直接用SET NOT NULL就够了，语义更清晰。

5. 给存量表补约束的实操路线：ALTER TABLE、锁表与两阶段校验

前面聊的都是原理，这一节进入实战。给一张已经有大量数据的表补约束，和新建表时顺手写上约束，完全是两码事。最大的风险点是锁表时间和校验失败。

5.1 直接 ADD CONSTRAINT 的锁表成本

以添加外键约束为例，如果直接执行：

ALTER TABLE t_order_detail ADD CONSTRAINT t_order_detail_order_id_fkey FOREIGN KEY (order_id) REFERENCES t_order(order_id);

PostgreSQL 会获取一个较高等级的锁，然后扫描全表校验每一行的order_id是否都在t_order中存在。表越大，扫描时间越长，期间任何针对这张表的写操作都会被阻塞。线上大表如果直接这么干，很容易引发连接堆积和业务超时报警。

检查约束同理，直接添加时会带着校验一起跑。但 PostgreSQL 提供了一个非常实用的特性：先加约束但不校验，再分阶段校验。

5.2 两阶段 NOT VALID 与 VALIDATE

PostgreSQL 允许你在添加约束时声明NOT VALID，意思是“约束从现在开始对后续写入生效，但暂时不校验存量数据”。

ALTER TABLE t_order_detail ADD CONSTRAINT t_order_detail_order_id_fkey FOREIGN KEY (order_id) REFERENCES t_order(order_id) NOT VALID;

这一步非常快，因为它不扫描全表，只是把约束元数据加上。之后存量数据继续维护，等选择一个业务低谷期，再执行：

ALTER TABLE t_order_detail VALIDATE CONSTRAINT t_order_detail_order_id_fkey;

校验阶段会扫描表，但不会长时间阻塞所有操作，锁级别相对温柔，可以让应用继续读写。两阶段方案特别适合大表补外键和检查约束。

不过要注意：主键和唯一约束不支持 NOT VALID，因为它们需要立刻构建唯一索引来保证唯一性，这个动作没法推迟。所以在存量表上补主键，依然要评估好索引构建的时间和锁影响。

5.3 校验前的数据清理顺序

给存量表补约束，先别急着执行 DDL，而是先把违规数据查出来处理掉。这是很多人在生产环境栽跟头的地方。

查重复数据的标准姿势：

SELECT order_id, count(*) FROM t_order_detail GROUP BY order_id HAVING count(*) > 1;

查外键孤儿数据：

SELECT d.order_id FROM t_order_detail d LEFT JOIN t_order o ON o.order_id = d.order_id WHERE o.order_id IS NULL;

查出结果后，是删除、补录还是置空，要根据业务决定。但一定要在低峰期操作，并且先备份。清理干净后，再执行两阶段 DDL，成功的概率才会高。

一个很常见的细节坑是：用NOT VALID添加外键后，新写入的数据依然会被约束检查。也就是说，从执行完成那一刻开始，脏数据就只能被挡在外面，但存量问题还需要专门 VALIDATE 才能暴露。这个“先防新增、后台清存量”的思路，在多个团队协作时尤其好用。

6. 用系统表盘点约束资产：几条能直接抄走的 SQL

最后分享几条我常用的约束排查 SQL。PostgreSQL 把约束元数据都放在pg_constraint系统表里，配合pg_attribute和pg_index可以快速掌握整个库的约束全景。

6.1 查看某张表上的全部约束

SELECT conname, contype, pg_get_constraintdef(oid) AS constraint_definition FROM pg_constraint WHERE conrelid = 'public.t_order_detail'::regclass ORDER BY contype;

contype字段的含义很直白：p是主键，u是唯一约束，f是外键，c是检查约束，x是排除约束。pg_get_constraintdef可以直接把约束的定义还原成 DDL 语句，排查时非常方便。

6.2 找出所有尚未校验的约束

SELECT conrelid::regclass AS table_name, conname, contype FROM pg_constraint WHERE NOT convalidated;

这条 SQL 会列出所有因为用 NOT VALID 添加而尚未完成存量校验的约束。看到这样的约束，说明约束已在使用但存量数据还没验证，务必要安排时间 VALIDATE。

6.3 找出所有未建索引的外键列

这是我最常用的一条 SQL。外键列缺索引是生产环境隐性的性能炸弹，通过系统表可以一次性扫出来：

SELECT tc.conrelid::regclass AS child_table, tc.conname AS fk_name, pg_get_constraintdef(tc.oid) AS fk_definition, ( SELECT string_agg(att.attname, ', ' ORDER BY k.ord) FROM unnest(tc.conkey) WITH ORDINALITY AS k(attnum, ord) JOIN pg_attribute att ON att.attrelid = tc.conrelid AND att.attnum = k.attnum ) AS fk_columns FROM pg_constraint tc WHERE tc.contype = 'f' AND NOT EXISTS ( SELECT 1 FROM pg_index i WHERE i.indrelid = tc.conrelid AND i.indkey::smallint[] @> tc.conkey ) ORDER BY 1;

执行之后，把返回的每一条都加上对应索引。比如返回的是t_order_detail(order_id)，那就：

CREATE INDEX idx_t_order_detail_order_id ON t_order_detail(order_id);

加了索引之后，父表删除和子表反查的性能都会有明显改善。索引名建议包含表名和列名，方便后续维护。

6.4 临时禁用约束的注意事项

最后提一个很多人会问的操作：能不能先 DROP 约束再重建？

ALTER TABLE t_order_detail DROP CONSTRAINT t_order_detail_order_id_fkey;

能，但我不建议在生产环境用这个方式做批量导入。约束一旦被删掉，后续写入的数据就完全失去了校验，一旦混入脏数据，再重建约束时会面临两难：不校验会留下隐患，校验可能要全表扫。正常导入大数据，优先使用前面提到的DEFERRABLE或在事务内分批处理，而不是删约束。

我个人在实际操作里的体会是：约束配置这件事，看起来是建表时写几行 DDL，但真正考验人的地方永远在存量数据和锁时间的平衡上。建好约束之后，日常要留意的不是约束本身，而是那些因为早期疏漏一直没补上的空档。最后再分享一个小技巧：每季度跑一次上面的系统表 SQL，把新增的表和约束资产过一遍，很多潜在故障根本等不到爆发就会被提前发现。