外键约束

• 只有InnoDB存储引擎支持外键，并且在创建外键的时候，我们子表的外键必须关联主表的主键。
• 外键字段依赖于已经存在的表的一个主键，所以一定要先有主表。
• 我们的外键字段必须和我们主表的主键字段的数据类型要相似：如果是数值型，两者必须类型一致，并且同时为有无符号；如果是字符串型，两者的长度可以不同，但是类型必须相同。
• 如果子表的外键字段没有创建索引，但是当我们创建完外键之后，它会自动的添加索引。
• 如果子表中有记录外键关联着主表中的记录，那么主表中的这条记录以及主表是无法删除的，子表中的这些记录可以删除。

创建外键

建表时指定外键

[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)

外键名称一般以 从表字段名称_fk_主表名称 进行命名

动态删除外键

ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;

动态增加外键

ALTER TABLE tbl_name
ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);

注意：动态添加外键之前表中的记录一定是合法的记录，没有脏值，否则外键添加不成功。

外键约束的参照操作

CASCADE

级联的。当我们对父表进行删除或者更新，那么我们子表对应得也会进行删除或者是更新。

SET NULL

从父表删除或者更新记录，会设置子表对应的外键列为NULL。但是得确保这个字段是可以为空得，不然无法写入NULL值。

NO ACTION | RESTRICT

这是默认的。拒绝对父表做更新或删除操作

测试

建表时指定外键测试

-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);
INSERT news_cate(cateName)
VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号',
    FOREIGN KEY(cateId) REFERENCES news_cate(id)
);
INSERT news(title,content,cateId)
VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

测试插入非法记录

INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);

1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)

也就会报错了

测试删除父表中的记录和删除父表

-- 测试删除父表中的记录
DELETE FROM news_cate WHERE id=1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))

-- 测试删除父表
DROP TABLE IF EXISTS news_cate;

3730 - Cannot drop table 'news_cate' referenced by a foreign key constraint 'news_ibfk_1' on table 'news'.

测试更新父表中的记录

UPDATE news_cate SET id=10 WHERE id=1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))

尝试向父类插入记录

INSERT news_cate(cateName) VALUES('教育新闻');

+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  1 | 国内新闻 | 无       |
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
|  5 | 教育新闻 | 无       |
+----+----------+----------+

这条语句是可以成功的

尝试更改新插入的记录

这条记录是刚插入的，也就是子表还没有使用的。

UPDATE news_cate SET cateName='教育' WHERE id=5;

+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  1 | 国内新闻 | 无       |
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
|  5 | 教育     | 无       |
+----+----------+----------+

尝试在建表时指定外键并指定外键名称

-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号',
    CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
);

下面时表的信息，我们就可以看到我们命名的外键名称

| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`),
  CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

尝试动态删除外键

ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

查看表信息，我们可以发现之前的外键被删除了

| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

尝试动态添加外键

ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

查看表信息

| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId` (`cateId`),
  CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

在动态添加外键的时候指定外键名称

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

查看表信息

| news  | CREATE TABLE `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `title` varchar(100) NOT NULL COMMENT '新闻标题',
  `content` varchar(1000) NOT NULL COMMENT '新闻内容',
  `cateId` tinyint(4) NOT NULL COMMENT '新闻所属分类编号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  KEY `cateId_fk_newsCate` (`cateId`),
  CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

尝试在表中有非法记录时动态插入外键

-- 插入非法记录
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);

-- 尝试动态添加外键
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

-- 会报错
1452 - Cannot add or update a child row: a foreign key constraint fails (`king`.`#sql-3d84_4b`, CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`))

我们将那个非法的记录删除后再进行动态添加外键

DELETE FROM news WHERE id=6;

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

添加成功

所以在表中存在对于将要添加的外键来说是非法的记录时，将无法添加这个外键。

尝试指定级联操作

-- 指定级联操作 DELETE,UPDATE CASCADE
-- 注意先把之前同名的外键删除
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;

-- 尝试更新父表news_cate
UPDATE news_cate SET id=11 WHERE id=1;

我们可以发现上面的操作中，我们仅仅对父表进行了更新，但是父表和子表同时进行了更新

SELECT * FROM news_cate;
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  2 | 国际新闻 | 无       |
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
| 11 | 国内新闻 | 无       |
+----+----------+----------+

SELECT * FROM news;
+----+-------+---------+--------+
| id | title | content | cateId |
+----+-------+---------+--------+
|  1 | a1    | aaaa1   |     11 |
|  2 | a2    | aaaa2   |     11 |
|  3 | a3    | aaaa3   |      4 |
|  4 | a4    | aaaa4   |      2 |
|  5 | a5    | aaaa5   |      3 |
+----+-------+---------+--------+

-- 尝试删除父表中的记录
DELETE FROM news_cate WHERE id=2;

SELECT * FROM news_cate;
+----+----------+----------+
| id | cateName | cateDesc |
+----+----------+----------+
|  3 | 娱乐新闻 | 无       |
|  4 | 体育新闻 | 无       |
| 11 | 国内新闻 | 无       |
+----+----------+----------+

