结构化查询语言(Structured Query Language): 外键

外键（Foreign Keys）

外键 (Foreign Keys, FK) 定义了一个表（子表）到另一个表（父表）的定向引用。这种引用要求两个表中相关列的值保持一致。它将子表中的一行关联到父表中的一行，而父表中的一行可以被多个子表行引用。

例如，有一个表 department 包含列 id，另一个表 employee 包含列 dept_id。为了将员工分配到特定部门，可以将部门的 id 存储在员工的 dept_id 中。尽管这可以在没有外键定义的情况下实现，但人们通常有两个额外的需求：

员工只能分配到实际存在的部门。
如果某个部门有员工分配给它，则不能删除该部门。

外键的主要作用就是保证这两个需求，从而防止“孤儿记录”的产生。

外键与联接的区别

在关系数据库管理系统 (RDBMS) 中，值相同的行可以通过联接（JOIN）关联在一起。虽然外键和联接概念相似，但存在以下重要区别：

方向性：外键有方向，必须明确哪个表是子表，哪个是父表。
定义方式：联接需要在每个 DML 语句中明确表达，而外键则是表定义的一部分，所有 DML 操作会自动考虑外键约束。

外键语法

-- 在 CREATE TABLE 中定义外键
CREATE TABLE <table_name> (
  ...
  CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES <parent_table_name> (<other_column_name>)
);

-- 在 ALTER TABLE 中添加或删除外键
ALTER TABLE <table_name> ADD  CONSTRAINT <constraint_name> ... ; -- 添加外键
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;      -- 删除外键

规则：

外键可以在表创建时定义（CREATE TABLE），也可以通过 ALTER TABLE 添加。
外键约束属于子表的定义。
子表中允许某些行不对应任何父表行（即列值为 NULL）。如果要避免这种情况，可以将列定义为 NOT NULL。
外键约束会影响父表：如果父表中有被子表引用的行，则不能删除这些行。
父表必须先存在。
父表中的引用列必须是其主键或唯一键。
同一张表既可以是子表，也可以是父表。
一个表可以包含多个外键约束。

示例：部门与员工的 1:m 关系

-- 父表：DEPARTMENT
CREATE TABLE department (
  id         DECIMAL,
  dept_no    CHAR(10),
  dept_name  VARCHAR(100),
  CONSTRAINT dept_pk PRIMARY KEY (id)
);

-- 子表：EMPLOYEE
CREATE TABLE employee (
  id         DECIMAL,
  emp_name   VARCHAR(100),
  dept_id    DECIMAL,
  CONSTRAINT emp_pk PRIMARY KEY (id),
  CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES department(id)
);

-- 插入操作示例
-- 失败：因为 department 表中尚无 id 为 10 的记录
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);

-- 插入部门后成功
INSERT INTO department (id, dept_no, dept_name) VALUES (10, 'D10', 'E-Bike Development');
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (1, 'Mike Baker', 10);

-- 一个部门可以有多个员工
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (2, 'Elenore McNeal', 10);
INSERT INTO employee   (id, emp_name, dept_id)  VALUES (3, 'Ted Walker', 10);

-- 删除操作失败：因为该部门仍有员工
DELETE FROM department WHERE dept_name = 'E-Bike Development';

这种建模方式表示层级树结构，其中一个或多个子节点（行）属于一个父节点（行）。这种关系被称为 1:m 关系。

n:m 关系

现实中还有一种 n:m 关系，即一个对象（行）可以属于多个其他对象（行）。例如，一个人可以有多个爱好，而多个不同的人可以有相同的爱好。这种情况可以通过一个“中间表”来实现。

示例：两张表 t1 和 t2 的 n:m 关系

