MySQL外键详解

什么是外键约束

外键是表中的一列或一组列链接到另外一张表的一列或一组列。外键会在相关联的表中起到约束作用，保证数据的一致性和实现一些级联操作。

如果在表A中建立外键，关联到表B，那么表B为主表，表A为从表。主表B中的对应的列的更新或删除会联动到外键所在的表A中的相应的列的操作（具体的操作根据在表A中添加外键时的配置不同而不同）

即，建立外键的表为从表，被外键关联的表是主表。

创建外键约束时，会针对本表中相应的行自动创建索引。

外键的使用条件

  1. 外键只适用于InnoDB引擎，MyISAM不支持。
  2. 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
  3. 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

简单介绍

假设存在两张表customers和orders。每一个custormer可以有0个或多个orders，同样的，每个order都属于某一个customer。

可以看出custormers和orders表是一对多的关系。并且这个关系通过orders表中的外键的customer_id列来建立。

在oders表中的custormer_id列链接到customers表中的id主键列。

此时，customers表通常被称为主表（父表），orders表被称为子表。

通常情况下，外键都是关联到主表的主键列上面。

子表上面可以创建多个外键并关联到多个主表的主键列上。

一旦外键约束就位。外键约束的列的值需要在主表的主键列上存在，或者为NULL（此时外键约束的action是SET NULL）

例如，orders表中的customer_id列的值需要存在与customers表的id列上。oders表中的多个行可以拥有相同的custormer_id

自引用的外键

有些时候，子表和主表可能是同一张表。这种情况下外键引用的是当前表的主键

比如下面这张表employees：

employees

其中reportTo字段是一个外键字段，它指向本表的主键列employeeNumber。

这种关系允许employees表存储雇员与管理人员的关系结构。每个雇员都有0个或1个上级，且每个雇员可以拥有0个或多个下级。

此时，reportTo列上的外键就叫做递归或自引用外键。

创建外键的SQL语法

通过CREATE TABLE或ALTER TABLE创建外键的基本语法如下：

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

首先在CONSTRAINT关键字后面指定外键约束的名字。如果省略名字，那么MySQL会为此外键约束自动创建一个名字。

接下来，通过FOREIGN KEY关键字来指定此外键的列，多个列的话用逗号分隔开来，同样的外键的名字也是可省略的。

第三步指定主表和主表上被引用到的列，多列用逗号分隔。

最后，指定外键在子表和主表之间联动的动作（action），这些动作分为ON DELETE和ON UPDATE。reference_option表示子表中此外键关联的列所采取的行动，当主表中被引用的列被删除（ON DELETE）或更新（ON UPDATE）时。

MySQL有5种reference options：CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT：

  • CASCADE：级联操作，如果父表中的一行被删除或更新，子表会自动跟着删除或更新。
  • SET NULL：如果父表中的行被删除或更新，子表中相应的列的值被设置为NULL。
  • RESTRICT：如果父表中被外键引用的列的值在子表中存在相应的行与之匹配，MySQL拒绝父表的这个删除或更新操作。
  • NO ACTION：同RESTRICT
  • SET DEFAULT：MySQL的语法分析器可以识别，但是InnoDB和NDB引擎不支持。

实际上，MySQL支持三种actions：RESTRICT, CASCADE and SET NULL。

如果没有设置ON DELETE和ON UPDATE，那么默认的action是RESTRICT。

开始实验

下面通过实际的操作来体验外键的功能。

创建两张表custormers和orders

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_info VARCHAR(255)
) ENGINE=INNODB;

RESTRICT & NO ACTION actions

在orders表中创建外键，使用默认的RESTRICTaction。

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id);

未指定action时，默认ON DELETE和ON UPDATE都是RESTRICT

在customers表中插入数据：

INSERT INTO customers (name) values ('andy'),('jerry');

此时customser表中的数据为：

id name
1 andy
2 jerry

在orders表中插入新的行：

INSERT INTO orders (customer_id,order_info) values (1,'info');

插入成功，因为id为1的customer_id存在于customers表中。

那么接下来在orders表中插入一条customer_id不存在的数据看看会发生什么：

INSERT INTO orders (customer_id,order_info) values (3,'info');