SELECT * FROM news;
+----+-------+---------+--------+
| id | title | content | cateId |
+----+-------+---------+--------+
|  1 | a1    | aaaa1   |     11 |
|  2 | a2    | aaaa2   |     11 |
|  3 | a3    | aaaa3   |      4 |
|  5 | a5    | aaaa5   |      3 |
+----+-------+---------+--------+

为什么需要外键约束？

-- 创建新闻分类表
CREATE TABLE IF NOT EXISTS news_cate(
	id TINYINT KEY AUTO_INCREMENT COMMENT '编号',
    cateName VARCHAR(50) NOT NULL COMMENT '分类名称',
    cateDesc VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '分类描述'
);
INSERT news_cate(cateName)
VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');

-- 创建新闻表
CREATE TABLE IF NOT EXISTS news(
	id INT KEY AUTO_INCREMENT COMMENT '编号',
    title VARCHAR(100) NOT NULL UNIQUE COMMENT '新闻标题',
    content VARCHAR(1000) NOT NULL COMMENT '新闻内容',
    cateId TINYINT NOT NULL COMMENT '新闻所属分类编号'
);
INSERT news(title,content,cateId)
VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

-- 插入一个错误数据
INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);

**问题1：**对于上面的这个错误数据，也就是脏数据，在news_cate中并没有对用的新闻种类与它的caseId对应，这显然是不合适的。

-- 查询news id,title,content
-- news_cate cateName
SELECT n.id,n.title,n.content,nc.cateName
FROM news AS n
INNER JOIN news_cate AS nc
ON n.cateId=nc.id;

+----+-------+---------+----------+
| id | title | content | cateName |
+----+-------+---------+----------+
|  1 | a1    | aaaa1   | 国内新闻 |
|  2 | a2    | aaaa2   | 国内新闻 |
|  3 | a3    | aaaa3   | 体育新闻 |
|  4 | a4    | aaaa4   | 国际新闻 |
|  5 | a5    | aaaa5   | 娱乐新闻 |
+----+-------+---------+----------+

**问题2：**假如这个时候我们将news_cate下的国际新闻删除

DElETE FROM news_cate WHERE id=2;

但是对于news表中的新闻来说，它的caseId还是有2的，那么也就缺少了对应的caseId，这也显然是不合适的。

针对上面两个问题，我们就需要使用我们的外键约束。

今天继续给大家介绍MySQL相关知识，本文主要内容是MySQL外键约束详解。

一、MySQL外键约束作用

外键约束（Foreign Key）即数据库中两个数据表之间的某个列建立的一种联系。这种联系通常是以实际场景中含义完全相同的字段所造成的。MySQL通过外键约束的引入，可以使得数据表中的数据完整性更强，也更符合显示情况。下面，我举一个例子来说明MySQL外键约束的作用。
假如我们对大学学生成绩管理系统建立数据库，有两张表，一张表是学生表，存储了学生的学号、姓名、性别、院系等信息，还有一张表是成绩表，存储了学生学号、课程编号、考试成绩等信息。这样，这两张表之间就会通过学生学号建立外键约束。很自然的我们想到，成绩表的学生学号依赖于学生表的学生学号存在，如果一个学生毕业、或者退学，从学生表中删除时，那么他的相关成绩也就没有必要在成绩表中存在了。在没有创建外键关系之前，这两张表完全是独立存在的，我们可以强行在成绩表中插入一个不存在学生的相关成绩，也可以强行删除学生表中的一个学生，并且不管其成绩信息是否在成绩表中存在。但是，在建立外键关系后，MySQL数据库会约束上述两种行为，每次对数据进行插入或者删除时，都会检查数据完整性，使得我们的操作必须符合实际情况。

二、外键约束创建

（一）创建外键约束的条件

MySQL数据库外键的创建，需要满足以下四个条件，否则会被MySQL数据库拒绝：
1、创建外键的表和列存在
2、组成外键的列存在索引
3、必须指定数据表的引擎为InnoDB
4、外键字段和关联字段，数据类型必须一致

（二）在创建数据表时创建外键约束

在创建数据表时创建外键约束，只需要在创建数据表的create语句后面，使用foreign key关键字指定本表的外键字段，使用reference关键字指定关联表的关联字段，并且明确约束行为即可。
创建外键约束的SQL语句示例如下：

create table student (id int(8),name varchar(20),department varchar(20) ,index (id))ENGINE=InnoDB;
create table grade (Sid int(8),Cid int(10),score int,index(Sid),foreign key (Sid) references student(id) on  delete  restrict on update cascade)ENGINE=InnoDB;

在上述SQL语句中，on delete restrict 是指明在删除时外键会对该删除操作进行限制，而on update cascade是指名在更新时会对该更新操作进行同步。

（三）在创建数据表后添加外键约束

同样的，MySQL也支持在创建数据表后再添加外键约束。在上例中，我们先删除grade表，然后再创建grade表，现不创建外键，尝试在创建grade表后添加外键，相关SQL命令如下：

drop table grade;
create table grade(Sid int(8),Cid int(10),score int);
alter table grade add index(Sid);
alter table grade add foreign key (Sid) references student(id) on delete restrict on update cascade;

