MySQL - 外键

MySQL - 外键 首页 / MySQL入门教程 / MySQL - 外键

外键用于将一个或多个表链接在一起。也称为引用键(referencing key)。外键与另一个表的主键字段匹配。这意味着一个表中的外键字段是指另一表的主键字段。它唯一标识另一个表的每一行，这些表在MySQL中保持参照完整性。

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

MySQL 通过两种方式定义外键:

  1. 使用CREATE TABLE语句
  2. 使用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                -  当从父表中删除或更新在reference(child)表中具有匹配行的任何行时，将使用它，MySQL不允许删除或更新父表中的行。

NO ACTION             -  它类似于RESTRICT。但是它有一个区别，即它在尝试修改表之后会检查参照完整性。

SET DEFAULT        -  MySQL解析器可以识别此操作。但是，InnoDB和NDB表都拒绝了此操作。

外键示例

让无涯教程了解外键如何在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;

将得到如下结构:

MySQL Foreign Key

在上面的输出中，可以看到客户表的键列中的 PRI 指示该字段是主索引值。接下来，联系值键列中的 MUL 告诉 Customer_Id 字段可以存储具有相同值的多行。

将数据插入表

现在，无涯教程必须将记录插入两个表中。执行以下语句以将数据插入表customer:

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

插入后，执行SELECT TABLE命令以检查客户表数据，如下所示:

MySQL Foreign Key

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

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

联系表如下所示:

MySQL Foreign Key

现在，让无涯教程看看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的字段将从两个表中完全删除。

MySQL Foreign Key

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

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

再次，如果无涯教程查看表，可以看到两个表都被更改，其中Customer_Id为Mary = 3。

MySQL Foreign Key

使用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@learnfk.com', 'email'),
(1, '121-121-121', 'work' ),
 (2, 'mary@learnfk.com', 'email'),
(2, '212-212-212', 'work'),
(3, 'peter@learnfk.com', 'email'),
(3, '313-313-313', 'home');

现在，更新"Persons"表的ID:

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

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

MySQL Foreign Key

如果查看表，可以看到两个表都已更改。由于进行了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;

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

无涯教程网

mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer;

可以使用SHOW CREATE TABLE语句来验证是否删除了外键约束。它将提供如下输出，在该输出中，无涯教程可以看到表联系人中的外键不再可用。

MySQL Foreign Key

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

祝学习愉快！(内容编辑有误？请选中要编辑内容 -> 右键 -> 修改 -> 提交！)

技术教程推荐

从0开始学架构 -〔李运华〕

零基础学Python -〔尹会生〕

Android开发高手课 -〔张绍文〕

Kafka核心技术与实战 -〔胡夕〕

Netty源码剖析与实战 -〔傅健〕

视觉笔记入门课 -〔高伟〕

TensorFlow 2项目进阶实战 -〔彭靖田〕

容器实战高手课 -〔李程远〕

体验设计案例课 -〔炒炒〕

好记忆不如烂笔头。留下您的足迹吧 :)
休闲君评测 |  TOOLFK工具网 |  chrome插件网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  豪评测 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630