出现了如下的报错信息：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

那么接下来更新一下customers表中id为1的行试试看：

UPDATE customers set id = 5 where id = 1;

出现了报错：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

因为RESTRICTaction，如果子表中存在父表中外键约束引用到的列的值，那么mysql会阻止了父表的更新和删除操作。

下面的更新就不会出错，因为id为2的customer_id不存在于oders表中。

UPDATE customers set id = 5 where id = 2;

CASCADE action

删除外键约束

需要使用两条命令来删除：

//删除外键约束
ALTER TABLE orders DROP FOREIGN KEY `orders_ibfk_1`;
//删除创建外键约束时自动创建的索引
ALTER TABLE orders DROP INDEX `customer_id`;

注意删除外键约束的时候，使用语句ALTER TABLE example_table DROP FOREIGN KEY `constraint_name`;这里的constraint_name是外键约束的名字，而不是外键的名字，如果创建约束的时候没有指定名字，那么可以通过SHOW CREATE TABLE example_table命令查看。
同时，上面的命令删除了外键约束并不会同步删除创建外键约束是的对应列的索引，所以需要额外的一条命令去删除它。

创建新的外键约束，使用CASCADEaction：

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

此时orders表中的数据如下：

id customer_id order_info
1 1 info

将custorms表中的id为1的行改为10：

UPDATE customers set id = 10 where id = 1;

更新成功之后，查看orders表中的数据：

id customer_id order_info
1 10 info

发现其customer_id列的值也同步更新为了10

接下来删除customsers表中id为10的行：

DELETE FROM customers where id = 10;

执行成功之后查看orders表中的数据为空，其与主表关联的行数据也被删除了。

SET NULL action

删除外键约束

需要使用两条命令来删除：

//删除外键约束
ALTER TABLE orders DROP FOREIGN KEY `orders_ibfk_1`;
//删除创建外键约束时自动创建的索引
ALTER TABLE orders DROP INDEX `customer_id`;

创建新的外键约束，使用CASCADEaction：

ALTER TABLE orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    ON UPDATE SET NULL
    ON DELETE SET NULL;

这里注意创建外键约束的列customer_id不能使用NOT NULL语句，不然无法创建SET NULLaction的外键约束

往orders插入新的数据（上个步骤的删除行操作已经将orders表的数据删除了）

INSERT INTO orders (customer_id,order_info) values (5,'info');
id customer_id order_info
1 5 info

更新customers表中id为5个行，将id改为50：

UPDATE customers set id = 50 where id = 5;

查看orders表：

id customer_id order_info
1 NULL info

原先customer_id为5的那一行数据，现在值变为了NULL，这是因为ON UPDATE SET NULL起作用了

往customsers和orders表中都插入新的测试数据

INSERT INTO customers (id,name) values (8,'andy');
INSERT INTO orders (customer_id,order_info) values (8,'info');

现在customsers表和orders表的数据分别如下：

id name
8 andy
50 jerry
id customer_id order_info
1 NULL info
4 8 info

删除customers表中新增的id为8的数据：

DELETE FROM customers where id = 8;

此时orders表中的相对应的行的customer_id列的值变为了NULL：

id customer_id order_info
1 NULL info
4 NULL info

这是因为外键约束ON DELETE SET NULL action 起了作用

禁用外键检查

有些时候当我们需要从一个表中导入数据是，如果存在外键，那么导入和修改的顺序就不能错乱，必须严格遵循先导入主表然后再导入子表的顺序。此时可以通过改变变量的形式来临时禁用外键检查。

禁用外键检查：

SET foreign_key_checks = 0;

启用外键检查：

SET foreign_key_checks = 1;

参考

An Essential Guide to MySQL Foreign Key By Practical Examples
Using FOREIGN KEY Constraints
mysql 外键（foreign key）的详解和实例

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,723评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,485评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,998评论 0赞 344
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,323评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,355评论 5赞 374
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,079评论 1赞 285
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,389评论 3赞 400
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,019评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,519评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,971评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,100评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,738评论 4赞 324
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,293评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,289评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,517评论 1赞 262
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,547评论 2赞 354
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,834评论 2赞 345