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 DELETEON 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 数据库中创建 useruser_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 DELETEON 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 |
+----------+---------+----------+

让我们看一下对父表进行 UPDATEDELETE 操作引起的子表的关联操作:

  • 对父表进行 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 DELETEON 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 |
+----------+---------+----------+

让我们看一下对父表进行 UPDATEDELETE 操作的结果:

  • 对父表进行 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 DELETEON 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');

让我们看一下对父表进行 UPDATEDELETE 操作引起的子表的关联操作:

  • 对父表进行 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 ... 语句可以用来删除外键。
  • 自引用外键引用的是当前表自身。这可以实现树形数据结构。