-- 父表 t1
CREATE TABLE t1 (
  id               DECIMAL,
  name             VARCHAR(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

-- 父表 t2
CREATE TABLE t2 (
  id               DECIMAL,
  name             VARCHAR(50),
  CONSTRAINT t2_pk PRIMARY KEY (id)
);

-- 中间表 t1_t2
CREATE TABLE t1_t2 (
  id               DECIMAL,
  t1_id            DECIMAL,
  t2_id            DECIMAL,
  CONSTRAINT t1_t2_pk       PRIMARY KEY (id),       -- 中间表的主键
  CONSTRAINT t1_t2_unique   UNIQUE (t1_id, t2_id),  -- 每对关系只能出现一次
  CONSTRAINT t1_t2_fk_1     FOREIGN KEY (t1_id) REFERENCES t1(id), -- 外键指向 t1
  CONSTRAINT t1_t2_fk_2     FOREIGN KEY (t2_id) REFERENCES t2(id)  -- 外键指向 t2
);

在这种设计中，中间表 t1_t2 是两个父表 t1 和 t2 的子表，同时这两个父表在逻辑上处于相同层级。

ON DELETE / ON UPDATE

外键（Foreign Key）定义的默认行为是不允许删除父表中有子表引用的行。但 SQL 标准提供了五种选项来灵活处理这种父子表之间的关系。这些选项通过在外键约束中扩展定义来实现：

ON DELETE 选项

ON DELETE CASCADE: 如果父表的一行被删除，与之匹配的子表行也会被删除。
ON DELETE SET NULL: 如果父表的一行被删除，子表中所有匹配行的引用列会被设置为 NULL。
ON DELETE SET DEFAULT: 如果父表的一行被删除，子表中所有匹配行的引用列会被设置为该列的默认值。
ON DELETE RESTRICT: 如果父表的一行有匹配的子表行，则禁止删除父表行。检查约束的时间点可以推迟到事务提交（COMMIT）。
ON DELETE NO ACTION: 默认行为，禁止删除父表行，无论是否延迟检查。

ON UPDATE 选项

这些选项与 ON DELETE 类似，但针对的是父表中被引用列的更新：

ON UPDATE CASCADE: 当父表中被引用列的值改变时，子表中所有匹配行的引用列值也随之改变。
ON UPDATE SET NULL: 父表中被引用列的值改变时，子表中所有匹配行的引用列值被设置为 NULL。
ON UPDATE SET DEFAULT: 父表中被引用列的值改变时，子表中所有匹配行的引用列值被设置为默认值。
ON UPDATE RESTRICT: 如果父表行有匹配的子表行，禁止更新父表行。检查可以推迟到 COMMIT。
ON UPDATE NO ACTION: 默认行为，禁止更新父表行，无论是否延迟检查。

如果没有指定 ON DELETE 或 ON UPDATE，默认采用 NO ACTION 行为。

示例

CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
  ...
);

提示：

更新主键的行为是有争议的。
并非所有数据库管理系统（DBMS）都支持所有选项。

IMMEDIATE / DEFERRED

SQL 提供了选项来控制外键约束的检查时间点：

默认情况下，每次执行 UPDATE 或 DELETE 时立即检查外键约束。
使用延迟（DEFERRED）选项，外键约束的检查可以推迟到事务提交时（COMMIT）。

在约束定义中，通过关键字 [NOT] DEFERRABLE 和 INITIALLY IMMEDIATE（默认）或 INITIALLY DEFERRED 指定。

示例：

CREATE TABLE t1_t2 (
  ...
  CONSTRAINT t1_t2_fk_1 FOREIGN KEY (t1_id) REFERENCES t1(id)
                        ON UPDATE CASCADE  DEFERRABLE INITIALLY IMMEDIATE
                        ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
  ...
);

提示：

MySQL 不支持 DEFERRABLE 选项，但可以通过 SET foreign_key_checks = 0/1; 动态启用或禁用外键检查。

鸡蛋与鸡问题（Chicken-Egg Problem）

在某些应用中可能存在循环依赖。例如：

表 team 包含列 team_leader（引用表 player）。
表 player 包含列 team_id（引用表 team）。

初始定义：

CREATE TABLE team (
  id               DECIMAL,
  team_name        VARCHAR(50),
  team_leader      DECIMAL,      -- 引用 player 表的 id
  CONSTRAINT team_pk             PRIMARY KEY (id)
);

CREATE TABLE player (
  id               DECIMAL,
  player_name      VARCHAR(50),
  team_id          DECIMAL,      -- 引用 team 表的 id
  CONSTRAINT player_pk           PRIMARY KEY (id)
);

-- 添加外键约束
ALTER TABLE team   ADD CONSTRAINT team_fk   FOREIGN KEY (team_leader) REFERENCES player(id);
ALTER TABLE player ADD CONSTRAINT player_fk FOREIGN KEY (team_id)     REFERENCES team(id);

问题： 插入 team 的第一行需要 player 的数据，但插入 player 的第一行又需要 team 的数据。

解决方法： 使用延迟检查（DEFER）：

-- 删除之前的约束
ALTER TABLE team   DROP CONSTRAINT team_fk;
ALTER TABLE player DROP CONSTRAINT player_fk;

-- 重新定义外键，设置为延迟检查
ALTER TABLE team   ADD  CONSTRAINT team_fk   
                        FOREIGN KEY (team_leader) REFERENCES player(id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE player ADD  CONSTRAINT player_fk
                        FOREIGN KEY (team_id)     REFERENCES team(id)   DEFERRABLE INITIALLY DEFERRED;

-- 插入数据顺序不受限制
INSERT INTO team   (id, team_name,   team_leader) VALUES (1, 'Wild Tigers',  1);
INSERT INTO player (id, player_name, team_id)     VALUES (1, 'Johnny Crash', 1);

-- 直到提交时检查外键
COMMIT;

DROP TABLE / TRUNCATE TABLE 的外键限制

如果一个表被外键引用：

不能删除（DROP TABLE）。
不能截断（TRUNCATE TABLE），即使没有子表实际引用父表中的任何行。

解决方法：

必须先删除外键约束。

提示： 某些数据库支持 DISABLE / ENABLE 命令来临时禁用外键约束。

最后修改: 2025年01月28日星期二 13:55