MySQL 外键基本指南（通过例子）

摘要：在本教程中，您将了解MySQL 外键以及如何创建、删除和禁用外键约束。

MySQL外键简介

外键是一个表中的一列或一组列，链接到另一个表中的一列或一组列。外键对相关表中的数据施加约束，这使得 MySQL 能够保持引用完整性。

让我们看一下示例数据库中的以下customers和orders表。

在此图中，每个客户可以有零个或多个订单，并且每个订单属于一个客户。

customers表和orders表之间是一对多的关系。这种关系是通过orders表中的customerNumber列指定的外键建立的。

orders表中的customerNumber列链接到customers表中的customerNumber主键列。

customers表称为父表或引用表， orders表称为子表或引用表。

通常，子表的外键列通常引用父表的主键列。

一张表可以有多个外键，其中每个外键引用不同父表的主键。

一旦外键约束到位，子表中的外键列必须在父表的父键列中具有相应的行，或者这些外键列中的值必须为NULL （请参阅下面的SET NULL操作示例）。

例如， orders表中的每一行都有一个存在于customers表的customerNumber列中的customerNumber 。 orders表中的多行可以具有相同的customerNumber 。

自引用外键

有时，子表和父表可能引用同一个表。在这种情况下，外键引用回同一表中的主键。

请参阅示例数据库中的以下employees表。

reportTo列是一个外键，它引用employeeNumber列，该列是employees表的主键。

这种关系允许employees表存储员工和经理之间的报告结构。每个员工向零个或一个员工汇报，一个员工可以有零个或多个下属。

reportTo列上的外键称为递归外键或自引用外键。

MySQL `FOREIGN KEY`语法

以下是在CREATE TABLE或ALTER TABLE语句中定义外键约束的基本语法：

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_name, ...)
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，在CONSTRAINT关键字后指定要创建的外键约束的名称。如果省略约束名称，MySQL 会自动生成外键约束的名称。

其次，在FOREIGN KEY关键字之后指定以逗号分隔的外键列的列表。外键名称也是可选的，如果您跳过它，则会自动生成。

第三，指定父表，后跟外键列引用的逗号分隔列的列表。

最后，指定外键如何通过使用ON DELETE和ON UPDATE子句来维护子表和父表之间的引用完整性。 reference_option确定当父键列中的值被删除（ ON DELETE ）或更新（ ON UPDATE ）时MySQL 将采取的操作。

MySQL 有五个引用选项： CASCADE 、 SET NULL 、 NO ACTION 、 RESTRICT和SET DEFAULT 。

CASCADE ：如果父表中的一行被删除或更新，子表中匹配行的值将自动删除或更新。
SET NULL ：如果删除或更新父表中的行，则子表中的外键列（或多列）的值将设置为NULL 。
RESTRICT ：如果父表中的行在子表中具有匹配的行，则 MySQL 拒绝删除或更新父表中的行。
NO ACTION ：与RESTRICT相同。
SET DEFAULT ：被 MySQL 解析器识别。但是，此操作会被 InnoDB 和 NDB 表拒绝。

事实上，MySQL 完全支持三种操作： RESTRICT 、 CASCADE和SET NULL 。

如果未指定ON DELETE和ON UPDATE子句，则默认操作为RESTRICT 。

MySQL `FOREIGN KEY`示例

让我们创建一个名为fkdemo的新数据库来进行演示。

CREATE DATABASE fkdemo;

USE fkdemo;Code language: SQL (Structured Query Language) (sql)

`RESTRICT` & `NO ACTION`行动

在fkdemo数据库中，创建两个表categories和products ：

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;Code language: SQL (Structured Query Language) (sql)

products表中的categoryId是引用categories表中categoryId列的外键列。

因为我们没有指定任何ON UPDATE和ON DELETE子句，所以更新和删除操作的默认操作都是RESTRICT 。

以下步骤说明了RESTRICT操作。

1）在categories表中插入两行：

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');Code language: SQL (Structured Query Language) (sql)

2）从categories表中选择数据：

SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)

3）在products表中插入新行：

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);Code language: SQL (Structured Query Language) (sql)

它之所以有效，是因为categories表中存在categoryId 1。

4) 尝试向products表中插入一个新行，其中categoryId值在categories表中不存在：

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);Code language: SQL (Structured Query Language) (sql)

MySQL 发出以下错误：

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)

5) 将categories表中的categoryId列中的值更新为100 ：

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;
Code language: SQL (Structured Query Language) (sql)

MySQL 发出此错误：

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Code language: JavaScript (javascript)

