轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL 外键约束


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


MySQL外键简介

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

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

MySQL外键 - 客户和订单表

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

customers 表称为父表引用表orders表称为子表引用表

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

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

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

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

MySQL递归外键

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 KEYREFERENCES必须相同。
  • ON DELETE子句允许您定义删除父表中的记录时子表中记录的内容。如果省略ON DELETE子句并删除父表中包含子表中记录的记录,MySQL将拒绝删除。此外,MySQL还为您提供操作,以便您可以使用其他选项,例如ON DELETE CASCADE  ,要求MySQL删除子表中的记录,当父表中的记录被删除时,记录将引用父表中的记录。如果您不希望删除子表中的相关记录,请改用ON DELETE SET NULL操作。MySQL会将子表中的外键列值设置为NULL删除父表中的记录时,条件是子表中的外键列必须接受NULL值。请注意,如果您使用ON DELETE NO ACTIONON DELETE RESTRICT操作,MySQL将拒绝删除。
  • ON UPDATE子句使您可以指定更新父表中的行时子表中的行会发生什么。您可以省略ON UPDATE子句,以便在更新父表中的行时让MySQL拒绝对子表中行的任何更新。ON UPDATE CASCADE操作允许您执行跨表更新,并且当更新父表ON UPDATE SET NULL中的行时,操作会将子表中的行中的值重置为值NULLON UPDATE NO ACTIONUPDATE RESTRICT行动拒绝任何更新。

MySQL创建表外键示例

下面的示例创建一个dbdemo数据库和两个表:categories和  products.每个类别具有一个或多个产品和每个产品只属于一个类别。products表中的cat_id字段被定义为带有UPDATE ON CASCADEDELETE 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_1products_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中有效地管理外键。