PHP小码农

Appearance

FOREIGN KEY 外键 ​

外键（Foreign Key）是关系型数据库里唯一能强制跨表一致性的机制：它保证子表里出现的引用值，必然在父表中真实存在。本节讲清外键的三种写法、五种引用动作、多列外键与 NULL 的陷阱，以及最容易出事的一条——外键列不会自动建索引。

前置知识

阅读本节前，建议先了解：PRIMARY KEY 主键

基础概念 ​

外键做的事情只有一件：在子表写入或更新时，检查引用值是否存在于父表。它涉及两个角色：

text
父表（Referenced Table）  users(id)          ← 被引用的目标，必须有 PRIMARY KEY 或 UNIQUE
子表（Referencing Table） orders(user_id)    ← 存放引用值，可以有很多行指向同一个父行

外键的检查时机有两个，方向相反：

时机触发条件报错方
子表写入/更新引用值在父表中不存在insert or update on table "orders" violates foreign key constraint
父表删除/更新引用列仍有子表行在引用update or delete on table "users" violates foreign key constraint

第二条是外键性能问题的根源：父表删改时要反向检查子表，而这个检查依赖子表引用列上的索引。

外键与其它约束的对照：

约束是否自动建索引是否允许 NULL能否被引用
PRIMARY KEY是否是（默认目标）
UNIQUE是是（多个 NULL 不冲突）是
FOREIGN KEY否（引用侧需手工建）是—

「FOREIGN KEY 不自动建索引」是本节最重要的一条，也是 PostgreSQL 与 MySQL 的一处显式差异（InnoDB 会自动为外键列建索引）。生产环境里大量「删一个用户卡住几秒」的问题都源于此。

语法与用法 ​

text
-- ① 列级写法：单列引用
column_name data_type [ CONSTRAINT constraint_name ]
    REFERENCES reftable [ ( refcolumn ) ]
    [ MATCH { FULL | SIMPLE | PARTIAL } ]
    [ ON DELETE referential_action ]
    [ ON UPDATE referential_action ]
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

-- ② 表级写法：单列或复合引用
[ CONSTRAINT constraint_name ]
FOREIGN KEY ( column_name [, ... ] )
    REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH ... ] [ ON DELETE ... ] [ ON UPDATE ... ] [ DEFERRABLE ... ]

-- referential_action 取值
NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( ... ) ]

四种动作的两两组合（ON DELETE × ON UPDATE）共 25 种搭配，实际最常用的是下面几组：

场景推荐写法
用户删了，订单不能留ON DELETE RESTRICT
用户删了，订单一起删ON DELETE CASCADE
用户删了，订单保留但置空ON DELETE SET NULL
主键基本不变ON UPDATE 省略（默认 NO ACTION）

最简可运行示例：

sql
CREATE TABLE users (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text   NOT NULL,
  CONSTRAINT uq_users_email UNIQUE (email)
);

CREATE TABLE orders (
  id      bigint  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint  NOT NULL,
  amount  numeric(12,2) NOT NULL,

  -- 列级写法：引用 users(id)
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE RESTRICT
);
text
CREATE TABLE
CREATE TABLE

详细说明 ​

三种写法：列级、表级与复合外键 ​

单列引用时列级与表级完全等价：

sql
-- 写法 A：列级
CREATE TABLE t_col (
  user_id bigint REFERENCES users (id)
);

-- 写法 B：表级（语义相同）
CREATE TABLE t_tab (
  user_id bigint,
  FOREIGN KEY (user_id) REFERENCES users (id)
);

复合外键（Composite Foreign Key）只能用表级，且引用列的数量与顺序必须完全对应：

