MySQL外键-IGI

时间：2019-11-20 08:52:12 　来源:igfitidea点击:

简介：在本教程中，您将学习MySQL外键以及如何创建，删除和禁用外键约束。

MySQL外键简介

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

让我们看一下示例数据库中的以下客户和订单表。

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

客户表和订单表之间的关系是一对多的。
并通过customerNumber列指定的订单表中的外键建立此关系。

订单表中的customerNumber列链接到客户表中的customerNumber主键列。

客户表称为父表或引用表，订单表称为子表或引用表。

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

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

外键约束到位后，子表中的外键列必须在父表的父键列中具有对应的行，或者这些外键列中的值必须为NULL(请参见下面的SET NULL操作示例) 。

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

自引用外键

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

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

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

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

列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]

使用以下语法：

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

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

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

最后，使用ON DELETE和ON UPDATE子句指定外键如何保持子表和父表之间的引用完整性。
reference_option确定在删除(ON DELETE)或更新(ON UPDATE)父键列中的值时MySQL将采取的措施。

MySQL有五个参考选项：CASCADE，SET NULL，NO ACTION，RESTRICT和SET DEFAULT。

级联：如果删除或更新了父表中的一行，则子表中匹配行的值将自动删除或更新。
SET NULL：如果删除或更新了父表中的一行，则子表中一个或多个外键列的值设置为NULL。
限制：如果父表中的一行在子表中具有匹配的行，则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;

限制和不采取行动

在fkdemo数据库中，创建两个表类别和产品：

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;

产品表中的categoryId是外键列，它引用了类别表中的categoryId列。

由于我们未指定任何ON UPDATE和ON DELETE子句，因此更新和删除操作的默认操作均为RESTRICT。

以下步骤说明了RESTRICT操作。

1)在类别表中插入两行：

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');

2)从类别表中选择数据：

SELECT * FROM categories;

3)在产品表中插入新行：

INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);

之所以起作用，是因为categoryId表中存在categoryId 1。

4)尝试在category表中不存在具有categoryId值的新表中插入新行：

INSERT INTO products(productName, categoryId)
VALUES('iPad',3);

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)

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

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

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)

由于使用了RESTRICT选项，您不能删除或更新categoryId 1，因为productId表中的productId 1引用了它。

级联动作

这些步骤说明ON UPDATE CASCADE和ON DELETE CASCADE操作如何工作。

1)放置产品表：

DROP TABLE products;

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

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;

3)在产品表中插入四行：

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

4)从产品表中选择数据：

SELECT * FROM products;

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

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

6)验证更新：

SELECT * FROM categories;

7)从产品表中获取数据：

SELECT * FROM products;

如您所见，由于执行了ON UPDATE CASCADE操作，products表的categoryId列中值为1的两行被自动更新为100。

8)从类别表中删除categoryId 2：

DELETE FROM categories
WHERE categoryId = 2;

9)确认删除：

SELECT * FROM categories;

10)检查产品表：

SELECT * FROM products;

由于" ON DELETE CASCADE"操作，自动删除了产品表中所有类别ID为2的产品。

SET NULL动作

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

1)删除类别和产品表：

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

2)创建类别和产品表：

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;

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

3)将行插入类别表：

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');

4)在产品表中插入行：

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

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

UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;

6)验证更新：

SELECT * FROM categories;

7)从产品表中选择数据：

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

8)从类别表中删除categoryId 2：

DELETE FROM categories 
WHERE categoryId = 2;

9)检查产品表：

SELECT * FROM products;

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

删除MySQL外键约束

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

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

使用以下语法：

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

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

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

SHOW CREATE TABLE table_name;

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

SHOW CREATE TABLE products;

以下是该语句的输出：

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

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

ALTER TABLE products 
DROP FOREIGN KEY fk_category;

为确保已删除外键约束，您可以查看产品表的结构：

SHOW CREATE TABLE products;

禁用外键检查

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

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

SET foreign_key_checks = 0;

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

SET foreign_key_checks = 1;

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

MySQL外键

MySQL外键简介

自引用外键

MySQL FOREIGN KEY语法

MySQL FOREIGN KEY示例

限制和不采取行动

级联动作

SET NULL动作

删除MySQL外键约束

禁用外键检查

相关推荐

最近更新

标签

MySQL外键

MySQL外键简介

自引用外键

MySQL FOREIGN KEY语法

MySQL FOREIGN KEY示例

限制和不采取行动

级联动作

SET NULL动作

删除MySQL外键约束

禁用外键检查

相关推荐

MySQL创建数据库

MySQL DROP DATABASE删除数据库

了解MySQL存储引擎

MySQL创建表

相关推荐

最近更新

标签