MySql 外键（foreign key）

一、什么是外键

外键是某表中的一列是另一张表的主键；反之A表的主键作为B表的某一列；

外键也是索引的一种，是通过一张表中的一列指向另一张表的主键，来对两张表进行关联；

一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

二、外键的作用

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。主要体现在以下两个方面：

阻止执行

1. 从表插入新行，其外键值不是主表的主键值便阻止插入；

2. 从表修改外键值，新值不是主表的主键值便阻止修改；

3. 主表删除行，其主键值在从表里存在便阻止删除（要想删除，必须先删除从表的相关行）；

4. 主表修改主键值，旧值在从表存在便阻止修改（要想修改，必须先删除从表相关行）。

级联执行

1. 主表删除行，连带从表的相关行一起删除；

2. 主表修改主键值，连带从表相关行的外键值一起修改；

三、外键创建限制

父表必须已经存在于数据库中，或者是当前正在创建的表；

如果是后一种情况，则父表和子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性；

必须为父表定义主键；

外键中列的行数数目必须和父表的主键中列的行数数目相同；

两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键；

外键列必须建立索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和 tinyint 可以，而 int 和 char 则不可以。

四、外键创建方法

可以在创建表时创建外键，也可以在已有的表中增加外键。

以下主要讲如何给已有的表添加外键。

创建外键的语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中，ON DELETE 和 ON UPDATE 表示事件触发限制，各参数意义如下：

参数	意义
RESTRICT	限制外表中的外键改动（默认值，也是最安全的设置）
CASCADE	跟随外键改动
SET NULL	设为null值
NO ACTION	无动作
SET DEFAULT	设为默认值

五、案例

创建两张表

CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `avatar` varchar(255) NULL COMMENT '头像',
  `nick_name` varchar(255) CHARACTER SET utf32 NULL COMMENT '用户昵称',
  `phone` varchar(11) CHARACTER SET utf32 NOT NULL COMMENT '用户手机号',
  `wx_id` int NULL COMMENT '微信用户表主键ID',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `wx_user`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `open_id` varchar(255) NOT NULL COMMENT '小程序唯一用户标识',
  `union_id` varchar(255) NOT NULL COMMENT '开放平台唯一用户标识',
  `create_time` datetime NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

建立外键

ALTER TABLE `user` ADD CONSTRAINT `fk_user_wx_user_1` FOREIGN KEY (`wx_id`) REFERENCES `wx_user` (`id`) ;

触发限制使用默认值 RESTRICT 的情况；

1. 从表插入新行，外键值不存在于主表中，被阻止;

INSERT INTO `user`(nick_name,phone,wx_id,create_time,update_time) VALUES('章三','1319584xxxx',1,NOW(),NOW());

报如下错误：Cannot add or update a child row: a foreign key constraint fails (default.user, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (wx_id) REFERENCES wx_user (id) ON DELETE SET NULL)

2. 从表修改外键值，新值不在主表的主键中，阻止修改;

INSERT INTO wx_user(open_id,union_id,create_time,update_time) VALUES ('1111111111111111','22222222222222',NOW(),NOW());

INSERT INTO user(nick_name,phone,wx_id,create_time,update_time) VALUES ('张三','1312635xxxx',1,NOW(),NOW());

UPDATE `default`.`user` SET `wx_id` = 2 WHERE `id` = 1;

修改报错：Cannot add or update a child row: a foreign key constraint fails (default.user, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (wx_id) REFERENCES wx_user (id) ON DELETE SET NULL)

因为主表 wx_user 表中没有 主键Id为2 的数据。

3. 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)

DELETE from wx_user WHERE id =1;

报如下错误：Cannot delete or update a parent row: a foreign key constraint fails (default.user, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (wx_id) REFERENCES wx_user (id))；

4. 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)

UPDATE `wx_user` SET id = 3 WHERE id =1;

报如下错误：Cannot delete or update a parent row: a foreign key constraint fails (default.user, CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (wx_id) REFERENCES wx_user (id))

六、更改外键事件触发限制为 CASCADE

# 删除旧外键
ALTER TABLE `user` DROP FOREIGN KEY fk_user_wx_user_1

# 添加新的外键，修改事件触发限制为CASCADE
ALTER TABLE `user` ADD CONSTRAINT fk_user_wx_user_1 FOREIGN KEY (`wx_id`) REFERENCES `wx_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

此时两张表中的数据：

当主表修改主键值，从表中相关行的外键值将一起修改:

UPDATE `wx_user` SET id = 3 WHERE id =1;

如果主表删除行，从表中的相关行将一起被删除:

DELETE from wx_user WHERE id = 3;

七、结论

事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。

参考文章：https://blog.csdn.net/weiguang102/article/details/126409406