sql
CREATE TABLE order_items (
  order_id   bigint  NOT NULL,
  line_no    integer NOT NULL,
  product_id bigint  NOT NULL,
  qty        integer NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

-- 子表：用 (order_id, line_no) 复合引用父表主键
CREATE TABLE shipments (
  id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id bigint  NOT NULL,
  line_no  integer NOT NULL,
  shipped_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT fk_shipments_item
    FOREIGN KEY (order_id, line_no) REFERENCES order_items (order_id, line_no)
    ON DELETE CASCADE
);

复合外键的一条硬性规则：引用列的集合必须整体匹配父表的一个唯一约束，不能「部分匹配」：

sql
-- 错误！父表只有 (order_id, line_no) 的组合唯一，没有单独的 line_no 唯一约束
CREATE TABLE bad_fk (
  line_no integer,
  CONSTRAINT fk_bad FOREIGN KEY (line_no) REFERENCES order_items (line_no)
);
text
ERROR:  there is no unique constraint matching given keys for referenced table "order_items"

引用目标必须是 PRIMARY KEY 或 UNIQUE ​

这是外键的定义要求：父表被引用的列组合必须有唯一性保证，否则「引用某一行的 id」这件事本身就没有确定含义。

sql
CREATE TABLE categories (
  id   bigint NOT NULL,          -- 只有 NOT NULL，没有唯一约束
  name text   NOT NULL
);

-- 错误！引用目标不唯一
CREATE TABLE products (
  id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  cat_id   bigint,
  CONSTRAINT fk_products_cat FOREIGN KEY (cat_id) REFERENCES categories (id)
);
text
ERROR:  there is no unique constraint matching given keys for referenced table "categories"

三种正确的目标形式：

sql
-- ① 主键
CONSTRAINT fk_a FOREIGN KEY (user_id) REFERENCES users (id)

-- ② 唯一约束
CONSTRAINT fk_b FOREIGN KEY (email) REFERENCES users (email)   -- users.email 有 UNIQUE

-- ③ 普通唯一索引也可以（不是约束也能被引用）
-- CREATE UNIQUE INDEX uq_cat_code ON categories (code);
CONSTRAINT fk_c FOREIGN KEY (cat_code) REFERENCES categories (code)

注意第 ③ 种：裸唯一索引也能作为外键目标，这常被误认为「必须写成 UNIQUE 约束」。

另外，被引用的唯一索引不能是部分索引：

sql
-- 错误！部分唯一索引不能作为外键目标
CREATE UNIQUE INDEX uq_cat_code_partial ON categories (code) WHERE is_active;
CREATE TABLE t_bad (code text REFERENCES categories (code));
text
ERROR:  there is no unique constraint matching given keys for referenced table "categories"

五种引用动作逐一对齐 ​

ON DELETE 与 ON UPDATE 支持同样的五种动作，语义完全对称。

动作含义可延迟（DEFERRABLE）典型用途
NO ACTION默认。若仍被引用则报错，但可延迟到语句/事务末是默认值，配合延迟约束
RESTRICT立即拒绝，不能延迟否强一致的「禁止删除」
CASCADE级联删除/更新子表行是从属数据（订单行）
SET NULL把子表引用列置 NULL（列必须可空）是可选关联（推荐人）
SET DEFAULT把子表引用列设为默认值（默认值必须存在于父表）是归入「未知/默认」分组

① NO ACTION（默认）

sql
CREATE TABLE t_na (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT fk_na FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION
);

INSERT INTO users (id, email) VALUES (1, 'a@example.com');
INSERT INTO t_na VALUES (1, 1);

DELETE FROM users WHERE id = 1;
text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_na" on table "t_na"
DETAIL:  Key (id)=(1) is still referenced from table "t_na".

② RESTRICT

sql
CREATE TABLE t_rs (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT fk_rs FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
);

行为上与 NO ACTION 在默认（非延迟）场景下完全相同，都立刻报错。区别只在延迟约束下（见下一节）。

③ CASCADE：级联

ON DELETE CASCADE 会连带删除子表行：

sql
CREATE TABLE order_lines (
  id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  sku      text   NOT NULL,
  CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

INSERT INTO orders (user_id, amount) VALUES (1, 100.00), (1, 200.00);
INSERT INTO order_lines (order_id, sku) VALUES (1, 'KB-001'), (1, 'MS-002'), (2, 'ST-003');

-- 删订单，行自动被删
DELETE FROM orders WHERE id = 1;
SELECT count(*) AS remaining FROM order_lines;
text
 remaining
-----------
         1
(1 row)

ON UPDATE CASCADE 则把父表主键的变更同步到子表：

sql
CREATE TABLE t_uc (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT fk_uc FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE
);

INSERT INTO t_uc VALUES (1, 2);
-- 父表主键改了，子表跟着改
UPDATE users SET id = 20 WHERE id = 2;
SELECT user_id FROM t_uc;
text
 user_id
---------
      20
(1 row)

CASCADE 的作用范围是递归的：如果 orders 又被第三张表用 CASCADE 引用，删除会一路传下去。

④ SET NULL

SET NULL 把子表引用列置空，因此该列必须允许 NULL：

sql
CREATE TABLE tasks (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  assignee_id bigint,          -- 可空
  title       text   NOT NULL,
  CONSTRAINT fk_tasks_assignee FOREIGN KEY (assignee_id)
    REFERENCES users (id) ON DELETE SET NULL
);

INSERT INTO tasks (assignee_id, title) VALUES (1, '修复登录');
DELETE FROM users WHERE id = 1;
SELECT id, assignee_id, title FROM tasks;
text
 id | assignee_id |   title
----+-------------+-----------
  1 |             | 修复登录
(1 row)

如果列不可空，删除父表会直接失败：

sql
-- 错误！列有 NOT NULL，SET NULL 无法执行
CREATE TABLE bad_setnull (
  id      bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  CONSTRAINT fk_bad_sn FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
);
DELETE FROM users WHERE id = 2;
text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_bad_sn" on table "bad_setnull"
DETAIL:  Key (id)=(2) is still referenced from table "bad_setnull".

⑤ SET DEFAULT

把引用列设为默认值，且该默认值必须在父表中存在，否则删除照样失败：

sql
CREATE TABLE tickets (
  id      bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL DEFAULT 999,      -- 999 这个「未知用户」必须先存在
  subject text   NOT NULL,
  CONSTRAINT fk_tickets_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE SET DEFAULT
);

INSERT INTO users (id, email) VALUES (999, 'unknown@example.com');
INSERT INTO tickets (user_id, subject) VALUES (1, '无法登录');

DELETE FROM users WHERE id = 1;
SELECT id, user_id, subject FROM tickets;
text
 id | user_id |  subject
----+---------+-----------
  1 |     999 | 无法登录
(1 row)

NO ACTION 与 RESTRICT 的真正区别 ​

这是外键里最容易被含糊带过的一点。二者在非延迟约束下行为一致，唯一实质区别是：

NO ACTION 可以被延迟到约束检查时刻；RESTRICT 永远立即检查，无法延迟。

对比项NO ACTIONRESTRICT
默认行为语句末检查立即检查
可否 DEFERRABLE可以不可以
DEFERRABLE INITIALLY DEFERRED 下推迟到事务提交时检查仍然立即报错
报错时机语句结束或事务提交语句执行中
sql
-- 建一个可延迟的 NO ACTION 外键
CREATE TABLE t_defer (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT fk_defer FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
);
sql
-- 同一个事务内：先删父行，再删子行，提交时能通过
BEGIN;
DELETE FROM users WHERE id = 1;
DELETE FROM t_defer WHERE user_id = 1;
COMMIT;
text
BEGIN
DELETE 1
DELETE 1
COMMIT

如果把动作换成 RESTRICT，即使加了 DEFERRABLE 也没用：

sql
-- 错误！RESTRICT 不支持延迟，DELETE 的第一句就会失败
CREATE TABLE t_no_defer (
  id      bigint PRIMARY KEY,
  user_id bigint,
  CONSTRAINT fk_no_defer FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED   -- 延迟声明被忽略
);

BEGIN;
DELETE FROM users WHERE id = 2;
text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_no_defer" on table "t_no_defer"

实践建议：除非你需要延迟检查（循环引用、批量导入乱序），否则用 RESTRICT 表达「禁止删除」的意图更明确，报错也更早。

MATCH 选项：多列外键与 NULL 的陷阱 ​

MATCH 只对多列外键有意义，默认是 MATCH SIMPLE。

模式子表引用列含 NULL 时的行为支持情况
MATCH SIMPLE（默认）任一列为 NULL 就跳过整个外键检查完整支持
MATCH FULL要么全为 NULL（跳过检查），要么全非 NULL（完整检查）；部分 NULL 直接报错完整支持
MATCH PARTIAL允许部分 NULL，只检查非空部分PostgreSQL 未实现

这是最容易踩的陷阱。看默认行为：

sql
CREATE TABLE shipment_items (
  shipment_id bigint  NOT NULL,
  line_no     integer NOT NULL,
  qty         integer NOT NULL,
  CONSTRAINT pk_shipment_items PRIMARY KEY (shipment_id, line_no)
);

CREATE TABLE item_notes (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  shipment_id bigint,
  line_no     integer,
  note        text NOT NULL,
  -- 默认 MATCH SIMPLE
  CONSTRAINT fk_notes_item FOREIGN KEY (shipment_id, line_no)
    REFERENCES shipment_items (shipment_id, line_no)
);

-- 插入两列全 NULL：跳过检查，成功
INSERT INTO item_notes (shipment_id, line_no, note) VALUES (NULL, NULL, '全 NULL');
text
INSERT 0 1
sql
-- 危险！只填一半，另一列为 NULL，外键检查被完全跳过
INSERT INTO item_notes (shipment_id, line_no, note) VALUES (77, NULL, '悬挂引用');
SELECT id, shipment_id, line_no, note FROM item_notes;
text
 id | shipment_id | line_no |   note
----+-------------+---------+----------
  1 |             |         | 全 NULL
  2 |          77 |         | 悬挂引用
(1 row)

第二行 shipment_id = 77 在父表里根本不存在，但因为 line_no 是 NULL，MATCH SIMPLE 认为「这行不构成引用」，直接放过。

改用 MATCH FULL 就能堵住：

sql
CREATE TABLE item_notes_full (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  shipment_id bigint,
  line_no     integer,
  note        text NOT NULL,
  CONSTRAINT fk_notes_full FOREIGN KEY (shipment_id, line_no)
    REFERENCES shipment_items (shipment_id, line_no) MATCH FULL
);

-- 允许：全为 NULL（表示「不关联」）
INSERT INTO item_notes_full (shipment_id, line_no, note) VALUES (NULL, NULL, '全 NULL');

-- 错误！部分 NULL 违反 MATCH FULL
INSERT INTO item_notes_full (shipment_id, line_no, note) VALUES (77, NULL, '半边 NULL');
text
ERROR:  insert or update on table "item_notes_full" violates foreign key constraint "fk_notes_full"
DETAIL:  MATCH FULL does not allow mixing of null and nonnull key values.

MATCH PARTIAL 在 PostgreSQL 里不支持：

sql
-- 错误！PostgreSQL 未实现 MATCH PARTIAL
CREATE TABLE t_mp (
  a bigint, b bigint,
  CONSTRAINT fk_mp FOREIGN KEY (a, b) REFERENCES shipment_items (shipment_id, line_no) MATCH PARTIAL
);
text
ERROR:  MATCH PARTIAL not yet implemented

选择原则：多列外键若允许「部分列可空」的设计，一定要显式写 MATCH FULL；否则请把引用列统一设为 NOT NULL，用单列外键替代。

DEFERRABLE：把检查推到事务末 ​

延迟约束（Deferrable Constraint）允许把外键检查从「语句执行时」推到「事务提交时」。它解决的是顺序问题。

场景一：循环引用

sql
CREATE TABLE employees (
  id        bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name      text   NOT NULL,
  manager_id bigint,
  -- 自引用外键，可延迟到事务末
  CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees (id)
    DEFERRABLE INITIALLY IMMEDIATE
);

-- 因为循环，谁先插都不行；把检查推到提交时
BEGIN;
SET CONSTRAINTS fk_emp_manager DEFERRED;
INSERT INTO employees (id, name, manager_id) VALUES (10, '总监', 11);
INSERT INTO employees (id, name, manager_id) VALUES (11, '经理', 10);
COMMIT;
text
BEGIN
SET CONSTRAINTS
INSERT 0 1
INSERT 0 1
COMMIT

场景二：批量导入（删旧数据 → 插新数据，中间态必然违反）

sql
BEGIN;
SET CONSTRAINTS fk_orders_user DEFERRED;

-- 先整体替换父表数据
DELETE FROM users;
INSERT INTO users (id, email) VALUES (1, 'a@example.com'), (2, 'b@example.com');

-- 子表数据此时已合法，提交时才校验
COMMIT;
text
BEGIN
SET CONSTRAINTS
DELETE 2
INSERT 0 2
COMMIT

三种声明方式与生效范围：

声明默认行为生效范围
NOT DEFERRABLE（默认）立即检查—
DEFERRABLE INITIALLY IMMEDIATE立即检查，但可用 SET CONSTRAINTS ... DEFERRED 切到延迟当前事务
DEFERRABLE INITIALLY DEFERRED默认延迟到提交当前事务，可用 SET CONSTRAINTS ... IMMEDIATE 切回

落地时推荐的折中是 DEFERRABLE INITIALLY IMMEDIATE：默认严格，按需在事务里临时放宽。

sql
-- 事后把已有外键改成可延迟
ALTER TABLE orders ALTER CONSTRAINT fk_orders_user
  DEFERRABLE INITIALLY IMMEDIATE;

也可以在事务内临时切换：

sql
BEGIN;
SET CONSTRAINTS ALL DEFERRED;        -- 本事务内所有可延迟约束都延迟
-- ... 批量操作 ...
COMMIT;

注意 SET CONSTRAINTS 的生效范围只有当前事务（或当前语句），不会持久化。

外键列不会自动建索引（最重要） ​

这条决定了外键在生产环境的性能表现。先建立对照：

sql
-- 造两张表：父表 10 万行，子表 100 万行
CREATE TABLE users (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text   NOT NULL
);

CREATE TABLE orders (
  id      bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  amount  numeric(12,2) NOT NULL,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
);

INSERT INTO users (email)
SELECT 'u' || g || '@example.com' FROM generate_series(1, 100000) AS g;

INSERT INTO orders (user_id, amount)
SELECT (g % 100000) + 1, (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000) AS g;

ANALYZE users;
ANALYZE orders;

没有索引时：父表删除要全表扫子表

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
DELETE FROM users WHERE id = 50000;
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Delete on users (actual time=0.045..0.045 rows=0 loops=1)
   Buffers: shared hit=1 dirtied=1
   ->  Index Scan using users_pkey on users (actual time=0.024..0.026 rows=1 loops=1)
         Index Cond: (id = 50000)
         Buffers: shared hit=1
   ->  Seq Scan on orders (actual time=48.312..48.312 rows=10 loops=1)
         Filter: (user_id = 50000)
         Rows Removed by Filter: 999990
         Buffers: shared hit=12000
 Planning Time: 0.318 ms
 Trigger for constraint fk_orders_user: time=48.335 calls=1
 Execution Time: 48.394 ms

关键行是 Seq Scan on orders ... Rows Removed by Filter: 999990：为了删 1 行父表数据，扫描了子表全部 100 万行（丢掉 99.999 万行），读了 12000 个数据块，耗时 48ms。

补上索引后：

sql
-- 建表后必须补上这一步
CREATE INDEX idx_orders_user_id ON orders (user_id);
ANALYZE orders;
sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
DELETE FROM users WHERE id = 50000;
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Delete on users (actual time=0.043..0.043 rows=0 loops=1)
   Buffers: shared hit=4 dirtied=1
   ->  Index Scan using users_pkey on users (actual time=0.020..0.022 rows=1 loops=1)
         Index Cond: (id = 50000)
         Buffers: shared hit=1
   ->  Index Scan using idx_orders_user_id on orders (actual time=0.020..0.022 rows=10 loops=1)
         Index Cond: (user_id = 50000)
         Buffers: shared hit=3
 Planning Time: 0.291 ms
 Trigger for constraint fk_orders_user: time=0.047 calls=1
 Execution Time: 0.104 ms

从 48ms 降到 0.1ms，缓冲区读从 12000 降到 3。数据量越大差距越夸张：子表上亿行时，没索引的父表删除会从毫秒级变成分钟级。

结论：每个外键的引用列都必须建索引，无论是 ON DELETE 哪种动作。这也是 ON DELETE CASCADE 的隐含前提——级联删除同样要定位子表行。

sql
-- 复合外键也要为整组列建索引
CREATE INDEX idx_shipments_item ON shipments (order_id, line_no);

外键带来的锁行为 ​

外键检查在父表行上加锁。删除或更新父表行时，PostgreSQL 会在该父行上取一个共享的行锁（FOR KEY SHARE 级别），以阻止并发的键变更。

这带来的两个实际后果：

现象原因
删除父表行会短暂锁住它对应的子表行反向检查需要稳定的引用关系
高并发下可能出现死锁两个事务以不同顺序删除/插入父表与子表行

典型死锁场景：事务 A 删父行 id=1 再删父行 id=2，事务 B 反序操作。缓解手段：

sql
-- 批量删除父表行时固定顺序，避免交叉加锁
DELETE FROM users WHERE id IN (1, 2, 3, 4) ORDER BY id;
sql
-- 给删除操作设锁等待上限，快速失败而不是长时间阻塞
SET lock_timeout = '2s';
DELETE FROM users WHERE id = 50000;
RESET lock_timeout;

另一个需要注意的点：ON DELETE CASCADE / SET NULL / SET DEFAULT 的级联操作发生在同一事务内，子表行数极多时会拉长事务、放大锁持有时长。超大范围的级联删除建议改成批次执行。

在线加外键：NOT VALID + VALIDATE CONSTRAINT ​

ADD CONSTRAINT ... FOREIGN KEY 默认会扫描整张子表校验历史数据，期间持有 ACCESS EXCLUSIVE 锁，大表上等同于停服。标准做法是拆成两步：

sql
-- 第 1 步：只对「新增和修改的数据」生效，不校验历史数据（很快）
SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
RESET lock_timeout;
text
ALTER TABLE

此时约束的 convalidated 是 false，但新写入的数据已经开始被检查：

sql
SELECT conname, convalidated, condeferrable
FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';
text
     conname     | convalidated | condeferrable
-----------------+--------------+---------------
 fk_orders_user  | f            | f
(1 row)
sql
-- 第 2 步：校验历史数据。只用 SHARE UPDATE EXCLUSIVE 锁，不阻塞读写
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
text
ALTER TABLE
sql
-- 校验通过后 convalidated 变成 t
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';
text
     conname     | convalidated
-----------------+--------------
 fk_orders_user  | t
(1 row)

如果历史数据里有「悬挂引用」，VALIDATE 会报错并告知具体的键值：

sql
-- 错误！历史数据中存在无效引用
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
text
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_user"
DETAIL:  Key (user_id)=(999999) is not present in table "users".

先修复数据再重试：

sql
-- 找出所有悬挂引用
SELECT o.user_id, count(*)
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL AND u.id IS NULL
GROUP BY o.user_id
ORDER BY count(*) DESC;

-- 修复后重新校验
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

三步在线法完整顺序（索引要最先建，否则 VALIDATE 会退化成全表扫）：

sql
-- ① 先建子表索引（在线）
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

-- ② 挂上未校验的外键（短暂 ACCESS EXCLUSIVE）
SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
RESET lock_timeout;

-- ③ 后台校验历史数据（SHARE UPDATE EXCLUSIVE，不阻塞读写）
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

查外键依赖 ​

方式一：\d 的 "Referenced by" 段

在父表上执行 \d，会列出所有引用它的外键：

sql
\d users
text
                       Table "public.users"
 Column |  Type  | Collation | Nullable |           Default
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated by default as identity
 email  | text   |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "uq_users_email" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_user" FOREIGN KEY (user_id) REFERENCES users(id)

这里有两个信息非常关键：

  • 子表索引没有出现——Referenced by 只列外键约束，不体现「子表引用列是否有索引」。必须自己去子表 \d orders 看。
  • 有 Referenced by 说明这张父表不能随便删，DROP TABLE users 会失败。

方式二：pg_constraint

sql
SELECT
  c.conname                                    AS constraint_name,
  c.conrelid::regclass                         AS child_table,
  pg_get_constraintdef(c.oid)                  AS definition,
  c.confrelid::regclass                        AS parent_table,
  c.confupdtype                                AS on_update,
  c.confdeltype                                AS on_delete,
  c.confmatchtype                              AS match_type,
  c.condeferrable                              AS deferrable,
  c.convalidated                               AS validated
FROM pg_constraint c
WHERE c.contype = 'f'
  AND c.confrelid = 'users'::regclass
ORDER BY c.conrelid::regclass;
text
 constraint_name | child_table |                       definition                        | parent_table | on_update | on_delete | match_type | deferrable | validated
-----------------+-------------+-------------------------------------------------------+--------------+-----------+-----------+------------+------------+-----------
 fk_orders_user  | orders      | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT | users | a         | r         | s          | f          | t
(1 row)

动作代码对照（这些单字符代码在系统表里到处都是）：

字段arcnd
confupdtype / confdeltypeNO ACTIONRESTRICTCASCADESET NULLSET DEFAULT
confmatchtypef = FULLs = SIMPLEp = PARTIAL（未实现）

方式三：information_schema（标准但信息少）

sql
SELECT tc.constraint_name, tc.table_name, kcu.column_name,
       ccu.table_name AS foreign_table, ccu.column_name AS foreign_column
FROM information_schema.table_constraints   AS tc
JOIN information_schema.key_column_usage    AS kcu
  ON kcu.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'orders';

方式四：排查「哪些外键列缺索引」（最实用的运维查询）

sql
-- 找出所有「外键列没有可用索引」的表
SELECT
  c.conrelid::regclass  AS child_table,
  c.conname             AS fk_name,
  pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND i.indisvalid
      -- 外键列是索引列的前缀即可
      AND (i.indkey::int2[])[0:array_length(c.conkey, 1) - 1] = c.conkey
  )
ORDER BY 1, 2;
text
 child_table |        fk_name        |                definition
-------------+-----------------------+------------------------------------------
 tickets     | fk_tickets_user       | FOREIGN KEY (user_id) REFERENCES users(id)
(1 row)

删除被引用的父表行失败的处理流程 ​

看到这个错误时，不要第一反应加 CASCADE：

text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_orders_user" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

按这个顺序处理：

sql
-- 第 1 步：确认是谁在引用
SELECT c.conrelid::regclass AS child_table,
       c.conname,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f' AND c.confrelid = 'users'::regclass;
sql
-- 第 2 步：看清子表里到底有多少行在引用（决定处理策略）
SELECT count(*) FROM orders WHERE user_id = 1;
text
 count
-------
    10
(1 row)
sql
-- 第 3 步：按业务决定
-- 3a. 业务上不允许孤立数据 → 让调用方先处理子表
-- 3b. 子表应从属删除 → 显式先删子表（比 CASCADE 更可控）
BEGIN;
DELETE FROM orders   WHERE user_id = 1;
DELETE FROM users    WHERE id = 1;
COMMIT;

-- 3c. 语义上是「解绑」→ 改外键动作或直接置空
UPDATE orders SET user_id = NULL WHERE user_id = 1;   -- 需要列可空
text
 count
-------
    10
(1 row)
BEGIN
DELETE 10
DELETE 1
COMMIT

如果长期需要「删父行时子行保留但归入默认分组」，可以把动作改成 SET DEFAULT：

sql
-- 需要先确保默认值在父表中存在
INSERT INTO users (id, email) VALUES (999, 'unknown@example.com') ON CONFLICT DO NOTHING;

ALTER TABLE orders DROP CONSTRAINT fk_orders_user;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
  REFERENCES users (id) ON DELETE SET DEFAULT;

ALTER TABLE orders ALTER COLUMN user_id SET DEFAULT 999;

外键 vs 应用层校验 ​

外键不是没有成本的：每次子表写入都要多一次父表查找（虽然有索引），父表删除要反向检查。因此分库分表、超高并发的日志类写入场景常常放弃外键。

对比项数据库外键应用层校验
一致性保障强，多写入路径共用一道防线弱，绕过应用即可破坏
写入性能每次写入多一次父表索引查找无额外开销
并发正确性由数据库保证，无竞态需应用处理竞态（check-then-insert 有窗口）
跨库/分片不支持应用可自行处理
DDL 与迁移迁移顺序受限（先父后子）更灵活
排查成本报错信息明确依赖代码质量
批量导入需要 DEFERRABLE 或调整顺序无限制

一个务实的结论：

  • 单体库、交易类核心数据 → 用外键，一致性收益远大于开销。
  • 分库分表、海量日志 → 不用外键，用应用层 + 定期对账任务兜底。
  • 中间态（单库但写入极高）→ 保留外键但确保索引齐全，并把 NOT VALID + VALIDATE 作为大表上线流程的一部分。

实战示例 ​

场景：为电商系统建立 users → orders → order_lines 三级引用链，覆盖 RESTRICT / CASCADE / SET NULL 三种动作，验证每种动作的行为，并检查索引是否齐全。

第 1 步：清理并建表

sql
DROP TABLE IF EXISTS order_lines, tasks, orders, users CASCADE;

第 2 步：建父表与子表

sql
CREATE TABLE users (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL,
  status     text   NOT NULL DEFAULT 'active',
  CONSTRAINT uq_users_email  UNIQUE (email),
  CONSTRAINT ck_users_status CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE orders (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id    bigint        NOT NULL,
  order_no   text          NOT NULL,
  amount     numeric(12,2) NOT NULL CHECK (amount >= 0),
  status     text          NOT NULL DEFAULT 'pending',
  created_at timestamptz   NOT NULL DEFAULT now(),

  CONSTRAINT uq_orders_no   UNIQUE (order_no),
  -- 禁止删除仍有订单的用户
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE order_lines (
  id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id bigint  NOT NULL,
  sku      text    NOT NULL,
  qty      integer NOT NULL CHECK (qty > 0),
  -- 订单删了，明细一起删
  CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES orders (id)
    ON DELETE CASCADE
);

CREATE TABLE tasks (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  assignee_id bigint,          -- 可空，配合 SET NULL
  title       text NOT NULL,
  -- 用户删了，任务保留但解绑
  CONSTRAINT fk_tasks_user FOREIGN KEY (assignee_id) REFERENCES users (id)
    ON DELETE SET NULL
);

第 3 步：补齐外键列索引（关键一步）

sql
CREATE INDEX idx_orders_user_id  ON orders (user_id);
CREATE INDEX idx_lines_order_id  ON order_lines (order_id);
CREATE INDEX idx_tasks_assignee  ON tasks (assignee_id);

第 4 步：写入数据

sql
INSERT INTO users (email) VALUES
  ('alice@example.com'), ('bob@example.com'), ('carol@example.com');

INSERT INTO orders (user_id, order_no, amount) VALUES
  (1, 'SO-20260924-001', 698.00),
  (1, 'SO-20260924-002', 129.00),
  (2, 'SO-20260924-003', 259.00);

INSERT INTO order_lines (order_id, sku, qty) VALUES
  (1, 'KB-001', 2), (1, 'MS-002', 1), (2, 'MS-002', 3), (3, 'ST-003', 1);

INSERT INTO tasks (assignee_id, title) VALUES
  (1, '处理退货申请'), (NULL, '整理库存');
text
INSERT 0 3
INSERT 0 3
INSERT 0 4
INSERT 0 2

第 5 步：验证外键真的生效

sql
-- 5.1 子表插入了不存在的父键
INSERT INTO orders (user_id, order_no, amount) VALUES (999, 'SO-X', 1.00);
text
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_user"
DETAIL:  Key (user_id)=(999) is not present in table "users".
sql
-- 5.2 删除被引用的父行，RESTRICT 立刻拒绝
DELETE FROM users WHERE id = 1;
text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_orders_user" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".
sql
-- 5.3 CASCADE 级联删除
DELETE FROM orders WHERE id = 1;
SELECT count(*) AS lines_left FROM order_lines WHERE order_id = 1;
text
 count
-------
     0
(1 row)
sql
-- 5.4 SET NULL 解绑
DELETE FROM users WHERE id = 3;   -- carol 只有一个订单，但订单引用了她
text
ERROR:  update or delete on table "users" violates foreign key constraint "fk_orders_user" on table "orders"
DETAIL:  Key (id)=(3) is still referenced from table "orders".
sql
-- 先处理她的订单，再看 tasks 的 SET NULL
DELETE FROM orders WHERE user_id = 3;
DELETE FROM users WHERE id = 3;
SELECT id, assignee_id, title FROM tasks ORDER BY id;
text
 id | assignee_id |     title
----+-------------+----------------
  1 |           1 | 处理退货申请
  2 |             | 整理库存
(1 row)
sql
-- 给 carol 建的 task 不存在，换个例子：删 alice（此时 alice 还有 order id=2）
SELECT id, assignee_id, title FROM tasks WHERE assignee_id = 1;
text
 id | assignee_id |     title
----+-------------+----------------
  1 |           1 | 处理退货申请
(1 row)
sql
-- 完整走一遍 SET NULL：先删订单，再删用户，任务被解绑
BEGIN;
DELETE FROM orders   WHERE user_id = 1;    -- order id=2 及其明细被删
DELETE FROM users    WHERE id = 1;
COMMIT;

SELECT id, assignee_id, title FROM tasks ORDER BY id;
text
 id | assignee_id |     title
----+-------------+----------------
  1 |             | 处理退货申请
  2 |             | 整理库存
(2 rows)

第 6 步：验证外键列索引齐全

sql
SELECT
  c.conrelid::regclass        AS child_table,
  c.conname                   AS fk_name,
  pg_get_constraintdef(c.oid) AS definition,
  EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indisvalid
      AND (i.indkey::int2[])[0:array_length(c.conkey, 1) - 1] = c.conkey
  ) AS has_index
FROM pg_constraint c
WHERE c.contype = 'f'
ORDER BY 1, 2;
text
 child_table |     fk_name      |                          definition                          | has_index
-------------+------------------+--------------------------------------------------------------+-----------
 order_lines | fk_lines_order   | FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE | t
 orders      | fk_orders_user   | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE | t
 tasks       | fk_tasks_user    | FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL | t
(3 rows)

三行全是 has_index = t，达标。

第 7 步：看父表的 "Referenced by"

sql
\d users
text
                                  Table "public.users"
 Column |  Type  | Collation | Nullable |           Default
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated by default as identity
 email  | text   |           | not null |
 status | text   |           | not null | 'active'::text
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "uq_users_email" UNIQUE CONSTRAINT, btree (email)
Check constraints:
    "ck_users_status" CHECK (status = ANY (ARRAY['active'::text, 'disabled'::text]))
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_user" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "tasks" CONSTRAINT "fk_tasks_user" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL

第 8 步：清理

sql
DROP TABLE order_lines, tasks, orders, users CASCADE;

注意事项 ​

  • 外键列不会自动建索引。 这是本节最重要的一条，也是「删一行父表卡几秒」的常见原因。
sql
-- 建完外键后必须补上
CREATE INDEX idx_orders_user_id ON orders (user_id);
  • 引用目标必须是 PRIMARY KEY 或 UNIQUE。 普通列、部分唯一索引都不行。
sql
-- 错误！there is no unique constraint matching given keys for referenced table "categories"
CONSTRAINT fk_bad FOREIGN KEY (cat_id) REFERENCES categories (id)
  • 复合外键只能用表级语法，且引用列的集合必须整体匹配父表的唯一约束。
sql
-- 错误！
CREATE TABLE bad (line_no integer REFERENCES order_items (line_no));
  • MATCH SIMPLE（默认）下，多列外键只要有一列为 NULL，整个外键检查就被跳过，能插入悬挂引用。
sql
-- 危险！shipment_id=77 不存在，但 line_no 为 NULL，检查被跳过
INSERT INTO item_notes (shipment_id, line_no, note) VALUES (77, NULL, '悬挂引用');
  • SET NULL 需要引用列可空。 列上有 NOT NULL 时，删除父行会失败。
sql
-- 错误！Column "user_id" is NOT NULL，SET NULL 无法执行
CONSTRAINT fk_bad FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
  • SET DEFAULT 需要默认值在父表中存在，否则删除父行报错。
sql
-- 危险！999 不存在时，删除父行会报外键错误
ALTER TABLE tickets ALTER COLUMN user_id SET DEFAULT 999;
  • RESTRICT 不支持延迟检查。 即使写了 DEFERRABLE INITIALLY DEFERRED，它仍然立即报错。
sql
-- 错误！延迟声明对 RESTRICT 无效
CONSTRAINT fk_no_defer FOREIGN KEY (...) REFERENCES users (id)
  ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
  • MATCH PARTIAL 在 PostgreSQL 中未实现。
sql
-- 错误！MATCH PARTIAL not yet implemented
FOREIGN KEY (a, b) REFERENCES t (x, y) MATCH PARTIAL
  • ADD CONSTRAINT ... FOREIGN KEY 默认扫全表校验，且持 ACCESS EXCLUSIVE 锁。 大表上线必须用 NOT VALID + VALIDATE CONSTRAINT。
sql
-- 大表不要直接执行
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id);

-- 正确：两步走
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
  • NOT VALID 的约束只拦新数据，不保证历史数据合法。 上线后记得补 VALIDATE。
sql
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'f';
  • CASCADE 的删除是递归的。 三级以上引用链上删一个父行，可能连带删掉大量数据，且全在同一事务内。
sql
-- 危险！可能连锁删除多层数据
DELETE FROM users WHERE id = 1;
  • \d 的 "Referenced by" 不显示子表索引情况。 判断索引是否齐全必须查 pg_index 或去看子表。
sql
\d orders    -- 在子表侧才能看到 idx_orders_user_id
  • 跨库/分片场景外键不可用，因为父表可能在另一个实例上。

最佳实践 ​

  • 每个外键的引用列都建索引，且把「建索引」写进建表脚本。 别指望迁移时补，补的时候往往已经在救火了。
sql
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
-- 紧跟着建索引
CREATE INDEX idx_orders_user_id ON orders (user_id);
  • ON DELETE 动作按业务语义显式指定，别依赖默认值。 默认的 NO ACTION 报错信息在线上容易让人误判。
sql
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
  • 「禁止删除」用 RESTRICT 而非 NO ACTION，报错更早、意图更清晰。
sql
ON DELETE RESTRICT       -- 优于 ON DELETE NO ACTION
  • 多列外键一律显式写 MATCH FULL，否则 NULL 会绕过检查。
sql
CONSTRAINT fk_notes_item FOREIGN KEY (shipment_id, line_no)
  REFERENCES shipment_items (shipment_id, line_no) MATCH FULL
  • 批量导入/循环引用场景用 DEFERRABLE INITIALLY IMMEDIATE：默认严格，需要时在事务里 SET CONSTRAINTS ... DEFERRED。
sql
CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees (id)
  DEFERRABLE INITIALLY IMMEDIATE
  • 大表加外键走三步：先 CREATE INDEX CONCURRENTLY，再 ADD CONSTRAINT ... NOT VALID，最后 VALIDATE CONSTRAINT。校验前先跑一次悬挂引用排查。
sql
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
  REFERENCES users (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
  • DROP TABLE / DROP CONSTRAINT 前先查引用关系，避免 CASCADE 误伤。
sql
SELECT conrelid::regclass, conname FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'users'::regclass;
  • 定期跑一次「无索引外键」巡检，把它加进健康检查。
sql
SELECT c.conrelid::regclass AS child_table, c.conname
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indisvalid
      AND (i.indkey::int2[])[0:array_length(c.conkey, 1) - 1] = c.conkey
  );
  • 给批量删除设 lock_timeout，避免外键锁等待引发的堆积。
sql
SET lock_timeout = '3s';
DELETE FROM users WHERE id IN (1, 2, 3) ORDER BY id;
RESET lock_timeout;
  • 分库分表场景接受「无外键」，但要用应用层校验 + 定期对账任务补齐一致性。这是明确的取舍，不是偷懒。

下一节

继续学习：UNIQUE 唯一约束

参考链接 ​