执行结果如下：

三、外键约束功能演示

下面，我们就来测试一下外键约束的功能，首先，尝试向grade表插入一个不存在学生的成绩，发现被拒绝：

之后，尝试删除student表中存在成绩的学生，发现被拒绝：

紧接着，我们测试一下MySQL外键约束级联更新功能，发现，如果更改了student表中的数据，grade表也会跟着变动，如下所示：

原创不易，转载请说明出处：https://blog.csdn.net/weixin_40228200

（一）外键约束

• 外键：是指表中某个字段的值依赖于另一个表中的某个字段的值(被依赖的字段必须要有主键约束或者唯一约束）

• 外键约束：用户实现数据库表的参照完整性。外键约束可以使两张表紧密结合起来，特别是对于删除/修改级联操作时，会保证数据的完整性。

• 子表/从表：使用外键约束的表 （学生表：学号，姓名，性别，年龄，班级号）

• 父表/主表：含有被依赖的字段的表（班级表：班级号，班级名）

注意：学生表中含有班级号

（二）外键约束的功能

• 同一个字段有着大量重复数据，使用外键约束后，修改只修改一次父表中的数据即可，节省时间；

注意：外键约束只有表级约束

（三）学生表和班级表

（1）练习

-- 创建主表（班级表）
create table class_table(
	c_id int(4) primary key auto_increment,
	c_name varchar(10)
);


-- 创建从表（学生表）
create table stu_table(
	s_id int PRIMARY key auto_increment,
	s_name varchar(10) not null,
	s_sex char(1) check(s_sex = '男' or s_sex = '女'),
	s_age int(3) check(s_age > 0 and s_age < 100),
	c_id int(4) 
);


-- 班级表添加数据
insert into class_table values(NULL, 'xg1901'), (NULL, 'xg1902');

-- 查看班级表数据
select * from class_table;

-- 学生表中插入数据
insert into stu_table values (NULL, '香菱', '女', 18, 1);
insert into stu_table values (NULL, '行秋', '男', 18, 2);
insert into stu_table values (NULL, '胡桃', '女', 16, 2);
insert into stu_table values (NULL, '班尼特', '男', 18, 1);
-- 查看学生表数据
select * from stu_table;

（2）两个问题

1. 问题一：班级表中只有1， 2 ，那我们在学生表中插入3班学生呢？
   insert into stu_table values (NULL, '测试名', '女', 18, 3 );

结果很明显添加成功，但这种做法就是错误的，因为根本没有c_id = 3的班

2. 问题二：那我们删除班级表中的一条记录呢，那2班的人怎么办？
   比如：删除xg1902
   delete from class_table where c_id = 2;
   
   那么学生表中c_id = 2的学生有没有被删除呢？？很显然并没有
   

（3）解决方案

• 产生原因：未将外键约束语法添加进去

• 添加外键约束(只有表级约束)：

1.创建表时添加表级外键约束
语法：constraint 约束名 foreign key 子表名 (子表字段名) references 父表名 (父表唯一字段名)

-- 创建从表（学生表）
create table stu_table(
	s_id int PRIMARY key auto_increment,
	s_name varchar(10) not null,
	s_sex char(1) check(s_sex = '男' or s_sex = '女'),
	s_age int(3) check(s_age > 0 and s_age < 100),
	c_id int(4),
	
	-- 创建时添加表级外键约束
	constraint fk_c_id  foreign key (c_id) references class_table (c_id)
);

2.对已创建的表添加表级外键约束
alter table 子表名 add constraint 约束名 foreign key 子表名 (子表字段名) references 父表名 (父表唯一字段名);

-- 已存在的表添加表级外键约束
alter table stu_table add constraint fk_c_id  foreign key (c_id) references class_table (c_id);

（4）结果展示

（四）外键策略

（1）策略一：手动置空

（2）策略二：级联操作 on update on delete

创建约束 外键策略;

什么是级联呢？

开启级联后，更新/删除一个主表的主键值（唯一字段），系统会相应的更新/删除所有从表匹配的外键值。

先删除旧的外键约束，在添加含有外键策略（级联操作）的外键约束

• 语法格式：

-- 1.先删除旧的外键约束
alter table 从表名 drop foreign key 外键约束名;

-- 2.重新添加带有级联操作(更新/删除)的外键约束
alter table 从表名 add constraint 外键约束名 foreign key (从表字段名) references 主表名 (主表字段名) on update cascade on delete cascade;

• 示例：

-- 1.先删除旧的外键约束
alter table stu_table drop foreign key fk_c_id;

-- 2.重新添加带有级联操作(更新/删除)的外键约束
alter table stu_table add constraint fk_c_id foreign key (c_id) references class_table (c_id) on update cascade on delete cascade;

-- 级联更新
update class_table set c_id = 1 where c_id = 3;

-- 级联删除
delete from class_table where c_id = 2;

（3）级联置空 set null

举个例子，学生表中有4个2班的学生， 班级表有1班和2班，假设删除班级表中的2班，出现的情况如下：

• 外键约束的外键策略set null：学生表中的所有2班学生的班级号为null