MySQL 外键

外键用于将一个或多个表链接在一起。它也被称为参考键。外键与另一个表的主键字段匹配。这意味着一个表中的外键字段指向另一个表的主键字段。它唯一地标识了另一个表的每一行，从而在MySQL中保持了引用完整性。

外键使得在表之间创建父子关系成为可能。在这种关系中，父表保存初始列值，子表的列值引用父表的列值。MySQL允许我们在子表上定义外键约束。

MySQL 以两种方式定义外键：

使用CREATE TABLE语句
使用ALTER TABLE语句

语法

以下是在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中如何使用外键。首先，我们将创建一个名为” mysqltestdb “的数据库，并使用以下命令开始使用它：

mysql> CREATE DATABASE mysqltestdb;
mysql> use mysqltestdb;

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

Table: customer

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

表格：联系人

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;

我们将会得到以下结构：

MySQL 外键

以上输出中，我们可以看到顾客表的关键列中的 PRI 表示该字段是主索引值。接下来，联系人表中关键列中的 MUL 表示 Customer_Id 字段可以存储具有相同值的多行。

向表中插入数据

现在，我们需要向两个表中插入记录。执行以下语句将数据插入customer表中：

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

在插入之后，执行SELECT TABLE命令来检查客户表的数据，如下所示：

MySQL 外键

执行下面的插入语句将数据添加到contact表中：

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');

我们的联系人表如下所示：

MySQL 外键

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

所以在这里，我们将删除参考数据，从两个表中删除记录。我们在contact表中定义了外键：

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

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

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

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

再次，如果我们查看我们的表格，我们可以看到两个表格都被更改了。这意味着名为JOHN的字段将从两个表格中完全删除。

MySQL 外键

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

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

再次，如果我们查看我们的表格，我们可以看到两个表格的Customer_Id都被更改为Mary=3。

MySQL 外键

使用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)
);

表：顾客

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语句验证更新:

MySQL 外键

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

如何删除外键

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

ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

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

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

mysql> SHOW CREATE TABLE contact;

它将产生以下输出，我们可以看到表contact有一个名为fk_customer的外键，显示在红色的矩形中。

MySQL 外键

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

mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer;

可以使用SHOW CREATE TABLE语句来验证外键约束是否被删除。它将输出如下结果，我们可以看到外键不再出现在contact表中。

MySQL 外键

使用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)
);

表格：联系人

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;