由于RESTRICT选项，您无法删除或更新categoryId 1 ，因为它由products表中的productId 1引用。

`CASCADE`行动

这些步骤说明了ON UPDATE CASCADE和ON DELETE CASCADE操作的工作原理。

1）删除products表：

DROP TABLE products;Code language: SQL (Structured Query Language) (sql)

2) 使用外键的ON UPDATE CASCADE和ON DELETE CASCADE选项创建products表：

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;Code language: SQL (Structured Query Language) (sql)

3）在products表中插入四行：

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);
Code language: SQL (Structured Query Language) (sql)

4）从products表中选择数据：

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

5) 将categories表中的categoryId 1更新为100：

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;Code language: SQL (Structured Query Language) (sql)

6）验证更新：

SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)

7）从products表中获取数据：

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

正如您所看到的，由于ON UPDATE CASCADE操作， products表的categoryId列中值为1的两行已自动更新为100 。

8) 从categories表中删除categoryId 2：

DELETE FROM categories
WHERE categoryId = 2;
Code language: SQL (Structured Query Language) (sql)

9）验证删除：

SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)

10）查看products表：

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

由于ON DELETE CASCADE操作， products表中categoryId为 2 的所有产品都被自动删除。

`SET NULL`操作

这些步骤说明了ON UPDATE SET NULL和ON DELETE SET NULL操作的工作原理。

1) 删除categories和products表：

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;
Code language: SQL (Structured Query Language) (sql)

2）创建categories和products表：

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
        ON UPDATE SET NULL
        ON DELETE SET NULL 
)ENGINE=INNODB;
Code language: SQL (Structured Query Language) (sql)

products表中的外键更改为ON UPDATE SET NULL和ON DELETE SET NULL选项。

3) 将行插入categories表中：

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');
Code language: SQL (Structured Query Language) (sql)

4) 将行插入products表中：

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);
Code language: SQL (Structured Query Language) (sql)

5) 将categories表中的categoryId从1更新为100：

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;Code language: SQL (Structured Query Language) (sql)

6）验证更新：

SELECT * FROM categories;Code language: SQL (Structured Query Language) (sql)

7）从products表中选择数据：

由于ON UPDATE SET NULL操作， products表中categoryId为 1 的行被自动设置为NULL 。

8) 从categories表中删除categoryId 2：

DELETE FROM categories 
WHERE categoryId = 2;Code language: SQL (Structured Query Language) (sql)

9）查看products表：

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

由于ON DELETE SET NULL操作， products表中categoryId categoryId为 2 的行的类别 ID 列中的值自动设置为NULL 。

删除 MySQL 外键约束

要删除外键约束，请使用ALTER TABLE语句：

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，在ALTER TABLE关键字之后指定要从中删除外键的表的名称。
其次，在DROP FOREIGN KEY关键字后指定约束名称。

请注意， constraint_name是您在表中创建或添加外键约束时指定的外键约束的名称。

要获取表的生成约束名称，请使用SHOW CREATE TABLE语句：

SHOW CREATE TABLE table_name;Code language: SQL (Structured Query Language) (sql)

例如，要查看products表的外键，请使用以下语句：

SHOW CREATE TABLE products;Code language: SQL (Structured Query Language) (sql)

以下是该语句的输出：

从输出中可以清楚地看到，表products表有一个外键约束： fk_category

此语句删除了products表的外键约束：

ALTER TABLE products 
DROP FOREIGN KEY fk_category;Code language: SQL (Structured Query Language) (sql)

为了确保外键约束已经被删除，可以查看products表的结构：

SHOW CREATE TABLE products;Code language: SQL (Structured Query Language) (sql)

禁用外键检查

有时，禁用外键检查非常有用，例如，当您将数据从 CSV 文件导入表时。如果不禁用外键检查，则必须按正确的顺序加载数据，即必须先将数据加载到父表中，然后再加载到子表中，这可能很乏味。但是，如果禁用外键检查，则可以按任意顺序将数据加载到表中。

要禁用外键检查，请使用以下语句：

SET foreign_key_checks = 0;Code language: SQL (Structured Query Language) (sql)

您可以使用以下语句启用它：

SET foreign_key_checks = 1;Code language: SQL (Structured Query Language) (sql)

在本教程中，您了解了 MySQL 外键以及如何使用各种参考选项创建外键约束。

本教程有帮助吗？

MySQL外键简介

自引用外键

MySQL FOREIGN KEY语法

MySQL FOREIGN KEY示例

RESTRICT & NO ACTION行动

CASCADE行动

SET NULL操作