# 有目的地选择约束
> 用可靠的约束建模数据，为外键建立索引，衡量每项约束的代价；在约束真正有价值的地方保留它，在不值得的地方移除它。
---
LLMS 索引： [llms.txt](/llms.txt)
---
在我们的运行示例中，删除一个项目大约需要 100 毫秒。单看这个数字还好，直到你看清它做了什么：为了从另一张表中删除一行，它读取了 `events` 的全部 35,173 页，也就是 275 MB。一个缺失的索引造成了这一切。
约束不是文书工作。每一项约束都是数据库对每个写入者永远执行的保证，包括某人在凌晨 2 点手工运行的脚本。应用验证只是建议，约束才是事实。
不过，事实也有代价，外键（foreign key）会在每次写入时收取这笔费用。所以先把话说在前面：我们并不特别喜欢外键。数据就是业务本身的地方，我们会保留外键；写入像水流一样涌来的地方，我们会移除外键。本章会测量其中的原因。
## 每个事实只存一处 {#store-each-fact-once}
规范化（normalization）只是同一条规则换了几种名字：每个事实只存放在一个地方。一个事实存了两份，迟早会和自己不一致，只要写入者足够多就一定如此。把每个账户的 `plan` 复制到它的 20 个项目上，然后通过其中一行项目升级账户，账户就会同时处于两种方案。在规范化的模式中，方案只存在 `accounts` 的一行里。
> [!DETAILS]- 深入阅读：按每种范式阻止的 bug 认识范式
>
> | 范式 | 直白的规则 | 它阻止的 bug |
> | ---- | ---------------------------------------- | ------------------------------------ |
> | 1NF | 每列一个值，不打包列表 | 无法约束或编辑单个项 |
> | 2NF | 列依赖整个键 | 半个键的事实被复制到多行 |
> | 3NF | 列依赖键，而不是彼此依赖 | 父项的事实被复制到子项 |
> | BCNF | 决定某列的因素必须是一个键 | 列之间一条没人检查的规则 |
>
>
>
> **Table 14-1.** 每个事实只存一处
>
> 把 `plan` 复制到项目上会破坏 3NF，这是最常见的违反形式；它还会让新账户的方案无处安放，直到这个账户拥有一个项目。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select account_id as acct from projects_flat
> > where account_plan <> 'team'
> > group by account_id having count(*) >= 2
> > order by account_id limit 1 \gset
> > update projects_flat set account_plan = 'team'
> > where id = (select min(id) from projects_flat where account_id = :acct);
> > select lab.prove('updating one project row leaves its account on two plans at once',
> > (select count(distinct account_plan) from projects_flat where account_id = :acct) = 2);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
### 规范化到难以忍受，再测量 {#normalize-until-it-hurts-then-measure}
一次连接要付出几页的代价；一次复制则可能带来几个月后才发现的 bug。只有测量结果说明应该复制时才复制：汇总值（[预计算你要展示的内容](/37-scale/#precompute-what-you-show)）、冻结在某个时刻的事实（例如发票行的价格），以及租户 ID（[让租户无处不在](/13-keys/#carry-the-tenant-everywhere)）。
### 每份副本都必须只有一个写入者 {#a-copy-needs-exactly-one-writer}
每份副本都必须有唯一的维护者，负责让它与原始事实保持相等：如果它是键的一部分，就用约束；如果它是派生值，就用一个触发器或一个任务。永远不要依赖“每个调用方都记得”：有人第一次从 psql 写入，这份副本就会漂移。`events.account_id` 复制了 `project_id` 所隐含的账户，而复合外键（composite foreign key）能证明这份副本始终相等。其他外键都移除后，`events` 保留的就是这一项外键（[在孤儿记录会成为客户可见 bug 的地方保留外键](/14-relationships/#keep-foreign-keys-where-an-orphan-is-a-bug-customers-see)）。
> [!DETAILS]- 深入阅读：复合外键让副本可证明地相等
>
> 整个修复只需要两条语句：
>
> ```sql
> alter table projects add constraint projects_id_account_uq unique (id, account_id);
> alter table events add constraint events_project_account_fk
> foreign key (project_id, account_id)
> references projects (id, account_id) not valid;
> ```
>
> 这个唯一约束看起来多余，因为单独的 `id` 已经唯一；但外键引用的列必须与某个唯一约束覆盖的列恰好一致。`not valid` 会跳过现有的两百万行，只检查之后的新行；稍后可以在不阻塞写入的情况下验证它们（[先添加 NOT VALID 外键，再验证](/26-migrations/#add-foreign-keys-not-valid-then-validate)）。现在，把事件移动到另一个账户，或让它指向另一个账户的项目，都会在试图执行的那一行失败。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > begin;
> > select lab.prove('without a constraint, an event can be moved to another account',
> > lab.try($$ update events set account_id = account_id + 1 where id = 1 $$) = 'ok');
> > rollback;
> > alter table projects add constraint projects_id_account_uq unique (id, account_id);
> > alter table events add constraint events_project_account_fk
> > foreign key (project_id, account_id) references projects (id, account_id) not valid;
> > select lab.prove('with the composite foreign key, the same update is rejected',
> > lab.try($$ update events set account_id = account_id + 1 where id = 1 $$) like '23503:%');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 外键要从两个方向检查，代价各不相同 {#a-foreign-key-is-checked-both-ways-at-different-prices}
`projects.account_id bigint not null references accounts (id)` 是一个**外键**：如果账户不存在，Postgres 会拒绝这个项目；如果删除账户会留下项目孤儿，Postgres 也会拒绝删除。这个约束会从两个方向检查，但代价完全不同。
**插入子行**时，数据库会查找父表的主键，读取几页，并在父行上取得一个 `FOR KEY SHARE` [锁](/43-glossary/#lock-modes)，一直持有到提交。**删除父行**时，数据库会执行 `select 1 from events where project_id = $1`；没有任何机制要求你为 `events.project_id` 建索引。没有索引时，每删除一个父行，就要完整扫描一次子表。
> [!WARNING] Postgres 不会替你给外键列建立索引
>
> 也要检查 ORM 生成的内容。那些从 MySQL 迁移来的模式会缺少这个索引，因为 MySQL 会静默创建它（[外键不会自动创建索引](/11-not-mysql/#foreign-keys-dont-index-themselves)）。
### 删除每个父行都会扫描整张子表 {#each-deleted-parent-scans-the-whole-child-table}
运行示例没有为外键列建立索引。在一个我们会回滚的事务中删除一个项目：
```sql
begin;
explain (analyze, costs off) delete from projects where id = 20001;
rollback;
```
```text
Delete on projects (actual time=0.067..0.068 rows=0.00 loops=1)
Buffers: shared hit=10
-> Index Scan using projects_pkey on projects ...
...
Trigger for constraint events_project_id_fkey: time=98.824 calls=1
Execution Time: 98.964 ms
```
把它读两遍。这个计划非常好：索引扫描只用了 10 个缓冲区。然后看触发器那一行：外键其实是内部触发器，而这个触发器几乎占用了全部 99 毫秒。`pg_stat_xact_user_tables` 显示它做了什么：顺序扫描全部 2,000,000 行事件数据，以确认没有任何一行引用这个项目。
> [!EXAMPLE]- 动手验证 · lab `13-relationships`
>
> ```sql
> begin;
> select seq as s0, tup as t0 from events_scans() \gset
> select fk_ms(format('delete from projects where id = %s', :scratch_id)) as one_ms \gset
> select lab.prove('deleting one project sequentially scans events once, reading all 2,000,000 rows',
> (select seq - :s0 = 1 and tup - :t0 = 2000000 from events_scans()));
> rollback;
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
一条语句删除 50 个项目时，触发器行会显示 `time=4214.089 calls=50`：每个项目都完整扫描一次 `events`。代价随着*子表*增长，而子表正是增长最快的那张表。
### 一个索引把 35,173 页变成 3 页 {#one-index-turns-35173-pages-into-3}
```sql
create index events_project_id_idx on events (project_id);
```
这是一个 14 MB 的索引。现在检查只读取 3 页，而不是 35,173 页；删除 50 个项目时，触发器耗时从 4,214 毫秒降到约 1 毫秒。除非外键引用的是永远不会删除的行，否则不为外键列建索引就是一个错误。
> [!DETAILS]- 深入阅读：观察索引前后的触发器查询
>
> `auto_explain` 配合 `log_nested_statements = on`（再加上 `log_analyze`、`log_buffers` 和 `log_level = notice`）会记录触发器自己的查询：
>
> ```text
> Query Text: SELECT 1 FROM ONLY "public"."events" x
> WHERE $1 OPERATOR(pg_catalog.=) "project_id" FOR KEY SHARE OF x
> LockRows ... (actual time=107.395..107.397 rows=0.00 loops=1)
> Buffers: shared hit=2142 read=33031
> -> Seq Scan on events x ...
> Rows Removed by Filter: 2000000
> ```
>
> 全部 35,173 页。建立索引后，查询会执行 `Bitmap Index Scan on events_project_id_idx`，只读取 3 页。删除 50 个项目时，没有索引约读取 1,760,000 页，有索引约读取 150 页。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > begin;
> > select seq as s0, tup as t0 from events_scans() \gset
> > select fk_ms($$ delete from projects where name like 'scratch %' $$) as fifty_ms \gset
> > select lab.prove('deleting 50 projects scans events 50 times: 100,000,000 rows read',
> > (select seq - :s0 = 50 and tup - :t0 = 100000000 from events_scans()));
> > rollback;
> > create index events_project_id_idx on events (project_id);
> > begin;
> > select seq as s0, tup as t0, idx as i0 from events_scans() \gset
> > select fk_ms($$ delete from projects where name like 'scratch %' $$) as fifty_idx_ms \gset
> > select lab.prove('with the index, the 50 checks scan events by index: no sequential scans',
> > (select seq - :s0 = 0 and idx - :i0 >= 50 from events_scans()));
> > rollback;
> > select lab.prove('and the trigger time for 50 deletes drops by more than 100 times',
> > :fifty_ms > 100 * greatest(:fifty_idx_ms, 0.001));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
### 一条系统目录查询列出未建索引的外键 {#one-catalog-query-lists-the-unindexed-foreign-keys}
你不必手工寻找它们。每次迁移和每创建一张新表后，都运行下面的查询：
```sql
select c.conrelid::regclass as table_name, pg_get_constraintdef(c.oid) as definition
from pg_constraint c
where c.contype = 'f'
and not exists (
select 1 from pg_index i
where i.indrelid = c.conrelid and i.indpred is null
and (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey)
order by 1, 2;
```
在当前加载的运行示例中，在建立上面的索引之前，它会列出五项：`account_id` 和 `owner_id` 在 `projects` 上，以及 `events` 上的三项外键。
> [!DETAILS]- 深入阅读：只有索引以键开头，键才算被覆盖
>
> 这个查询会列出其列没有出现在某个非部分索引前导位置的每个外键。`indkey` 保存索引列的编号，从位置 0 开始；切片会取出与键列数相同的前导列，而 `@>` 会询问这些列是否包含键的每一列，顺序可以不同。`(a, b)` 上的键由 `(b, a, c)` 上的索引覆盖，却不会由 `(c, a, b)` 上的索引覆盖：删除时的查找需要先出现键列。部分索引不算，因为检查查询可能不满足它的谓词。
>
> `users.account_id` 没有出现在列表中，因为 `unique (account_id, email)` 背后的索引以它为前导列。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('the query lists five unindexed foreign keys on projects and events',
> > (select count(*) from unindexed_fks) = 5
> > and (select count(*) from unindexed_fks where table_name::text in ('projects', 'events')) = 5);
> > select lab.prove('users.account_id is not listed: unique (account_id, email) leads with it',
> > not exists (select 1 from unindexed_fks where table_name = 'users'::regclass));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 决定删除父行时如何处理子行 {#decide-what-a-parents-delete-does-to-its-children}
默认的 `no action` 会拒绝删除仍有子行的父行。`cascade` 会连同子行一起删除；`set null` 会清空子行的列（从 PostgreSQL 15 开始，只清空你点名的列，这一点对复合租户键很重要）。
### 谨慎使用级联 {#use-cascade-deliberately}
当子行没有父行就没有意义时，`on delete cascade` 是合适的：例如成员关系和设置行。在大型表上它很危险：通过级联删除一个大型账户，会在一个事务中删除数百万行，对每行加锁并记录日志。级联只是替你执行它所替代的 `delete` 语句；它只是把工作藏起来。对于大型子表，应由任务分批删除。
## 外键并非免费，也不总是你的朋友 {#foreign-keys-arent-free-and-arent-always-your-friend}
下面是大多数模式建议跳过的一半事实。每个外键都会在每次向子表写入时执行一次查找、锁住一行父表，并通过你将经历的每次迁移、加载和拆分把两张表绑在一起。我们逐项测量过；正是这些测量结果，让我们只在数据本身就是业务、写入速度接近人们点击速度的地方保留外键。
### 外键能消除应用无法避免的竞态 {#a-foreign-key-closes-a-race-the-app-cant}
它的优点确实存在。外键的锁会让父行的删除等待子行插入，从而消除应用检查无法避免的竞态。它会在传入错误 ID 的那条语句上捕获错误，而不是几周后等报告发现。它还会为规划器提供信息：在复合外键存在时，`events` 与 `projects` 按两列连接，估算结果是正确的 2,000,000 行；没有外键时则是 1,999 行。
> [!DETAILS]- 深入阅读：估算从何而来，以及不存在的连接消除
>
> 没有外键时，规划器会把两个条件的选择率当作相互独立，然后将它们相乘。低估一千倍，就会让一个嵌套循环最终处理两百万行。你可能听到的一个优点并不成立：仅凭父表上的唯一索引，Postgres 就会删除一个未使用的左连接，无论有没有外键；但它从不会因为有外键而删除内连接。（PostgreSQL 18 的自连接消除可以移除通过唯一键连接到自身的一张表；它同样不需要外键。）
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > \set twocol 'select * from events e join projects p on p.id = e.project_id and p.account_id = e.account_id'
> > select lab.est_rows(:'twocol') as est_with_fk \gset
> > alter table events drop constraint events_project_account_fk;
> > select lab.est_rows(:'twocol') as est_without_fk \gset
> > select lab.prove('with the composite foreign key, the planner expects about 2,000,000 joined rows',
> > :est_with_fk between 1900000 and 2100000);
> > select lab.prove('without it, the planner multiplies two selectivities and expects about 2,000',
> > :est_without_fk between 1000 and 4000);
> > select lab.prove('an unused left join is removed by the unique index alone; no foreign key needed',
> > lab.est_nodes($$ select e.id from ev_nofk e left join projects p on p.id = e.project_id $$)
> > = array['Seq Scan']);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
### 每次插入都要为每项检查付费 {#the-insert-pays-for-every-check}
我们向表的两份副本各插入了 100,000 行，一份没有外键，另一份带有 `events` 的三个外键：
| 插入 100,000 行 | 无外键 | 三个外键 |
| ------------------- | --------------- | --------------- |
| 我们机器上的耗时 | 约 100 毫秒 | 约 1,000 毫秒 |
| 触碰的缓冲区 | 305,467 | 2,103,290 |
| WAL | 22 MB | 35 MB |
**Table 14-2.** 每次插入都要为每项检查付费
在我们的机器上，耗时大约是十倍（耗时会变化，所以实验只检查是否超过四倍）。[WAL](/43-glossary/#wal) 才是这里的意外之处：`FOR KEY SHARE` 锁存在父行的元组头中，所以获取它会产生日志；每个不同的父行只记录一次。向 `events` 插入数据也会写入 `users`。
> [!EXAMPLE]- 动手验证 · lab `13-relationships`
>
> ```sql
> select lab.prove('with its three foreign keys, the same 100,000-row insert takes over 4 times as long',
> :ins_fk_ms > 4 * :ins_nofk_ms);
> select lab.prove('the checks read over 12 extra buffers per inserted row, all parent lookups',
> (:u2 - :u1) - (:u1 - :u0) > 12 * 100000);
> select lab.prove('and add one WAL record per distinct parent row: the FOR KEY SHARE lock on it',
> (:r2 - :r1) - (:r1 - :r0) between :parents * 0.95 and :parents * 1.1);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
公平地说，要看清它在哪些地方造成影响。16 个客户端每次只提交一个事件时，磁盘把两张表的吞吐量都限制在每秒 2,300 到 2,800 次插入。设置 `synchronous_commit = off` 后，外键会让吞吐量降低三分之一：没有外键时每秒 88,000 到 99,000 次，有外键时每秒 60,000 到 62,000 次。它们会拖慢写入行的场景：流式写入、批量加载和回填。
> [!DETAILS]- 深入阅读：额外的 13 MB WAL 从何而来
>
> 每行的检查大约多触碰 18 个缓冲区，即每个外键多 6 个。插入额外写入了 40,698 条 WAL 记录，每个被锁定的父行对应一条（1,000 个账户、19,849 个项目、19,849 个用户）；这些锁让几乎每个父页都变脏，所以检查点之后的第一次变更会记录整页（[检查点后的第一次变化会记录整页](/08-wal/#the-first-change-after-a-checkpoint-logs-the-whole-page)）：1,330 个页面镜像，这构成了额外 13 MB 的大部分。
### 一个热点父行会制造多事务 ID {#one-hot-parent-makes-multixacts}
一行的元组头可以容纳一个加锁者；第二个加锁者到来时，Postgres 会换成一个**多事务 ID（MultiXact）**，也就是一份加锁者列表；每个新的加锁者又会创建一份新列表（[共享行锁会制造多事务 ID，多事务 ID 也会回卷](/25-concurrency/#shared-row-locks-make-multixacts-and-multixacts-wrap-too)）。16 个客户端为同一个项目插入事件 10 秒，会产生大约 400,000 个多事务 ID；如果分散到随机父行上，则大约只有 500 个。
按这个速度，多事务 ID 的冻结限制（也就是 Postgres 强制清理整张表时的多事务 ID 年龄）会在不到三小时后到达，而最大客户的行会决定你的防[回卷](/43-glossary/#wraparound)清理计划。
> [!DETAILS]- 深入阅读：同一项目上的十个未提交插入，逐个看成员
>
> 针对项目 7 的十个未提交插入，在它的行上留下了十个 `FOR KEY SHARE` 成员；为了达到这个状态，它创建了九个多事务 ID，共保存 54 个成员：存储量会随同一行上并发数的平方增长。完整运行产生了 389,000 到 441,000 个多事务 ID，以及大约一百万个成员。删除这个项目时，必须等待每个加锁者。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('ten open inserts for one project leave ten FOR KEY SHARE members on its row',
> > (select count(*) = 10 and bool_and(mode = 'keysh')
> > from pg_get_multixact_members((select xmax from projects where id = 7))));
> > select lab.prove('getting there took nine multixacts, one per new locker, storing 54 members in all',
> > (select count(distinct mx::text) from hot_seen where n >= 2) = 9
> > and (select sum((select count(*) from pg_get_multixact_members(mx)))
> > from hot_seen where n >= 2) = 54);
> > select lab.prove('a delete of the project waits for every open child insert',
> > lab.try($$ set local lock_timeout = '200ms'; delete from projects where id = 7 $$) like '55P03:%');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
### 添加一个外键会锁住两张表 {#adding-one-locks-both-tables}
`alter table ... add foreign key` 会在子表和*父表*上取得 `SHARE ROW EXCLUSIVE` 锁，即使使用 [`not valid`](/43-glossary/#not-valid) 也是如此：锁持有期间，新项目会等待。可以把过程拆成 `not valid` 和 `validate` 两步，并在 `lock_timeout` 下运行（[先添加 NOT VALID 外键，再验证](/26-migrations/#add-foreign-keys-not-valid-then-validate)）。但它仍然是一项迁移：每添加一张子表，都要触碰父表。
### 外键止步于数据库边界 {#a-foreign-key-stops-at-the-database-boundary}
外键不能引用另一数据库或服务；在分片之间也只有共置的表才能使用外键（[约束必须包含分布列](/40-sharding/#constraints-must-include-the-distribution-column)）。任何跨越你终有一天要拆开的边界的外键，都会在压力下被移除。批量加载本来就会移除外键（[外键：NOT VALID，然后 VALIDATE](/27-loading/#foreign-keys-not-valid-then-validate)），这等于承认：加载期间，是别的机制在保持数据正确。
### 在孤儿记录会成为客户可见 bug 的地方保留外键 {#keep-foreign-keys-where-an-orphan-is-a-bug-customers-see}
在账户、用户、项目、成员关系和金钱上**保留**外键：那里出现孤儿记录，就是客户能看到的 bug。在事件、日志、指标、审计轨迹、跨边界引用和导入数据上**移除外键，或声明为 `not enforced`**。在运行示例中，`events` 之所以还保留外键，只是因为演示需要它们。
要谨慎选择关键字。`not valid` 仍会检查每一行新数据。PostgreSQL 18 的 `not enforced` 只是文档声明：`\d` 和图表工具仍会显示它，但不会运行触发器，孤儿记录可以进入，规划器也会忽略它。之后用 `alter constraint ... enforced` 可以重新启用它；启用时会检查所有现有行，并在遇到第一个已经进入的孤儿时失败，因此先清理它们。
```sql
project_id bigint not null references projects (id) not enforced
```
即使在写入洪流上，也有一项外键要保留：复合租户键，即 `(project_id, account_id)` 引用 `projects (id, account_id)`。保留它不是为了整洁，而是因为它构成安全边界，使跨租户引用不可能发生（[让跨租户引用不可能](/13-keys/#make-cross-tenant-references-impossible)）；几分钟后才运行的孤儿检查无法挽回一次数据泄露。
它并非没有代价，而且应该把这一点说清楚。它引用 `projects`，所以要承担前面在这个父表上测量到的一切代价：每行一次查找；每个事务中每个不同项目一次写入日志的 `FOR KEY SHARE` 锁；以及许多写入者集中到一个繁忙项目上时产生的多事务 ID。最大客户的项目行，正是这些代价落下的地方。我们仍然付出这笔费用，因为泄露无法挽回；要监控 `projects` 上的多事务 ID 年龄，别让账单出人意料。
> [!DETAILS]- 深入阅读：证明：两张表都被锁定、不能跨数据库建键，以及 not enforced
>
> 每项结论都是这样检查的。关于锁：在一个添加 `not valid` 外键的未提交事务中，`pg_locks` 会显示 `ShareRowExclusiveLock` 同时存在于子表和 `projects` 上；另一个通过 `dblink` 打开的会话尝试插入项目时，会在 200 毫秒的 `lock_timeout` 后放弃。关于边界：`postgres_fdw` 外部表指向另一个数据库中的 `projects`，但不能被引用，视图也不能；两者都会以 `42809` 失败。关于关键字：一份子表副本把它的键声明为 `not enforced`，另一份声明为 `not valid`，然后分别向它们提供同一个孤儿。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > begin;
> > alter table ev_nofk add constraint ev_nofk_project_fk
> > foreign key (project_id) references projects (id) not valid;
> > select lab.prove('adding a foreign key, even not valid, takes ShareRowExclusive on the child and the parent',
> > (select array_agg(c.relname::text order by c.relname) from pg_locks l
> > join pg_class c on c.oid = l.relation
> > where l.pid = pg_backend_pid() and l.mode = 'ShareRowExclusiveLock')
> > = array['ev_nofk', 'projects']);
> > select lab.prove('so a new project waits until the migration commits',
> > lab.try($$ select dblink_exec('w1', 'set lock_timeout = ''200ms''; '
> > 'insert into projects (account_id, owner_id, name) values (1, 1, ''new'')') $$)
> > like '%lock timeout%');
> > rollback;
> > select lab.prove('a foreign key cannot reference a table in another database',
> > lab.try($$ create table t_remote (project_id bigint references remote_projects (id)) $$)
> > = '42809: referenced relation "remote_projects" is not a table');
> >
> > select lab.prove('a not enforced foreign key (PostgreSQL 18) creates no triggers',
> > (select count(*) from pg_trigger where tgrelid = 'ev_doc'::regclass) = 0
> > and (select not conenforced from pg_constraint where conrelid = 'ev_doc'::regclass and contype = 'f'));
> > select lab.prove('and accepts an orphan',
> > lab.try($$ insert into ev_doc values (1, 999999) $$) = 'ok');
> > select lab.prove('while a not valid one still checks every new row',
> > lab.try($$ insert into ev_nv values (1, 999999) $$) like '23503:%');
> > select lab.prove('a not enforced foreign key gives the planner nothing: back to about 2,000',
> > :est_not_enforced = :est_without_fk);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
### 检查你不再强制执行的规则 {#check-what-you-no-longer-enforce}
删除约束只是转移了工作，并没有消灭它。**一条写入路径**会检查它为授权请求而读取过的父行。**一个定时运行的孤儿检查**会从高水位标记（high-water mark）开始，把事件表左连接到每个父表，每隔几分钟检查一次；每晚再做完整检查，发现行时通知值班人员。完整检查读取了 36,500 页；只检查最新 10,000 行时读取了 1,534 页。ID 并不是按提交顺序发放的（[让数据库为行编号，永远不要相信顺序](/13-keys/#let-the-database-number-rows-and-never-trust-the-order)），所以要让标记落后于最新 ID 几分钟，并让每晚的完整运行捕获任何漏网之鱼。
这就是取舍：永远在每次写入时执行一次查找并加一把锁，或者按计划运行一条查询。数据是业务本身的地方，我们支付查找的代价；写入洪流上，我们运行查询。
> [!DETAILS]- 深入阅读：孤儿检查只读数据，因此副本可以运行它
>
> ```sql
> select e.id, e.account_id, e.project_id, e.user_id
> from events e
> left join accounts a on a.id = e.account_id
> left join projects p on p.id = e.project_id
> left join users u on u.id = e.user_id
> where e.id > $1 -- the last id the previous run checked
> and (a.id is null or p.id is null or (e.user_id is not null and u.id is null));
> ```
>
> 只有在本次运行干净结束后，才推进标记；而且只能推进到几分钟前的某个 ID。因为它从不写入，所以副本可以运行它。我们植入两个孤儿后，它恰好返回了这两个。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('the orphan check finds exactly the two planted orphans',
> > (select count(*) from ev_nofk e
> > left join accounts a on a.id = e.account_id
> > left join projects p on p.id = e.project_id
> > left join users u on u.id = e.user_id
> > where e.id > 0
> > and (a.id is null or p.id is null or (e.user_id is not null and u.id is null))) = 2);
> > select lab.prove('checking all 2,000,000 events reads the table once: about 36,000 pages',
> > :full_pages between 35000 and 38000);
> > select lab.prove('checking only rows past the last high-water mark reads under half of that',
> > :recent_pages < :full_pages / 2);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 一对一拆分要付出一次连接代价，先让它值得 {#a-one-to-one-split-costs-a-join-so-make-it-earn-one}
一对一关系会把外键设为子表的主键，例如 `account_billing (account_id bigint primary key references accounts (id))`。只有在一组列作为整体可选、列很大且很少读取、写入频率不同，或权限不同的情况下，才这样拆分表。每次拆分都要付出一次连接。
## 连接表需要在两个方向上都有索引 {#a-join-table-needs-an-index-in-each-direction}
用户和项目通过**连接表（join table）**相遇：
```sql
create table project_members (
project_id bigint not null references projects (id) on delete cascade,
user_id bigint not null references users (id) on delete cascade,
primary key (project_id, user_id)
);
create index project_members_user_id_idx on project_members (user_id);
```
主键防止重复，并服务于“这个项目的成员”查询。第二个索引服务于“这个用户参与的项目”查询，也服务于删除用户时的检查；主键无法胜任这两项用途，因为 `user_id` 是它的第二列。
## 替换多态关联 {#replace-polymorphic-associations}
ORM 中多态关联（polymorphic association）的 `commentable_type` 加 `commentable_id` 模式无法建立外键，因此没有任何东西能阻止一条评论指向不存在的项目。为每种父对象使用一个可空外键（`project_id`、`event_id`），再加上 `check (num_nonnulls(project_id, event_id) = 1)`。每个引用都真实有效；要支持新的父对象类型，就新增一列。
## CHECK 约束陈述一条任何代码路径都不能跳过的规则 {#a-check-states-a-rule-no-code-path-can-skip}
让每一列都使用 `not null`，除非你能说清楚 `null` 对它意味着什么。除此之外，`check` 会陈述一条关于单行数据的规则，例如 `check (status <> 'failed' or failure_reason is not null)`；任何代码路径都不能跳过它。CHECK 不能看到其他行（[跨行规则对约束不可见](/09-acid/#rules-across-rows-are-invisible-to-constraints)），并且表达式为 null 时也会通过，因此要把 CHECK 与 `not null` 配对使用。
### 默认情况下 NULL 彼此不同 {#nulls-are-distinct-by-default}
`null` 永远不等于 `null`，因此 `unique` 约束允许其约束列中包含 null 的行有任意多条。当这不是你想要的行为时，写成 `unique nulls not distinct`（PostgreSQL 15+）。
## 排他约束让重叠变得不可能 {#an-exclusion-constraint-makes-overlaps-impossible}
**排他约束（exclusion constraint）**是对 unique 的泛化：“不允许两行满足这组运算符的组合”。同一时间只能有一个部署持有某个项目的锁：
```sql
create extension if not exists btree_gist;
create table deploy_locks (
project_id bigint not null references projects (id),
held_during tstzrange not null,
exclude using gist (project_id with =, held_during with &&)
);
```
同一项目的时间范围发生重叠，就会被拒绝。应用代码无法安全完成这件事，因为“先检查是否重叠，再插入”会产生竞争。PostgreSQL 18 的 `primary key (project_id, valid_during without overlaps)` 是时态键的简写，并能阻止“同时生效的两个方案”。
> [!DETAILS]- 深入阅读：时态键，以及两种拒绝
>
> 实验构建的、用于阻止“同时生效的两个方案”的时态键如下：
>
> ```sql
> create table project_plans (
> project_id bigint not null references projects (id),
> plan text not null,
> valid_during tstzrange not null,
> primary key (project_id, valid_during without overlaps)
> );
> ```
>
> 它在底层与 `deploy_locks` 使用同一种 GiST 排他约束；因此错误会把主键称为排他约束，也因此需要 `btree_gist` 来支持 `bigint` 列。它还会拒绝空范围，这一点对本章末尾的历史触发器很重要。两张表中都已经有项目 42 的一行；向任一表插入重叠范围都会以 `23P01` 失败。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('an overlapping range for the same project is rejected',
> > lab.try($$ insert into deploy_locks values (42, tstzrange('2026-10-01 10:30+00', '2026-10-01 10:45+00')) $$)
> > like '23P01: conflicting key value violates exclusion constraint "deploy_locks_project_id_held_during_excl"');
> > select lab.prove('without overlaps rejects a second plan in effect at the same time',
> > lab.try($$ insert into project_plans values (42, 'team', tstzrange('2026-05-01+00', null)) $$)
> > like '23P01: conflicting key value violates exclusion constraint "project_plans_pkey"');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 可延迟约束（deferrable constraint）让交换完成 {#deferrable-constraints-let-a-swap-finish}
普通 unique 约束会逐行检查，所以在一次 `update` 中交换两个步骤的位置，会在中途失败。把它声明为 `deferrable`，就会在语句结束时或提交时检查（[约束按语句触发，或在提交时触发](/09-acid/#constraints-fire-per-statement-or-at-commit)）。代价是：外键不能引用它，upsert 也不能以它为目标。
> [!DETAILS]- 深入阅读：交换，以及 deferrable 放弃了什么
>
> 假设表中有两行，位置分别是 1 和 2，交换操作如下：
>
> ```sql
> create table steps (
> id bigint primary key,
> pipeline_id bigint not null,
> position int not null,
> unique (pipeline_id, position) deferrable initially immediate
> );
> update steps set position = case position when 1 then 2 else 1 end;
> ```
>
> 没有 `deferrable` 时，同一个 `update` 会在它先移动的那一行上以 `23505` 失败。`initially immediate` 仍会在每条语句结束时检查，所以大多数代码看不出区别；`set constraints ... deferred` 会把检查推迟到提交时，供需要执行多条语句的事务使用。它会放弃两点：外键不能引用它（`55000`），`on conflict` 也不能把它用作仲裁器。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('deferrable initially immediate checks at end of statement, so the swap works',
> > lab.try($$ update steps set position = case position when 1 then 2 else 1 end $$) = 'ok');
> > select lab.prove('a deferrable unique constraint cannot be referenced by a foreign key',
> > lab.try($$ create table steps_ref (pipeline_id bigint, position int,
> > foreign key (pipeline_id, position) references steps (pipeline_id, position)) $$)
> > = '55000: cannot use a deferrable unique constraint for referenced table "steps"');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 一个 NULL 就会让 NOT IN 什么也返回不了 {#one-null-makes-not-in-return-nothing}
`null` 表示未知，而 `x not in (a, null)` 的含义是 `x <> a and x <> null`，这永远不可能为真。“项目 42 中没有事件的用户”原本统计出 99,999；直到某个事件的 `user_id` 变成 null，结果就变成了 0，而且没有任何错误。`not exists` 仍然返回 99,999。始终使用 `not exists`（[NOT IN 是陷阱，请使用 NOT EXISTS](/22-queries/#not-in-is-a-trap-use-not-exists)）。
> [!EXAMPLE]- 动手验证 · lab `13-relationships`
>
> ```sql
> begin;
> update events set user_id = null
> where id = (select min(id) from events where project_id = 42);
> select lab.prove('one null user_id makes not in return 0 users',
> (select count(*) from users u
> where u.id not in (select e.user_id from events e where e.project_id = 42)) = 0);
> select lab.prove('not exists still returns 99,999',
> (select count(*) from users u
> where not exists (select 1 from events e
> where e.project_id = 42 and e.user_id = u.id)) = 99999);
> rollback;
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 软删除需要部分唯一索引 {#a-soft-delete-needs-a-partial-unique-index}
**软删除**会设置 `deleted_at timestamptz` 列，而不是删除行；这会破坏唯一约束：已删除的“Project 1”仍然会阻止创建新的同名项目。**部分唯一索引（partial unique index）**只在存活行之间强制唯一性：
```sql
create unique index projects_name_live_uq
on projects (account_id, lower(name)) where deleted_at is null;
```
之后每个查询都必须过滤 `deleted_at is null`（可以用视图或行级安全策略集中完成），并且应该由任务清理已删除很久的行。
## 历史记录也是你要设计的表 {#history-is-a-table-you-design}
一次 `update` 会替换整行，查询看不到旧版本。“这个项目在三月时叫什么？”需要你有意保留历史记录：
| | 审计行 | 触发器历史 | 时态表 |
| --- | --- | --- | --- |
| 捕获每个写入者 | 否 | 是，`replica` 模式除外 | 是 |
| 知道谁以及为什么 | 是 | 只有应用传入的内容 | 只有你添加列时才知道 |
| “某一时刻”查询 | 重放 | 合并两张表 | 一个范围谓词 |
**Table 14-3.** 历史记录也是你要设计的表
**审计行**由应用在同一事务中写入，因此知道原因，但只覆盖那些记得这样做的写入者（[从服务器日志开始审计，免费](/33-security/#start-auditing-with-the-server-log-its-free)）。像 `project_plans` 这样的**时态表（temporal table）**会把每个版本作为带范围的一行保存；当历史本身就是产品时使用它（例如价格、方案、权益）。
### 历史触发器捕获每个写入者，每行插入一次 {#a-history-trigger-catches-every-writer-at-an-insert-per-row}
触发器能捕获每个写入者：每次更新和删除时，它都会把旧版本复制到 `projects_history`，并带上 `valid_during` 范围，用 `without overlaps` 键约束它。每次批量更新都会为每行变更付出一次插入代价（[行触发器每行大约需要一微秒](/24-functions/#a-row-trigger-costs-about-a-microsecond-a-row)）；但它捕获不到在 `replica` 模式下运行的写入者，逻辑复制就是如此（[COPY 会触发触发器，复制角色不会](/24-functions/#copy-fires-triggers-replication-roles-dont)）。
> [!DETAILS]- 深入阅读：历史触发器，以及其中两个关键细节
>
> ```sql
> alter table projects add column valid_from timestamptz not null default now();
>
> create function projects_keep_history() returns trigger
> language plpgsql as $$
> begin
> if tg_op = 'UPDATE' and old is not distinct from new then
> return new; -- nothing changed: keep no history
> end if;
> if old.valid_from < now() then -- skip versions born in this transaction
> insert into projects_history
> values (old.id, old.account_id, old.owner_id, old.name, old.archived_at,
> old.deleted_at, tstzrange(old.valid_from, now()),
> nullif(current_setting('app.user_id', true), '')::bigint);
> end if;
> if tg_op = 'DELETE' then
> return old;
> end if;
> new.valid_from := now();
> return new;
> end $$;
>
> create trigger projects_keep_history
> before update or delete on projects
> for each row execute function projects_keep_history();
> ```
>
> `now()` 是事务开始的时间，因此同一事务中的两次更新会产生 `tstzrange(t, t)`，它是 `empty`；`without overlaps` 键会拒绝它。这个中间版本从未对外可见，因此触发器会跳过它。应用通过 `set local app.user_id` 传入用户；使用 `local` 是为了避免这个值泄露给连接池中下一个借用者（[连接池会泄露租户信息](/33-security/#pooled-connections-leak-tenants)）。
>
> 还要知道一个边界情况：`now()` 是每个事务*开始*的时间，而不是它写入的时间。如果一个较早开始的事务，在一个较晚开始的事务已经提交了对某行的更改之后才更新该行，那么 `old.valid_from` 会晚于这个事务的 `now()`。此时触发器会跳过该版本的历史记录，并让 `valid_from` 向过去移动。如果并发写入下的历史必须精确，应改用 `clock_timestamp()` 打时间戳，并接受某个版本可能只持续几微秒。
>
> > [!EXAMPLE]- 动手验证 · lab `13-relationships`
> >
> > ```sql
> > select lab.prove('a range from a moment to itself is empty',
> > tstzrange(now(), now()) = 'empty'::tstzrange);
> > -- after two earlier single-update transactions left two history rows:
> > begin;
> > set local app.user_id = '7';
> > update projects set name = 'Checkout v3' where id = 43;
> > update projects set name = 'Checkout v4' where id = 43;
> > commit;
> > select lab.prove('two updates in one transaction add one history row, stamped with the user',
> > (select count(*) from projects_history where project_id = 43) = 3
> > and (select changed_by from projects_history where project_id = 43
> > order by lower(valid_during) desc limit 1) = 7);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/13-relationships.sql)
## 本章要点 {#the-short-version}
- 每个事实只存一处；每份有意保留的副本都只交给一个写入者。
- 为引用侧的每个外键列建立索引。没有索引时，删除一个项目会扫描 `events` 的全部 35,173 页。
- 外键会让每次写入付出代价：带三个外键插入 100,000 行时，耗时是无外键时的数倍（我们机器上是十倍）；每个父行对应一条写入日志的锁，热点父行会产生多事务 ID，添加外键还会锁住两张表。
- 在核心业务实体上保留外键。在写入洪流、跨边界引用和导入数据上移除外键，或声明为 `not enforced`，并定时运行孤儿检查。复合租户键到处保留：它是安全边界，值得付出对 `projects` 的查找和加锁代价。
- 级联只用于少量依赖行。默认使用 `not null`；用排他约束表达“不能重叠”；用 `not exists` 替代 `not in`；有意保留历史记录。
## 延伸阅读 {#further-reading}
- [Constraints](https://www.postgresql.org/docs/18/ddl-constraints.html)，PostgreSQL documentation。集中介绍每种约束类型，并说明引用列不会自动获得索引。
- [CREATE TABLE](https://www.postgresql.org/docs/18/sql-createtable.html)，PostgreSQL documentation。介绍所有约束子句、可延迟约束、`not enforced`、`without overlaps` 和 `period`。
- [A Simple Guide to Five Normal Forms in Relational Database Theory](https://www.bkent.net/Doc/simple5.htm)，William Kent，Communications of the ACM，1983。用直白语言介绍各个范式，以及每种范式阻止的异常。