PostgreSQL / 系统课时

设计外键与引用关系

设计外键与引用关系

分区表可以参与外键,但“能建外键”不等于“适合被长期引用”。本课以按月分区的订单表为例,把引用方向与分区下线动作放在一起设计,避免归档时才发现 DETACH PARTITION 被锁等待或参照完整性阻断。

学习目标

  • 区分分区表作为引用方和被引用方时的生命周期影响
  • 为高频归档表选择更稳妥的外键方向与删除动作
  • 用可运行 SQL 验证父表外键会约束写入各个分区的数据

优先让分区事实表引用稳定主表

按月淘汰的订单、事件、日志通常是“生命周期短的一侧”,客户、租户、设备等主数据则更稳定。把外键定义为“分区事实表 → 稳定主表”,既能在插入时阻止孤儿数据,又不会让主数据依赖某个即将归档的历史分区。

下面在分区父表上定义外键。插入会被路由到具体分区,但参照完整性仍由 PostgreSQL 检查;业务不需要逐个给新分区手工补同一条外键。

sql
CREATE TABLE customers (
    customer_id bigint PRIMARY KEY,
    name text NOT NULL
);

INSERT INTO customers VALUES (10, 'Acme');

CREATE TABLE orders (
    ordered_on date NOT NULL,
    order_id bigint NOT NULL,
    customer_id bigint NOT NULL,
    PRIMARY KEY (ordered_on, order_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE RESTRICT
) PARTITION BY RANGE (ordered_on);

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

INSERT INTO orders VALUES ('2026-09-01', 2001, 10);
INSERT INTO orders VALUES ('2026-09-02', 2002, 999);

预期输出

text
CREATE TABLE
INSERT 0 1
CREATE TABLE
CREATE TABLE
INSERT 0 1
ERROR:  insert or update on table "orders_2026_09" violates foreign key constraint "fk_orders_customer"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

最后一条写入失败,说明外键约束作用到了实际承载数据的分区。错误中的分区名和本地化文字可能因版本或客户端设置略有差异,但不存在的 customer_id=999 必须被拒绝。

把被引用方向当成生命周期风险

反过来,如果长期存在的明细表、结算表或审计表引用一个会频繁拆分和下线的分区父表,归档动作就不再只是“移走一张物理表”。PostgreSQL 在分区表被外键引用时执行 DETACH PARTITION,需要在引用表上取得锁;若被拆出的分区含有仍被外部行引用的键,操作会被拒绝,以免留下失去目标行的外键记录。

外键目标还必须由可被引用的主键或唯一约束支撑。目标如果是分区表,这个候选键同样受分区唯一性规则约束,通常需要包含全部分区键;因此引用方往往也要携带这些列。若长期业务关系只想保存一个稳定 order_id,这正是拆出独立身份表的另一个理由。

分区表外键方向与归档风险

因此,准备按月 DETACH、备份、再删除的表,应尽量少承担其他长期表的“身份根”职责。真正需要长期被引用的对象,可以拆出非分区的身份表,例如 order_identity(order_id PRIMARY KEY),让长期关系引用身份表,而大体量订单明细继续按时间分区。

明确删除动作而不是依赖级联兜底

ON DELETE CASCADE 处理的是删除被引用行,并不会把分区拆卸自动变成安全的级联归档。对于客户与历史订单这类关系,RESTRICT 或默认的 NO ACTION 往往更能暴露误删;只有业务确实要求父对象删除时同步删除所有子记录,才应选择 CASCADE。

归档 Runbook 也应把引用检查放在 DETACH PARTITION 前,而不是等 DDL 报错。至少确认即将下线的数据是否仍是其他在线表的外键目标,并评估引用表锁是否会影响高峰写入。

sql
SELECT conname,
       conrelid::regclass AS referencing_table,
       confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'orders'::regclass;

预期输出

text
 conname | referencing_table | referenced_table
---------+-------------------+------------------
(0 rows)

在当前示例中没有其他表引用 orders,因此结果为空。若生产库返回记录,就应把这些引用表纳入分区下线评审;实际约束名和表名以你的数据库为准。

用引用方向控制维护成本

外键设计不应只看 ER 关系是否成立,还要看哪一侧会频繁建分区、拆分区和淘汰历史数据。高频变化的分区事实表引用稳定主表,通常能保留完整性而降低归档耦合;若业务必须让长期数据引用分区表,则应把锁、引用清理和失败回滚写进生命周期流程。

最终判断标准很简单:被引用的键应尽量比引用它的数据活得更久。当这个条件不成立时,优先重新划分身份表与事实表边界,而不是用 CASCADE 或临时删除约束掩盖模型冲突。

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

目录与阅读设置

记录一个想法

设计外键与引用关系

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

登录哪都通