MySQL FOREIGN KEY 外键

在本文中，我们介绍了什么是外键、外键的规则以及如何在 MySQL 中使用外键。

在关系数据库中，外键用来定义两个实体之间的约束关系。外键对保证数据的完整性很有用。

什么是外键

外键相对于主键而言，用来引用其他表。外键通过子表的一个或多个列对应到父表的主键或唯一键值，将子表的行和父表行建立起关联关系。

让我们看一下 Sakila 示例数据库中的 country 表和 city 表。下面是它们的关系图：

country 和 city 关系

以下是 country 表中的部分数据：

SELECT *
FROM country
WHERE country_id = 23;

+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|         23 | China   | 2006-02-15 04:44:00 |
+------------+---------+---------------------+
1 row in set (0.05 sec)

以下是 city 表中的部分数据：

SELECT *
FROM city
WHERE country_id = 23;

+---------+---------------+------------+---------------------+
| city_id | city          | country_id | last_update         |
+---------+---------------+------------+---------------------+
|      46 | Baicheng      |         23 | 2006-02-15 04:45:25 |
|      47 | Baiyin        |         23 | 2006-02-15 04:45:25 |
|      80 | Binzhou       |         23 | 2006-02-15 04:45:25 |
|     109 | Changzhou     |         23 | 2006-02-15 04:45:25 |
|     136 | Datong        |         23 | 2006-02-15 04:45:25 |
...
53 rows in set (0.00 sec)

由此我们看出，country 表和 city 表是一对多的关系。一个国家中可以有多个城市，一个城市只能位于一个国家。

如果一个国家已经有了城市，那么这个你就不能轻易的从 country 表删除国家，否则就会造成这个城市数据的不完整。你也不能为一个城市设定一个不存在的 country_id，否则这个城市数据就是错误的。

外键约束能保证数据的完整和正确。

外键的语法

让我们看一下 city 表定义的外键约束：

SHOW CREATE TABLE city\G

*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `city_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

注意其中的部分：

CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

这里定义了一个外键：

  • 位于 CONSTRAINT 关键字之后的 fk_city_country 是外键的名字。它是可选的。

  • 位于 FOREIGN KEY 关键字之后的是作为外键的列名。

  • 位于 REFERENCES 关键字之后的是被引用的表和列。

  • ON DELETE 和 ON UPDATE 指定了删除或更新被引用的表中的数据时要采取的约束策略。你可以使用以下 3 个策略中的一个：

    • CASCADE：如果被引用的表中的一行被删除或更新，该表中匹配行的值会自动删除或更新。
    • SET NULL：如果被引用的表中的一行被删除或更新，该表中匹配行的值设置为 NULL。
    • RESTRICT: 如果被引用的表中的一行在该表中有匹配的行，试图删除或更新被引用的表中行时会引发 MySQL 错误。这是默认的策略。

通常，外键所属的表被称作子表，被外键引用的表被称作父表。

添加外键语法

如果建表的时候没有定义外键，你也可以后来通过以下语法添加外键：

ALTER TABLE child_table_name
ADD [CONSTRAINT foreign_key_name]
  FOREIGN KEY (column))
  REFERENCES parent_table_name (column);

这里：

  • 使用 ALTER TABLE 语句修改表的定义。
  • 使用 ADD [CONSTRAINT foreign_key_name] 添加一个名为 foreign_key_name 的约束。[CONSTRAINT foreign_key_name] 是可选的。
  • 使用 FOREIGN KEY (column)) REFERENCES parent_table_name (column) 定义了外键。

删除外键语法

要删除表上外键，可以采用下面的两种语法之一：

  • ALTER TABLE table_name
DROP FOREIGN KEY foreign_key_name;

  • ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

这里：

  • 使用 ALTER TABLE 语句修改表的定义。
  • DROP FOREIGN KEY 后面指定外键名，也就是约束名。
  • DROP CONSTRAINT 后面指定约束名。它可以通过名字删除任何约束，并不仅仅是外键。

FOREIGN KEY 实例

以下实例将在 testdb 数据库中创建 user 和 user_hobby 两个表。其中，user_hobby 表中使用外键引用 user 表。下面先创建 user 表，user_hobby 表将在后面的实例中根据各自的情况再创建。请按照如下步骤执行：

  1. 使用 root 用户登录 MySQL 数据库：

    mysql -u root -p

    根据提示输入 root 用户的密码。

    注意：您也可以使用其他任何具有相应的数据库权限的用户登录。

  2. 使用以下语句选择 testdb 数据库：

    USE testdb;

    如果还未创建数据库，请先运行如下语句：

    CREATE DATABASE testdb;

  3. 使用以下语句创建 user 表：

    CREATE TABLE `user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_id`));

  4. 插入几个测试数据

    INSERT INTO user (user_id, name)
VALUES (1, "Tim"), (2, "Lucy");

至此，我们创建了 user 表。

CASCADE 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 CASCADE 策略：

  • 当父表的行被删除的时候，子表中匹配的行也会被删除。
  • 当父表的行的键值更新的时候，子表中匹配的行的字段也会被更新。

使用以下 SQL 创建 user_hobby 表，它的外键采用 CASCADE 策略。

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

向两个表中插入数据：

DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

此时 user_hobby 表中的数据：

+----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |       1 | Football |
|        2 |       1 | Swimming |
+----------+---------+----------+

让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作：

  • 对父表进行 UPDATE 操作

    我们将父表 user 中的键 user_id 的值从 1 修改为 100：

    UPDATE user
SET user_id = 100
WHERE user_id = 1;

    此时 user_hobby 表中的数据：

    +----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |     100 | Football |
|        2 |     100 | Swimming |
+----------+---------+----------+
2 rows in set (0.00 sec)

    我们发现，user_hobby 表中与 user 表中 user_id = 1 匹配的的那些行的列的值也被修改为 100。

  • 对父表进行 DELETE 操作

    DELETE FROM user
WHERE user_id = 100;

    此时 user_hobby 表中的数据：

    Empty set (0.00 sec)

    我们发现，user_hobby 表中与 user 表中 user_id = 100 匹配的那些行都被删除了。

RESTRICT 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 RESTRICT 策略：

  • MySQL 禁止删除父表中与子表匹配的行。
  • MySQL 禁止删除父表中与子表匹配的行的键的值。

使用以下 SQL 创建 user_hobby 表，它的外键采用 RESTRICT 策略。

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);

向两个表中插入数据：

DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

此时 user_hobby 表中的数据：

+----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |       1 | Football |
|        2 |       1 | Swimming |
+----------+---------+----------+

让我们看一下对父表进行 UPDATE 和 DELETE 操作的结果：

  • 对父表进行 UPDATE 操作

    UPDATE user
SET user_id = 100
WHERE user_id = 1;

    MySQL 服务器返回了如下的错误：

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_hobby`, CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

  • 对父表进行 DELETE 操作

    DELETE FROM user
