MySQL教程
 MySQL Primary Key(主键) MySQL Composite Key(复合键) 

MySQL Foreign Key(外键)

外键用于将一张或多张表链接在一起。它也称为引用键。外键匹配另一个表的主键字段。这意味着一个表中的外键字段引用另一个表的主键字段。它唯一地标识另一个表的每一行，以维护 MySQL 中的引用完整性。

外键可以创建与表的父子关系。在这种关系中，父表保存初始列值，子表的列值引用父列值。MySQL 允许我们在子表上定义外键约束。

MySQL以两种方式定义外键：

  1. 使用 CREATE TABLE 语句
  2. 使用 ALTER TABLE 语句

一、MySQL Foreign Key(外键) 语法

以下是用于在 MySQL 中使用 CREATE TABLE 或 ALTER TABLE 语句定义外键的基本语法：

[CONSTRAINT constraint_name]  
    FOREIGN KEY [foreign_key_name] (col_name, ...)  
    REFERENCES parent_tbl_name (col_name,...)  
    ON DELETE referenceOption  
    ON UPDATE referenceOption

在上面的语法中，我们可以看到以下参数：

constraint_name：指定外键约束的名称。如果我们没有提供约束名称，MySQL 会自动生成它的名称。

col_name：这是我们要制作外键的列的名称。

parent_tbl_name：它指定父表的名称，后跟引用外键列的列名。

Refrence_option：用于确保外键如何在父表和子表之间使用 ON DELETE 和 ON UPDATE 子句保持参照完整性。

MySQL 包含五个不同的引用选项，如下所示：

CASCADE：当我们从父表中删除或更新任何行时，子表中匹配行的值将被自动删除或更新。

SET NULL：当我们从父表中删除或更新任何行时，将子表中的外键列的值设置为NULL。

RESTRICT：当我们从父表中删除或更新引用（子）表中具有匹配行的任何行时使用它，MySQL 不允许删除或更新父表中的行。

NO ACTION：类似于 RESTRICT。但是它有一个区别，它在尝试修改表后检查引用完整性。

SET DEFAULT： MySQL 解析器识别此操作。然而，InnoDB 和 NDB 表都拒绝了这个动作。

注意：MySQL 主要提供对 CASCADE、RESTRICT 和 SET NULL 操作的全面支持。如果我们没有指定 ON DELETE 和 ON UPDATE 子句，MySQL 将采取默认操作 RESTRICT。

二、MySQL Foreign Key(外键) 示例

让我们了解外键在 MySQL 中是如何工作的。因此，首先，我们将创建一个名为“ mysqltestdb ”的数据库并使用以下命令开始使用它：

mysql> CREATE DATABASE mysqltestdb;  
mysql> use mysqltestdb;

接下来，我们需要使用以下语句创建两个名为“ customer ”和“ contact ”的表：

customer表：

CREATE TABLE customer (  
  ID INT NOT NULL AUTO_INCREMENT,  
  Name varchar(50) NOT NULL,  
  City varchar(50) NOT NULL,  
  PRIMARY KEY (ID)  
);

contact表：

CREATE TABLE contact (  
  ID INT,  
  Customer_Id INT,  
  Customer_Info varchar(50) NOT NULL,  
  Type varchar(50) NOT NULL,  
  INDEX par_ind (Customer_Id),  
  CONSTRAINT fk_customer FOREIGN KEY (Customer_Id)  
  REFERENCES customer(ID)  
  ON DELETE CASCADE  
  ON UPDATE CASCADE  
);

验证表结构是否创建成功

mysql> SHOW TABLES;  
mysql> DESCRIBE customer;  
mysql> DESCRIBE contact;

我们将得到如下结构：

在上面的输出中，我们可以看到 customer 表的 key 列中的PRI告诉该字段是主索引值。接下来，contact 值的 key 列中的MUL告诉Customer_Id字段可以存储具有相同值的多行。

向表中插入数据

现在，我们必须将记录插入到两个表中。执行此语句将数据插入到表 customer 中：

INSERT INTO customer(Name, City) VALUES  
('Joseph', 'California'),  
('Mary', 'NewYork'),  
('John', 'Alaska');

执行以下插入语句将数据添加到表联系人中：

INSERT INTO contact (Customer_Id, Customer_Info, Type) VALUES  
(1, 'Joseph@javatpoint.com', 'email'),  
(1, '121-121-121', 'work' ),  
(1, '123-123-123', 'home'),  
(2, 'Mary@javatpoint.com', 'email'),  
(2, 'Mary@javatpoint.com', 'email'),  
(2, '212-212-212', 'work'),  
(3, 'John@javatpoint.com', 'email'),  
(3, '313-313-313', 'home');

我们的contact表如下所示：

现在，让我们看看 MySQL 中的外键是如何保持数据完整性的。

所以在这里，我们将删除从两个表中删除记录的参考数据。我们已经将联系人表中的外键定义为：

FOREIGN KEY (Customer_Id) REFERENCES customer(ID)   
ON DELETE CASCADE   
ON UPDATE CASCADE.

这意味着如果我们从客户表中删除任何客户记录，那么联系人表中的相关记录也应该被删除。并且 ON UPDATE CASCADE 将在父表上自动更新到子表中的引用字段（这里是 Customer_Id）。

执行这条语句，从名为JOHN的表中删除一条记录。

mysql> DELETE FROM customer WHERE Name='John';

