mysql菜鸟教程
8.2 外键（FOREIGN KEY）：表间关系
在现实世界中，数据不是孤立存在的。比如，一个学生属于某个班级，一个订单属于某个客户，一篇文章属于某个作者。这种“属于”的关系在数据库中就是通过外键（FOREIGN KEY） 来实现的。外键是连接两张表的桥梁，它保证了数据之间的参照完整性。
一、什么是外键？
外键 是一个表中的一列（或一组列），它的值必须引用另一个表中的主键或唯一键。简单来说，外键告诉数据库：“这张表的这一列的值，必须在另一张表的对应列中存在”。
例如，在 students 表中，我们有一个 class_id 列，它引用 classes 表的 id 主键。这样，每个学生都被分配到一个真实存在的班级。
为什么需要外键？
-
保证数据一致性：防止插入无效的引用（比如给一个学生分配一个不存在的班级）。
-
防止孤立数据：当删除被引用的数据时，可以自动处理相关的子记录（例如，删除班级时，自动删除该班级的所有学生，或将其置空）。
-
清晰表达关系：数据库结构能够直观反映业务逻辑，便于理解和维护。
二、外键的创建语法
外键可以在创建表时定义，也可以使用 ALTER TABLE 后期添加。
1. 创建表时定义外键
CREATE TABLE 子表名 (
列定义,
...
[CONSTRAINT 外键名称] FOREIGN KEY (子表外键列) REFERENCES 父表名(父表主键列)
[ON DELETE 行为]
[ON UPDATE 行为]
);
示例：班级表和学生表
-- 先创建父表：班级表
CREATE TABLE classes (
id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(50) NOT NULL,
grade INT
);
-- 创建子表：学生表，包含外键 class_id
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age TINYINT,
class_id INT,
CONSTRAINT fk_student_class -- 为外键起个名字（便于管理）
FOREIGN KEY (class_id)
REFERENCES classes(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
2. 使用 ALTER TABLE 添加外键
如果表已经存在，可以这样添加外键（前提是已有数据满足外键约束）：
ALTER TABLE students
ADD CONSTRAINT fk_student_class
FOREIGN KEY (class_id) REFERENCES classes(id)
ON DELETE SET NULL
ON UPDATE CASCADE;
3. 删除外键
ALTER TABLE students DROP FOREIGN KEY fk_student_class;
三、外键约束的行为：ON DELETE 和 ON UPDATE
当父表中的被引用行发生删除或更新时，我们可以指定子表该如何应对。MySQL 支持以下四种行为：
行为
含义
适用场景
CASCADE
级联操作。父表删除/更新时，子表中匹配的行也自动删除/更新。
删除订单时自动删除订单明细；更新客户ID时自动更新订单中的客户ID。
SET NULL
置空。父表删除/更新时，将子表中的外键列设为 NULL（要求该列允许 NULL）。
删除班级后，将该班学生的班级ID设为 NULL（保留学生记录）。
RESTRICT
限制。如果子表中存在引用，则拒绝父表的删除/更新操作。
默认行为（如果未指定）。防止删除仍有学生的班级。
NO ACTION
与 RESTRICT 类似，但在某些数据库中略有差异（MySQL 中同 RESTRICT）。
同上。
注意：如果使用 SET NULL，外键列必须允许 NULL 值，否则操作会失败。
四、实战案例：订单与客户
让我们通过一个更贴近业务的例子来理解外键的应用。
场景
-
一个客户可以拥有多个订单（一对多关系）。
-
订单必须属于一个已存在的客户。
-
如果客户被删除，应该如何处理其订单？（这里我们选择：删除客户时，同时删除其所有订单）
建表
-- 客户表
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20)
);
-- 订单表，包含外键 customer_id
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2) NOT NULL,
customer_id INT NOT NULL,
CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
插入数据测试
-- 插入客户
INSERT INTO customers (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com');
-- 插入订单
INSERT INTO orders (amount, customer_id) VALUES
(299.00, 1),
(59.90, 1),
(1200.00, 2);
-- 尝试插入一个不存在的客户ID（应该失败）
INSERT INTO orders (amount, customer_id) VALUES (100.00, 99);
-- 错误：Cannot add or update a child row: a foreign key constraint fails
删除客户，观察级联效果
-- 删除客户“张三”（id=1）
DELETE FROM customers WHERE id = 1;
-- 查询 orders 表，发现张三的订单也被自动删除了
SELECT * FROM orders; -- 只剩下 id=2 的订单（属于李四）
如果使用 SET NULL 会怎样？
假设我们将外键设为 ON DELETE SET NULL，并允许 customer_id 为 NULL：
-- 修改外键行为
ALTER TABLE orders DROP FOREIGN KEY fk_order_customer;
ALTER TABLE orders ADD CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE SET NULL;
-- 注意：需要先让 customer_id 允许 NULL
ALTER TABLE orders MODIFY customer_id INT NULL;
-- 再删除客户“李四”
DELETE FROM customers WHERE id = 2;
-- 查询 orders，发现原先属于李四的订单的 customer_id 变成了 NULL
五、外键使用注意事项
-
数据类型必须匹配：外键列与引用的主键列必须数据类型一致（长度、符号等也要一致）。
-
引用的列必须是主键或唯一键：通常引用主键，但也可以引用唯一键（UNIQUE）。
-
存储引擎限制：MySQL 中只有 InnoDB 存储引擎支持外键约束（MyISAM 不支持，但可以定义语法，实际不生效）。
-
性能影响：外键约束会带来额外的检查开销，但在保证数据完整性方面非常值得。对高并发写入频繁的表，可能需要权衡。
-
循环依赖：应避免两个表互相作为外键（鸡生蛋蛋生鸡问题），这会很难插入数据。
-
外键名：建议给外键起一个有意义的名字（如 fk_订单_客户），便于管理和错误排查。
六、如何查看外键关系？
可以使用以下 SQL 查看某个数据库中的所有外键：
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = '数据库名'
AND REFERENCED_TABLE_NAME IS NOT NULL;
七、外键的最佳实践
-
为每个表设计主键：这是建立外键关系的基础。
-
谨慎选择约束行为：根据业务逻辑选择 CASCADE、SET NULL 或 RESTRICT，避免意外删除大量数据。
-
命名规范：外键名建议以 fk_ 开头，后跟子表名和父表名，如 fk_orders_customers。
-
备份与测试：在生产环境添加或修改外键前，务必在测试环境验证，并备份数据。
-
考虑性能：如果表非常大且写操作频繁，可考虑在应用层实现参照完整性，以换取更高性能。
八、综合练习
设计一个“博客系统”的数据库，包含三张表：
-
users（用户表）：id（主键）、username、email
-
posts（文章表）：id（主键）、title、content、user_id（外键引用 users.id，级联删除）
-
comments（评论表）：id（主键）、content、post_id（外键引用 posts.id，级联删除）、user_id（外键引用 users.id，设置 NULL）
要求：
-
写出建表语句，包含合适的外键约束。
-
插入几条测试数据。
-
尝试删除一个用户，观察其文章和评论的变化。
九、总结
-
外键 是表与表之间的“法律条约”，它强制子表的引用必须在父表中存在。
-
外键保证了数据的参照完整性，防止了“孤儿数据”的出现。
-
通过 ON DELETE 和 ON UPDATE 可以定义父表变化时的自动行为。
-
外键的设计需要结合实际业务，选择适当的约束策略。
-
虽然外键会带来一些性能开销，但对于保证数据一致性来说，它是最简单可靠的方案。
发表评论
所有评论