WHERE user_id = 1;

    MySQL 服务器返回了如下的错误：

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_hobby`, CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

SET NULL 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 SET NULL 策略：

  • 当父表的行被删除的时候，子表中匹配的行的列的值被设置为 NULL。
  • 当父表的行的键值被更新的时候，子表中匹配的行的列的值被设置为 NULL。

使用以下 SQL 创建 user_hobby 表，它的外键采用 SET NULL 策略。

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT,
  `hobby` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`hobby_id`),
  CONSTRAINT `fk_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE SET NULL
    ON UPDATE SET NULL);

向两个表中插入数据：

DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

让我们看一下对父表进行 UPDATE 和 DELETE 操作引起的子表的关联操作：

  • 对父表进行 UPDATE 操作

    UPDATE user
SET user_id = 100
WHERE user_id = 1;

    此时 user_hobby 表中的数据：

    +----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |    NULL | Football |
|        2 |    NULL | Swimming |
+----------+---------+----------+
2 rows in set (0.00 sec)

    更新父表中的 user_id = 1 的行的 user_id 列的值后，user_hobby 表中那些 user_id = 1 的行的 user_id 列的值被设置为 NULL。

  • 对父表进行 DELETE 操作

    由于上面实例将表的数据修改了，我们重新初始化两个表的数据：

    DELETE FROM user;
DELETE FROM user_hobby;
INSERT INTO user (user_id, name)
VALUES (1, "Tim");
INSERT INTO user_hobby (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

    DELETE FROM user
WHERE user_id = 1;

    此时 user_hobby 表中的数据：

    +----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |    NULL | Football |
|        2 |    NULL | Swimming |
+----------+---------+----------+
2 rows in set (0.00 sec)

    删除父表中的 user_id = 1 的行后，user_hobby 表中那些 user_id = 1 的行的 user_id 列的值被设置为 NULL。

自引用外键

有时，子表和父表可能是同一个表。这种表中的外键被称为自引用外键。

通常，自引用外键定义在表示树形数据结构的表中。比如一个代表分类的表：

CREATE TABLE category (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(45),
  parent_category_id INT,
  CONSTRAINT fk_category FOREIGN KEY (parent_category_id)
    REFERENCES category (category_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

在这个表中，parent_category_id 列是一个外键。它引用了 category 表的 category_id 列。

这个表实现了一个无限层级的分类树。一个分类可以有多个子分类，一个子分类可以有 0 个或者 1 个父类；

启用或禁用外键约束

要禁用外键约束，请使用以下语句：

SET foreign_key_checks = 0;

要启用外键约束，请使用以下语句：

SET foreign_key_checks = 1;

禁用外键约束在批量导入数据的时候很有用。

结论

在本文中，我们介绍了什么是外键、外键的规则以及如何在 MySQL 中使用外键。以下是本文的要点内容：

  • 外键用来定义两个实体之间的约束关系。外键对保证数据的完整性很有帮助。
  • 定义外键的表被称作子表，被外键引用的表被称作父表。
  • 外键引用的是父表的主键或者唯一键值列。
  • ALTER TABLE ... ADD FOREIGN KEY ... 语句可以用来添加外键。
  • ALTER TABLE ... DROP FOREIGN KEY ... 语句可以用来删除外键。
  • 自引用外键引用的是当前表自身。这可以实现树形数据结构。