同样，如果我们查看我们的表，我们可以看到两个表都已更改。这意味着名称为 JOHN 的字段将从两个表中完全删除。

现在，测试ON UPDATE CASCADE。在这里，我们将把联系表中Mary的 Customer_Id 更新为：

mysql> UPDATE customer SET id=3 WHERE Name='Mary';

同样，如果我们查看我们的表，我们可以看到两个表都已更改为 Mary=3 的 Customer_Id。

三、使用 SET NULL 操作的外键示例

在这里，我们将了解 SET NULL 操作如何与外键一起使用。首先，我们要创建两个名为Persons和Contacts的表，如下所示：

Persons表：

CREATE TABLE Persons (  
  ID INT NOT NULL AUTO_INCREMENT,  
  Name varchar(50) NOT NULL,  
  City varchar(50) NOT NULL,  
  PRIMARY KEY (ID)  
);

Contacts表：

CREATE TABLE Contacts (  
  ID INT,  
  Person_Id INT,  
  Info varchar(50) NOT NULL,  
  Type varchar(50) NOT NULL,  
  INDEX par_ind (Person_Id),  
  CONSTRAINT fk_person FOREIGN KEY (Person_Id)  
  REFERENCES Persons(ID)  
  ON DELETE SET NULL  
  ON UPDATE SET NULL  
);

接下来，我们需要使用以下语句将数据插入到两个表中：

INSERT INTO Persons(Name, City) VALUES  
('Joseph', 'Texas'),  
('Mary', 'Arizona'),  
('Peter', 'Alaska');  

INSERT INTO Contacts (Person_Id, Info, Type) VALUES  
(1, 'joseph@javatpoint.com', 'email'),  
(1, '121-121-121', 'work' ),  
 (2, 'mary@javatpoint.com', 'email'),  
(2, '212-212-212', 'work'),  
(3, 'peter@javatpoint.com', 'email'),  
(3, '313-313-313', 'home');

现在，更新“Persons”表的 ID：

mysql> UPDATE Persons SET ID=103 WHERE ID=3;

最后，使用下面给出的 SELECT 语句验证更新：

如果我们查看我们的表，我们可以看到两个表都已更改。由于 ON UPDATE SET NULL 操作，Contacts 表中Person_Id=3的行自动设置为NULL 。

四、如何删除外键

MySQL 允许 ALTER TABLE 语句从表中删除现有的外键。以下语法用于删除外键：

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

在这里，table_name是我们要从中删除外键的表的名称。constraint_name是在创建表期间添加的外键的名称。

如果我们不知道表中现有外键的名称，请执行以下命令：

mysql> SHOW CREATE TABLE contact;

它将给出如下输出，我们可以看到表联系人有一个名为 fk_customer 的外键，显示在红色矩形中。

现在，要从联系人表中删除此外键约束，请执行以下语句：

mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer;

我们可以使用 SHOW CREATE TABLE 语句来验证外键约束是否移除。它将给出如下输出，我们可以看到外键在表联系人中不再可用。

五、使用 ALTER TABLE 语句定义外键

该语句允许我们对现有表进行修改。有时需要在现有表的列中添加外键；然后，此语句用于为该列添加外键。

语法

以下是在现有表中添加外键的 ALTER TABLE 语句的语法：

ALTER TABLE table_name  
    ADD [CONSTRAINT [symbol]] FOREIGN KEY  
    [index_name] (column_name, ...)  
    REFERENCES table_name (column_name,...)  
    ON DELETE referenceOption  
    ON UPDATE referenceOption

当我们使用 ALTER TABLE 语句添加外键时，建议首先在外键引用的列上创建索引。

示例

以下语句创建两个表，“ Person ”和“ Contact ”，表定义中没有外键列。

Person表：

CREATE TABLE Person (  
  ID INT NOT NULL AUTO_INCREMENT,  
  Name varchar(50) NOT NULL,  
  City varchar(50) NOT NULL,  
  PRIMARY KEY (ID)  
);

Contact表：

CREATE TABLE Contact (  
  ID INT,  
  Person_Id INT,  
  Info varchar(50) NOT NULL,  
  Type varchar(50) NOT NULL  
);

创建表后，如果要给现有表添加外键，需要执行如下的 ALTER TABLE 语句：

ALTER TABLE Contact ADD INDEX par_ind ( Person_Id );  
ALTER TABLE Contact ADD CONSTRAINT fk_person  
FOREIGN KEY ( Person_Id ) REFERENCES Person ( ID ) ON DELETE CASCADE ON UPDATE RESTRICT;

六、外键检查

MySQL 有一个特殊的变量 foreign_key_cheks 来控制外键检查到表中。默认情况下，在对表的正常操作期间启用它以强制执行参照完整性。这个变量本质上是动态的，因此它同时支持全局和会话范围。

有时需要禁用外键检查，这在以下情况下非常有用：

  • 我们删除一个由外键引用的表。
  • 我们将数据从 CSV 文件导入到表中。它加快了导入操作。
  • 我们在具有外键的表上使用 ALTER TABLE 语句。
  • 我们可以按任意顺序对表执行加载数据操作，以避免外键检查。

以下语句允许我们禁用外键检查：

SET foreign_key_checks = 0;

以下语句允许我们启用外键检查：

SET foreign_key_checks = 1;

 

 MySQL Primary Key(主键) MySQL Composite Key(复合键) 

热门文章

优秀文章

全部教程