约束与生成列：把不变量交给数据库
应用层的校验只能保证「通过我这条路径写进来的数据」是对的。数据迁移脚本、运维手工修数据、第二个服务、出问题时的紧急修复——这些路径都绕过了它。约束是唯一对所有路径都生效的地方。
生成列：让派生数据无法写错
Section titled “生成列：让派生数据无法写错”DROP TABLE IF EXISTS line_items; CREATE TABLE line_items ( id int PRIMARY KEY, price numeric(10,2) NOT NULL, qty int NOT NULL, total numeric(12,2) GENERATED ALWAYS AS (price * qty) STORED ); INSERT INTO line_items (id, price, qty) VALUES (1, 19.99, 3), (2, 5.00, 10); SELECT * FROM line_items;
total 不能被写入，写了直接报错：
UPDATE line_items SET total = 1 WHERE id = 1;
改 price 或 qty，total 自动跟着走：
UPDATE line_items SET qty = 5 WHERE id = 1; SELECT * FROM line_items ORDER BY id;
生成列 vs 触发器：能用生成列就别写触发器。生成列是声明式的，优化器知道它的定义，ALTER TABLE 时行为明确，也不会出现「触发器被临时禁用后数据悄悄不一致」的情况。
限制是：表达式必须是 IMMUTABLE 的，只能引用同一行的列。所以 now()、子查询、引用别的表——都不行。
Postgres 18 起还支持 VIRTUAL 生成列（查询时计算、不占存储）。STORED 占空间但读得快，VIRTUAL 反之；建索引需要 STORED。
UNIQUE 和 NULL：一个经典的坑
Section titled “UNIQUE 和 NULL：一个经典的坑”SQL 标准规定 NULL 之间互不相等，所以唯一约束不会拦截重复的 NULL：
DROP TABLE IF EXISTS u1; CREATE TABLE u1 (a int, b int, UNIQUE (a, b)); INSERT INTO u1 VALUES (1, NULL), (1, NULL), (1, NULL); SELECT count(*) AS 三行都插进去了 FROM u1;
「同一个用户对同一商品只能有一条未删除的记录」，如果用 (user_id, sku, deleted_at) 做唯一约束，而未删除时 deleted_at 是 NULL——约束形同虚设。
PG 15 起可以显式要求 NULL 相等：
DROP TABLE IF EXISTS u2; CREATE TABLE u2 (a int, b int, UNIQUE NULLS NOT DISTINCT (a, b)); INSERT INTO u2 VALUES (1, NULL); INSERT INTO u2 VALUES (1, NULL);
第二条被拦住了。
排他约束：唯一约束的推广
Section titled “排他约束：唯一约束的推广”UNIQUE 说的是「这两行的这些列不能相等」。排他约束（EXCLUDE）把「相等」换成任意操作符——最典型的是范围重叠 &&：
CREATE EXTENSION IF NOT EXISTS btree_gist; DROP TABLE IF EXISTS booking; CREATE TABLE booking ( id int PRIMARY KEY, room int, during tsrange, EXCLUDE USING gist (room WITH =, during WITH &&) ); INSERT INTO booking VALUES (1, 101, '[2024-06-01 09:00, 2024-06-01 10:00)'), (2, 102, '[2024-06-01 09:30, 2024-06-01 10:30)'); SELECT count(*) AS 不同房间不冲突 FROM booking;
读作：不允许存在两行，它们的 room 相等且 during 重叠。
INSERT INTO booking VALUES (3, 101, '[2024-06-01 09:30, 2024-06-01 10:30)');
101 房间 9:30–10:30 和已有的 9:00–10:00 重叠，被拦住了。
btree_gist 扩展是必需的——GiST 原生不支持 int 的 =，这个扩展把 B-tree 能处理的类型接进 GiST。
范围类型本身也值得一提——tsrange / int4range / daterange 自带边界语义：
SELECT '[2024-06-01, 2024-06-10)'::daterange @> date '2024-06-01' AS 包含左端点,
'[2024-06-01, 2024-06-10)'::daterange @> date '2024-06-10' AS 包含右端点,
'[1,5]'::int4range * '[3,9]'::int4range AS 交集,
upper('[1,5]'::int4range) AS 右边界被规范化;[ 闭 ) 开是默认写法。注意最后一列：int4range 是离散类型，[1,5] 会被自动规范化成 [1,6)。
有些约束在语句执行的中间状态必然被违反，只有事务结束时才应该成立。典型的是「交换两行的排序号」：
DROP TABLE IF EXISTS ordering; CREATE TABLE ordering ( pos int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, name text ); BEGIN; INSERT INTO ordering VALUES (1, 'a'), (1, 'b'); SELECT '插入时没有报错' AS 状态;
两行都是 pos = 1，主键冲突了，但没有立即报错——检查被推迟到提交时：
COMMIT;
提交时才拒绝，整个事务回滚。
ROLLBACK;
CHECK 与域
Section titled “CHECK 与域”CHECK 是最朴素也最被低估的约束：
DROP TABLE IF EXISTS account;
CREATE TABLE account (
id int PRIMARY KEY,
balance numeric(12,2) NOT NULL CHECK (balance >= 0),
status text NOT NULL CHECK (status IN ('active', 'frozen', 'closed')),
CHECK (status <> 'closed' OR balance = 0)
);
INSERT INTO account VALUES (1, 100.00, 'active');
SELECT * FROM account;最后那条表级 CHECK 表达的是跨列的不变量：已关闭的账户余额必须为 0。这类规则写在应用层，迟早会有一条路径绕过去。
INSERT INTO account VALUES (2, 50.00, 'closed');
如果同一套规则要在很多表里复用，可以做成域：
DROP TABLE IF EXISTS person; DROP DOMAIN IF EXISTS email; CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@[:space:]]+@[^@[:space:]]+$'); CREATE TABLE person (id int PRIMARY KEY, e email); INSERT INTO person VALUES (1, 'a@b.com'); SELECT * FROM person;
INSERT INTO person VALUES (2, 'not-an-email');
先自己回答，再点开对照。
生成列相比触发器的优势是什么？它有哪些限制？
优势是声明式：优化器知道它的定义，ALTER TABLE 时行为明确，而且不会出现「触发器被临时禁用后数据悄悄不一致」这种情况——生成列没有开关可以关。写不进去，写了直接报错。
限制来自一条硬性要求：表达式必须是 IMMUTABLE 的，且只能引用同一行的列。所以 now()、子查询、引用别的表——都不行。
另外 PG 18 起有 VIRTUAL 和 STORED 两种：STORED 占存储但读得快，VIRTUAL 查询时才算、不占空间，但建索引需要 STORED。
常见错误：想用生成列做 updated_at 自动填充——这恰恰是触发器最经典的用途，看起来正是「能用生成列就别写触发器」该覆盖的场景。但 now() 是 VOLATILE 的，直接被 IMMUTABLE 这条规则挡在门外。分界线不是「哪个更现代」，而是这个值能不能只从同一行的其他列算出来：能，用生成列；依赖时间、依赖别的行、依赖别的表，只能用触发器。
为什么 UNIQUE (a, b) 拦不住重复的 (1, NULL)？软删除场景该怎么做唯一性？
因为 SQL 标准规定 NULL 之间互不相等。唯一约束判断的是「这两行相等吗」，只要有一列是 NULL，答案就是「不知道」，于是放行——本节里 (1, NULL) 连插三次全都成功。
软删除场景有两种解法：
- 部分唯一索引（更常用）：
CREATE UNIQUE INDEX ON items (user_id, sku) WHERE deleted_at IS NULL。只对未删除的行建索引，语义直白，索引也更小。 - PG 15 起的
UNIQUE NULLS NOT DISTINCT (a, b)，显式要求NULL之间相等。
常见错误：把 deleted_at 加进唯一约束，写成 UNIQUE (user_id, sku, deleted_at)，读起来完全成立——「同一用户、同一商品、同一删除状态只能有一条」。约束创建成功，测试里删除后重新添加也正常工作。问题在于它只在已删除的那一侧生效：deleted_at 有值时三列都能比较，重复确实被拦住了；而未删除时 deleted_at IS NULL，约束形同虚设，同一用户可以有任意多条未删除的记录——恰好是你真正想保护的那一侧。
排他约束和唯一约束的关系是什么？为什么「时间段不重叠」不能只靠应用层检查？
UNIQUE 是排他约束的特例。UNIQUE 说的是「不允许存在两行，它们的这些列相等」；EXCLUDE 把「相等」换成任意操作符——EXCLUDE USING gist (room WITH =, during WITH &&) 读作「不允许存在两行，它们的 room 相等且 during 重叠」。
应用层做不到，是因为「先 SELECT 查有没有冲突，再 INSERT」天然有竞态：两个请求同时通过检查，然后都插入成功。排他约束把这件事交给一个索引，对所有写入路径生效。
btree_gist 扩展是必需的——GiST 原生不支持 int 的 =，这个扩展把 B-tree 能处理的类型接进 GiST。
常见错误：以为「把 SELECT 和 INSERT 包进同一个事务就安全了」。事务保证的是原子性，不是「别人不能在我读完之后写入」。默认的 Read Committed 下，两个事务各自的 SELECT 都看不到对方尚未提交的行，双双通过检查、双双提交，一行报错都没有。要靠事务堵住这个洞，得升到可串行化隔离级别或者显式加表锁——都比一个索引贵得多。
什么场景需要延迟约束？为什么不建议默认开启？
需要延迟的是这样一类约束：在语句执行的中间状态必然被违反，只有事务结束时才应该成立。典型是「交换两行的排序号」——不管先改哪一行，中间都会短暂出现两行同号。
不建议默认开启的理由有两条：
- 冲突要到事务末尾才暴露，错误信息离真正出错的那条语句很远，排查变难；
- 违规的行会在事务内一直存在，占着资源。
推荐写法是 DEFERRABLE INITIALLY IMMEDIATE——默认立即检查，只在确实需要时用 SET CONSTRAINTS ... DEFERRED 临时推迟。
常见错误：以为写了 DEFERRABLE 就已经推迟了。DEFERRABLE 只是允许推迟，真正决定默认行为的是后半截：INITIALLY DEFERRED 才是默认推迟，INITIALLY IMMEDIATE 仍然立即检查。两个关键字长得像、含义正交，而混淆的后果是不对称的——如果你以为开了延迟其实没开，交换排序号的事务直接报错，你立刻就知道；如果你以为只是「加了个能力」结果写成了 INITIALLY DEFERRED，全表的约束检查都被推到了提交时，这个变化在功能测试里完全看不出来。
CHECK 约束不能做哪些事？
两条边界：
- 不能引用其他行或其他表——子查询在
CHECK里是被禁止的。跨行的不变量属于外键和排他约束的领域。 - 应该是 IMMUTABLE 的，但 Postgres 不强制。
CREATE TABLE bad (t timestamptz CHECK (t <= now()))会被成功创建。
第二条的危险在于 CHECK 只在写入时求值：已经存进去的行不会被重新检查，于是表里会长期存在「按当前定义不合法」的数据。更糟的是 pg_dump / 恢复时会重新校验每一行——一个 CHECK (t >= now()) 之类的约束能让整个恢复过程失败。
常见错误：以为「Postgres 允许我创建，就说明这么写没问题」。这是把 DDL 通过当成了设计通过。约束创建成功、插入正常、测试全绿，什么都不会提醒你——问题要等到从备份恢复的那一天才出现，而那一天你正在救火。判断标准不靠数据库：表达式里出现 now()、random() 或任何 VOLATILE 函数，就是设计错了。