MySql教程
MySql用户管理
MySQL数据库
MySql表格和视图
MySQL查询
MySQL索引
MySQL子句
MySQL授权
MySql控制流
MySQL条件
MySQL连接
MySQL键
MySQL触发器
MySQL聚合函数
MySQL常用
MySQL正则表达式
MySql差异性

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: 它指定父表的名称,后跟引用外键列的列名。
Reference_option: 用于确保父键和子表之间使用ON DELETE和ON UPDATE子句来确保外键如何保持引用完整性。
MySQL包含 五个不同的引用选项,如下所述: 用于确保外键如何使用父表和子表之间的ON DELETE和ON UPDATE子句来保持引用完整性。
CASCADE: 当我们从父表中删除或更新任何行时使用,子表中匹配行的值将被自动删除或更新。
SET NULL: 当我们从父表中删除或更新任何行时,子表中外键列的值设置为NULL。
RESTRI CT: 当我们从父表中删除或更新在reference(child)表中具有匹配行的任何行时,将使用它,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;
接下来,我们需要使用以下语句创建两个名为" customers"和" contacts"的表:
表: customers
CREATE TABLE customer (
  ID int NOT null AUTO_INCREMENT,
  Name varchar(50) NOT null,
  City varchar(50) NOT null,
  PRIMARY KEY (ID)
);
表: contacts
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 Foreign Key
执行以下插入语句以将数据添加到表联系人中:
INSERT INTO contact (Customer_Id, Customer_Info, Type) VALUES
(1, 'Joseph@lidihuo.com', 'email'),
(1, '121-121-121', 'work' ),
(1, '123-123-123', 'home'),
(2, 'Mary@lidihuo.com', 'email'),
(2, 'Mary@lidihuo.com', 'email'),
(2, '212-212-212', 'work'),
(3, 'John@lidihuo.com', 'email'),
(3, '313-313-313', 'home');
我们的联系表如下所示:
MySQL外键
现在,让我们了解MySQL中的外键如何保持数据完整性。
因此,在这里,我们将删除引用数据,该引用数据将从两个表中删除记录。我们已将联系表中的外键定义为:
FOREIGN KEY (Customer_Id) REFERENCES customer(ID) 
ON DELETE CASCADE 
ON UPDATE CASCADE.
这意味着,如果我们从客户表中删除任何客户记录,那么联系表中的相关记录也应删除。然后ON UPDATE CASCADE将在父表上自动更新为子表中的引用字段(此处为Customer_Id)。
执行此语句,从名称为 的表中删除一条记录约翰
mysql> DELETE FROM customer WHERE Name='John';
同样,如果我们查看我们的表,我们可以看到两个表都已更改。这意味着名称为JOHN的字段将从两个表中完全删除。
MySQL外键
现在,测试 ON UPDATE CASCADE 。在这里,我们将联系表中的 玛丽的Customer_Id更新为:
mysql> UPDATE customer SET id=3 WHERE Name='Mary';
同样,如果我们查看我们的表,我们可以看到两个表都被更改为Mary_3的Customer_Id。
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)
);
表: customers
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@lidihuo.com', 'email'),
(1, '121-121-121', 'work' ),
 (2, 'mary@lidihuo.com', 'email'),
(2, '212-212-212', 'work'),
(3, 'peter@lidihuo.com', 'email'),
(3, '313-313-313', 'home');
现在,更新"人员"表的ID:
mysql> UPDATE Persons SET ID=103 WHERE ID=3;
最后,使用下面给出的SELECT语句验证更新:
MySQL外键
如果查看表,我们可以看到两个表都已更改。由于进行了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外键
现在,要从联系人表中删除此外键约束,请执行以下语句:
mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer;
我们可以使用SHOW CREATE TABLE语句验证是否删除了外键约束。它将给出如下输出,在该输出中我们可以看到表联系人中不再有外键。
 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语句添加外键时,建议首先在由外键引用的列上创建一个 索引
示例
以下语句创建两个表" "和" contacts",而没有外键列到表定义中。
表: 人
CREATE TABLE Person (
  ID int NOT null AUTO_INCREMENT,
  Name varchar(50) NOT null,
  City varchar(50) NOT null,
  PRIMARY KEY (ID)
);
表: contacts
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;

昵称: 邮箱:
Copyright © 2022 立地货 All Rights Reserved.
备案号:京ICP备14037608号-4