❮ 上一页 下一页 ❯

MySQL 外键约束

简介：在本教程中，您将了解MySQL外键以及如何在MySQL中创建，添加和删除外键约束。

MySQL外键简介

外键是表中与另一个表的另一个字段匹配的字段。外键对相关表中的数据设置了约束，这使MySQL能够保持参照完整性。

我们来看看示例数据库中的以下数据库图。

我们有两个表：customers和orders. 每个客户都有零个或多个订单，每个订单只能属于一个客户。customers表和orders表之间的关系是一对多的，它orders由customerNumber字段指定的表中的外键建立。customers表中的customerNumber字段与orders表中的customerNumber主键字段相关 。

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

外键可以是一个列或一组列。子表中的列通常引用父表中的主键列。

表可以具有多个外键，子表中的每个外键可以引用不同的父表。

子表中的行必须包含父表中存在的值，例如，orders表中的每个订单记录必须具有customers表customerNumber中存在的值。因此，多个订单可以引用同一个客户，这种关系称为一个（客户）到多个（订单）或一对多。

有时，子表和父表是相同的。外键引用表的主键，例如，下employees表：

reportTo列是一个外键，它引用employeeNumber作为employees表的主键的列，以反映员工之间的汇报结构，即每个员工向另一个员工汇报，员工可以有零个或多个直接汇报。我们有一个关于自联接教程可以帮助您根据这种表查询数据。

reportTo外键也被称为递归或自引用的外键。

外键强制执行引用完整性，可帮助您自动维护数据的一致性和完整性。例如，您无法为不存在的客户创建订单。

此外，您可以customerNumber在外键的删除操作上设置级联，以便在删除customers表中的客户时，也会删除与客户关联的所有订单。这节省了使用多个DELETE语句 或DELETE JOIN语句的时间和精力。

与删除相同，您还可以在更新操作上为customerNumber外键定义级联，以便在不使用多个UPDATE语句或UPDATE JOIN语句的情况下执行跨表更新。

注意：在MySQL中，InnoDB 存储引擎支持外键，因此您必须创建InnoDB表才能使用外键约束。

为表创建外键

MySQL创建外键语法

以下语法说明如何在CREATE TABLE语句中的子表中定义外键。

CONSTRAINT constraint_name

FOREIGN KEY foreign_key_name (columns)

REFERENCES parent_table(columns)

ON DELETE action

ON UPDATE action

让我们更详细地学习一下语法：

CONSTRAINT子句允许您为外键约束定义约束名称。如果省略它，MySQL将自动生成一个名称。

FOREIGN KEY子句指定子表中引用父表中主键列的列。你可以把一个外键名称放在FOREIGN KEY子句之后，或者让MySQL为你创建一个名字。请注意，MySQL会自动使用foreign_key_name名称创建索引。

REFERENCES子句指定子表中的列所引用的父表及列。在规定的子表和父表的列数FOREIGN KEY和REFERENCES必须相同。

ON DELETE子句允许您定义删除父表中的记录时子表中记录的内容。如果省略ON DELETE子句并删除父表中包含子表中记录的记录，MySQL将拒绝删除。此外，MySQL还为您提供操作，以便您可以使用其他选项，例如ON DELETE CASCADE ，要求MySQL删除子表中的记录，当父表中的记录被删除时，记录将引用父表中的记录。如果您不希望删除子表中的相关记录，请改用ON DELETE SET NULL操作。MySQL会将子表中的外键列值设置为NULL删除父表中的记录时，条件是子表中的外键列必须接受NULL值。请注意，如果您使用ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL将拒绝删除。

ON UPDATE子句使您可以指定更新父表中的行时子表中的行会发生什么。您可以省略ON UPDATE子句，以便在更新父表中的行时让MySQL拒绝对子表中行的任何更新。ON UPDATE CASCADE操作允许您执行跨表更新，并且当更新父表ON UPDATE SET NULL中的行时，操作会将子表中的行中的值重置为值NULL。ON UPDATE NO ACTION或UPDATE RESTRICT行动拒绝任何更新。

MySQL创建表外键示例

下面的示例创建一个dbdemo数据库和两个表：categories和 products.每个类别具有一个或多个产品和每个产品只属于一个类别。products表中的cat_id字段被定义为带有UPDATE ON CASCADE和DELETE ON RESTRICT操作的外键。

CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(

cat_id int not null auto_increment primary key,

cat_name varchar(255) not null,

cat_description text

) ENGINE=InnoDB;

