PostgreSQL / 系统课时

高级约束

高级约束

高级约束的价值是把“永远不应出现的数据状态”直接交给数据库守住,而不是只靠应用代码约定。本课用促销活动模型组合 CHECK、UNIQUE、EXCLUDE 与可延迟约束,展示值域、唯一性、区间冲突和事务提交时检查四类规则。

学习目标

  • 用 CHECK、UNIQUE 和 EXCLUDE 分别表达值域、唯一性与跨行冲突规则
  • 理解 DEFERRABLE 如何把约束检查从语句结束推迟到事务提交
  • 能判断哪些业务规则适合数据库约束,哪些仍需要事务逻辑或触发器

把业务不变量写进表定义

下面的促销表要求折扣必须在 0 到 100 之间、活动代码唯一,并禁止任意两个活动时间区间重叠。tstzrange 自带 GiST 操作符类,因此可以直接用 && 表示“区间发生重叠”:

sql
CREATE TABLE promotion_campaign (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code text NOT NULL,
    discount_percent numeric(5,2) NOT NULL,
    valid_during tstzrange NOT NULL,
    CONSTRAINT promotion_discount_chk
        CHECK (discount_percent > 0 AND discount_percent <= 100),
    CONSTRAINT promotion_code_uq
        UNIQUE (code) DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT promotion_no_overlap
        EXCLUDE USING gist (valid_during WITH &&)
        DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO promotion_campaign (code, discount_percent, valid_during) VALUES
('AUTUMN-A', 10, tstzrange('2026-09-01 00:00+00', '2026-09-05 00:00+00', '[)')),
('AUTUMN-B', 15, tstzrange('2026-09-05 00:00+00', '2026-09-10 00:00+00', '[)'));

预期输出:

text
CREATE TABLE
INSERT 0 2

[) 表示“包含起点、不包含终点”,因此两个区间在 9 月 5 日首尾相接但不重叠。CHECK 只验证单行表达式;UNIQUE 约束跨行重复值;EXCLUDE 更进一步,可以声明“任意两行不能在指定运算关系上同时成立”。

高级约束的空间冲突与检查时机

用 EXCLUDE 阻止跨行冲突

普通唯一约束只能表达“值不能相等”,却不能直接表达“时间段不能相交”。向上表插入一个与现有活动重叠的区间,会由排斥约束在数据库层拒绝:

sql
INSERT INTO promotion_campaign (code, discount_percent, valid_during)
VALUES (
    'AUTUMN-C',
    20,
    tstzrange('2026-09-04 00:00+00', '2026-09-06 00:00+00', '[)')
);

预期结果:

text
ERROR:  conflicting key value violates exclusion constraint "promotion_no_overlap"

错误的详细行内容会包含发生冲突的区间,具体格式可能因客户端版本而不同。真实预约系统常把 room_id WITH = 与 valid_during WITH && 组合起来,表示“同一房间的时间段不能重叠”;若等值列的类型没有内置 GiST 操作符类,通常需要 btree_gist 扩展提供支持。

延迟约束检查到提交时

可延迟约束解决的是“事务最终合法,但中间步骤暂时冲突”的问题。例如交换两个唯一代码时,立即约束可能在中间状态看到重复值;把命名约束设为 DEFERRED 后,数据库到提交时再验证最终状态:

sql
BEGIN;

SET CONSTRAINTS promotion_code_uq DEFERRED;

UPDATE promotion_campaign
SET code = CASE code
    WHEN 'AUTUMN-A' THEN 'AUTUMN-B'
    WHEN 'AUTUMN-B' THEN 'AUTUMN-A'
END
WHERE code IN ('AUTUMN-A', 'AUTUMN-B');

COMMIT;

SELECT id, code
FROM promotion_campaign
ORDER BY id;

预期输出:

text
BEGIN
SET CONSTRAINTS
UPDATE 2
COMMIT
 id |   code
----+----------
  1 | AUTUMN-B
  2 | AUTUMN-A

只有声明为 DEFERRABLE 的约束才能被 SET CONSTRAINTS 推迟。推迟检查不是关闭检查:如果提交时仍存在重复值或排斥冲突,COMMIT 会失败,事务必须回滚。

CHECK 与 UNIQUE 的易错边界

CHECK 表达式的结果只要不是 FALSE 就视为通过,因此可空列上的 CHECK (value > 0) 并不能替代 NOT NULL;如果 NULL 不合法,应同时声明 NOT NULL。此外,CHECK 不适合通过子查询去保证其他行或其他表的状态,因为跨行变化会让这种规则难以保持一致。

普通 UNIQUE 约束主要用于候选键和业务编号。对于需要把 NULL 也视为“同一个值”的场景,可使用 PostgreSQL 支持的 NULLS NOT DISTINCT 唯一语义。还要注意,可延迟唯一约束不能作为 INSERT ... ON CONFLICT 的冲突仲裁目标,因此“需要延迟检查”和“需要 UPSERT 仲裁”之间要提前做取舍。

约束该做到哪一层

能用声明式约束准确表达的“不变量”应优先放进数据库:它对所有写入入口都生效,也能在并发事务下由 PostgreSQL 统一维护。CHECK 管单行值域,UNIQUE 管重复键,EXCLUDE 管更一般的跨行冲突,DEFERRABLE 则控制检查时机。

涉及外部服务、复杂聚合、跨很多表的业务审批流程时,不要强行塞进约束表达式。此时应让数据库约束守住最小一致性边界,再由事务代码、存储过程或必要的触发器完成更高层规则。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

高级约束

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通