PostgreSQL / 系统课时

设计主键与唯一约束

设计主键与唯一约束

分区表最容易踩的完整性坑,是把普通表上的“全局唯一”直觉直接搬过来。本课用按日期分区的订单表说明:哪些主键与唯一约束能由 PostgreSQL 原生保证,以及业务只要求 order_id 全局唯一时应怎样调整模型。

学习目标

  • 解释为什么分区父表的 PRIMARY KEY 和 UNIQUE 必须覆盖全部分区键
  • 区分“组合键在全表唯一”和“某个业务列单独全局唯一”
  • 根据业务身份与分区生命周期选择可维护的唯一性方案

先判断唯一性到底约束什么

以下以 PostgreSQL 18 的声明式分区为基线。分区父表本身不存数据,真正的唯一索引位于各个分区。要把多个局部唯一索引组合成全表可证明的唯一性,分区边界本身必须参与证明,因此父表上的主键或唯一约束必须包含全部分区键列。

例如订单按 ordered_on 做范围分区,PRIMARY KEY (ordered_on, order_id) 合法。它保证的是二元组 (ordered_on, order_id) 在整张逻辑表中唯一,并不保证 order_id 单列在不同日期之间不能重复。

sql
CREATE TABLE orders (
    ordered_on date NOT NULL,
    order_id bigint NOT NULL,
    customer_id bigint NOT NULL,
    amount numeric(12, 2) NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (ordered_on, order_id)
) PARTITION BY RANGE (ordered_on);

CREATE TABLE orders_2026_08 PARTITION OF orders
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE orders_2026_09 PARTITION OF orders
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

INSERT INTO orders VALUES
    ('2026-08-31', 1001, 10, 88.00),
    ('2026-09-01', 1001, 11, 99.00);

SELECT tableoid::regclass AS partition_name, ordered_on, order_id
FROM orders
ORDER BY ordered_on;

预期输出

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
 partition_name | ordered_on | order_id
----------------+------------+----------
 orders_2026_08 | 2026-08-31 |     1001
 orders_2026_09 | 2026-09-01 |     1001
(2 rows)

同一个 order_id=1001 能出现在两个分区中,因为主键比较的是日期与订单号的组合。若业务语义要求订单号本身永不重复,这个主键只能充当数据库键的一部分,不能当作“已验证单列全局唯一”的证据。

分区键如何参与全局唯一性约束

识别不能直接建立的约束

如果直接要求 UNIQUE (order_id),PostgreSQL 会拒绝创建,因为 ordered_on 没有包含在唯一约束中。即使你给每个分区分别建立 UNIQUE (order_id),也只能阻止同一分区内重复,无法阻止另一个分区再次出现相同订单号。

sql
ALTER TABLE orders
ADD CONSTRAINT orders_order_id_uk UNIQUE (order_id);

预期输出

text
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "orders" lacks column "ordered_on" which is part of the partition key.

错误文本可能因客户端语言略有不同,但限制不变。另一个常被忽略的边界是:若分区键使用表达式或函数调用,分区父表也不能建立这样的主键或唯一约束;需要先把可参与约束的值落成真实列,或重新选择分区键。

在三种设计之间取舍

如果业务天然用“时间 + 业务号”定位一条记录,直接使用包含分区键的组合主键最简单。引用它的表也应携带相同键列,代价是外键和应用参数都会变宽。

如果业务必须由 order_id 单列作为数据库强制的全局身份,可以让分区键本身来自该身份,例如按 order_id 做 HASH 分区,使 PRIMARY KEY (order_id) 同时覆盖分区键;但这样会失去按时间快速淘汰整月数据的优势。

如果既要按时间快速归档,又要数据库强制 order_id 全局唯一,可以把稳定身份放到非分区注册表,由注册表主键保证唯一,分区事实表保存 order_id 与时间。这个方案增加一次写入和引用关系,却把“全局身份”与“历史数据生命周期”拆成两个清晰职责。

让约束服务于分区目标

设计时先写出真正需要唯一的业务元组,再检查它是否天然包含全部分区键。不要为了让 DDL 通过而机械追加日期列,然后继续把单列订单号当成已被数据库全局约束。

分区键、主键和归档策略应一起决定:需要按时间快速删除时,接受组合键或引入稳定身份表;需要单列主键最简单时,则考虑让该主键参与分区。这样得到的约束既能被 PostgreSQL 正确执行,也不会在业务语义上制造“看似唯一”的假象。

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

目录与阅读设置

记录一个想法

设计主键与唯一约束

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

登录哪都通