PostgreSQL / 系统课时

外键与级联策略

外键与级联策略

外键不仅决定“能不能引用不存在的行”,还决定父记录变更时子表如何响应。本篇用订单模型梳理 ON DELETE、ON UPDATE 的动作语义,并把引用列索引和大表渐进式加约束放进同一套可执行方案。

学习目标

  • 根据业务生命周期选择 RESTRICT、CASCADE、SET NULL 等外键动作
  • 判断外键两侧需要哪些索引,避免级联删除和父键更新退化成大范围扫描
  • 使用 NOT VALID 与 VALIDATE CONSTRAINT 为存量大表分阶段补充外键

先按业务语义选择动作

外键的核心问题不是“要不要级联”,而是父记录消失后,子记录在业务上还应不应该存在。如果子记录是父记录不可分割的组成部分,例如订单项依附订单,CASCADE 通常合理;如果删除父记录代表高风险业务动作,则更适合拒绝删除并由应用显式处理。

动作 父行删除或键值更新时 适合场景
NO ACTION 存在引用时拒绝;约束可延迟时可在事务末检查 默认保守策略,需要事务内调整顺序
RESTRICT 存在引用时直接拒绝,不能延迟检查 父对象不应被隐式删除
CASCADE 删除子行,或把子外键同步更新为新父键 强生命周期从属关系
SET NULL 把子表外键设为 NULL 子对象可独立存在,且列允许空值
SET DEFAULT 把子表外键改成默认值 确有合法“默认父对象”的模型

外键动作与子表结果

用订单模型验证级联边界

下面在当前会话创建临时表:客户删除受 RESTRICT 保护,订单删除会级联清理订单项。主示例只删除订单,因此可以直接观察 CASCADE 的效果。

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

CREATE TEMP TABLE fk_order_demo (
    order_id bigint PRIMARY KEY,
    customer_id bigint NOT NULL
        REFERENCES fk_customer_demo(customer_id) ON DELETE RESTRICT
);

CREATE TEMP TABLE fk_order_item_demo (
    item_id bigint PRIMARY KEY,
    order_id bigint NOT NULL
        REFERENCES fk_order_demo(order_id) ON DELETE CASCADE,
    sku text NOT NULL
);

INSERT INTO fk_customer_demo VALUES (1, 'Alice');
INSERT INTO fk_order_demo VALUES (1001, 1);
INSERT INTO fk_order_item_demo VALUES (9001, 1001, 'SKU-RED');

DELETE FROM fk_order_demo WHERE order_id = 1001;

SELECT
    (SELECT count(*) FROM fk_order_demo) AS orders_left,
    (SELECT count(*) FROM fk_order_item_demo) AS items_left;

预期输出

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
DELETE 1
 orders_left | items_left
-------------+-----------
           0 |          0
(1 row)

如果此时直接删除仍被订单引用的客户,RESTRICT 会让语句失败;这类保护适合把“先迁移、归档或取消子对象”变成显式业务动作,而不是让数据库静默扩大删除范围。

给引用列补上正确索引

被引用列必须由主键、唯一约束或非部分唯一索引保证唯一性,因此父表一侧通常已经有索引。子表的外键列不会因为创建外键而自动获得索引,而父行删除、父键更新、连接查询都经常需要按该列定位子行。

sql
CREATE INDEX fk_order_customer_id_idx
ON fk_order_demo(customer_id);

SELECT EXISTS (
    SELECT 1
    FROM pg_indexes
    WHERE tablename = 'fk_order_demo'
      AND indexname = 'fk_order_customer_id_idx'
) AS fk_index_exists;

预期输出

text
CREATE INDEX
 fk_index_exists
-----------------
 t
(1 row)

复合外键同样要从真实查询和级联路径出发设计索引列顺序。不要机械地“每个外键一个索引”:如果引用表极小、父键从不更新删除,额外索引可能只增加写入成本;但对大表和高频父对象变更,缺少引用列索引通常风险更高。

为存量大表分阶段加约束

直接给大表新增已验证外键会检查历史数据。更稳妥的方式是先以 NOT VALID 添加约束,让旧数据暂不扫描,同时新插入或被更新的行仍需满足外键;随后在业务低峰执行 VALIDATE CONSTRAINT 完成历史校验。

sql
BEGIN;

CREATE TABLE migration_customer_demo (
    customer_id bigint PRIMARY KEY
);

CREATE TABLE migration_order_demo (
    order_id bigint PRIMARY KEY,
    customer_id bigint NOT NULL
);

INSERT INTO migration_customer_demo VALUES (1);
INSERT INTO migration_order_demo VALUES (1001, 1);

ALTER TABLE migration_order_demo
ADD CONSTRAINT migration_order_customer_fk
FOREIGN KEY (customer_id)
REFERENCES migration_customer_demo(customer_id)
NOT VALID;

SELECT convalidated
FROM pg_constraint
WHERE conname = 'migration_order_customer_fk';

ALTER TABLE migration_order_demo
VALIDATE CONSTRAINT migration_order_customer_fk;

SELECT convalidated
FROM pg_constraint
WHERE conname = 'migration_order_customer_fk';

ROLLBACK;

预期输出

text
BEGIN
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
ALTER TABLE
 convalidated
--------------
 f
(1 row)
ALTER TABLE
 convalidated
--------------
 t
(1 row)
ROLLBACK

生产变更前先检查孤儿行、估算级联涉及的行数,并为引用列准备索引。超大级联删除更适合应用显式分批处理和可观测地提交事务;外键负责守住完整性边界,而不是替代容量评估和变更编排。

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

目录与阅读设置

阅读设置

17

记录一个想法

外键与级联策略

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

登录哪都通