CREATE TABLE products(

prd_id int not null auto_increment primary key,

prd_name varchar(355) not null,

prd_price decimal,

cat_id int not null,

FOREIGN KEY fk_cat(cat_id)

REFERENCES categories(cat_id)

ON UPDATE CASCADE

ON DELETE RESTRICT

)ENGINE=InnoDB;

将外键添加到表中

MySQL添加外键语法

要将外键添加到现有表，请使用带有上述外键定义语法的ALTER TABLE语句：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY foreign_key_name(columns)

REFERENCES parent_table(columns)

ON DELETE action

ON UPDATE action;

MySQL添加外键示例

现在，让我们添加一个名为vendors的新表，并更改products表以包含供应商ID字段：

USE dbdemo;

CREATE TABLE vendors(

vdr_id int not null auto_increment primary key,

vdr_name varchar(255)

)ENGINE=InnoDB;

ALTER TABLE products

ADD COLUMN vdr_id int not null AFTER cat_id;

要向表中添加外键products，请使用以下语句：

ALTER TABLE products

ADD FOREIGN KEY fk_vendor(vdr_id)

REFERENCES vendors(vdr_id)

ON DELETE NO ACTION

ON UPDATE CASCADE;

mysql> SHOW CREATE TABLE products;

CREATE TABLE `products` (

`prd_id` int(11) NOT NULL AUTO_INCREMENT,

`prd_name` varchar(355) NOT NULL,

`prd_price` decimal(10,0) DEFAULT NULL,

`cat_id` int(11) NOT NULL,

`vdr_id` int(11) NOT NULL,

PRIMARY KEY (`prd_id`),

KEY `fk_cat` (`cat_id`),

KEY `fk_vendor` (`vdr_id`),

CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON UPDATE CASCADE,

CONSTRAINT `products_ibfk_2` FOREIGN KEY (`vdr_id`) REFERENCES `vendors` (`vdr_id`) ON DELETE NO ACTION ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1

现在，products表有两个外键，一个引用categories表，另一个引用vendors表。

删除MySQL外键

您还可以使用ALTER TABLE语句删除外键，如下所示：

ALTER TABLE table_name

DROP FOREIGN KEY constraint_name;

在上面的声明中：

首先，指定要从中删除外键的表名。

其次，将约束名称放在DROP FOREIGN KEY子句之后。

注意：constraint_name是在向表创建或添加外键时指定的约束的名称。如果省略它，MySQL会为您生成约束名称。

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

SHOW CREATE TABLE table_name;

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

SHOW CREATE TABLE products;

以下是输出：

CREATE TABLE `products` (

`prd_id` int(11) NOT NULL AUTO_INCREMENT,

`prd_name` varchar(355) NOT NULL,

`prd_price` decimal(10,0) DEFAULT NULL,

`cat_id` int(11) NOT NULL,

`vdr_id` int(11) NOT NULL,

PRIMARY KEY (`prd_id`),

KEY `fk_cat` (`cat_id`),

KEY `fk_vendor` (`vdr_id`),

CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cat_id`) REFERENCES `categories` (`cat_id`) ON UPDATE CASCADE,

CONSTRAINT `products_ibfk_2` FOREIGN KEY (`vdr_id`) REFERENCES `vendors` (`vdr_id`) ON DELETE NO ACTION ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1

products表有两个外键约束：products_ibfk_1和products_ibfk_2

您可以products使用以下语句删除表的外键：

ALTER TABLE products

DROP FOREIGN KEY products_ibfk_1;

ALTER TABLE products

DROP FOREIGN KEY products_ibfk_2;

CREATE TABLE `products` (

`prd_id` int(11) NOT NULL AUTO_INCREMENT,

`prd_name` varchar(355) NOT NULL,

`prd_price` decimal(10,0) DEFAULT NULL,

`cat_id` int(11) NOT NULL,

`vdr_id` int(11) NOT NULL,

PRIMARY KEY (`prd_id`),

KEY `fk_cat` (`cat_id`),

KEY `fk_vendor` (`vdr_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL禁用外键检查

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

另一个示例是，除非禁用外键检查，否则不能删除外键约束引用的表。删除表时，也会删除为表定义的任何约束。

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

SET foreign_key_checks = 0;

当然，您可以使用以下语句启用它：

SET foreign_key_checks = 1;

在本教程中，我们已经介绍了很多关于MySQL外键的内容。我们还向您介绍了一些非常方便的语句，允许您在MySQL中有效地管理外键。

相关教程

MySQL NOT NULL约束

❮ 上一页 下一页 ❯