面试之需要知道MYSQL外键知识(添加、删除)

本文为博主原创文章版权归作者和CSDN共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

本文链接：https://blog.csdn.net/javaee_gao/article/details/114094826

前言

每年的三月、四月是跳槽的高峰期，也是我们面试的黄金期。同时如何在众多面试者中脱颖而出，面试考察重点除了我们的项目开发经验还有关于知识点的考察。正所谓：工作拧螺丝，面试造火箭。

外键(FOREIGN KEY)

何为外键？现在你知道外键是维系两张表之间的一个列，作用是为了维系表数据的完整性和一致性。外键列所在的表为从表而外键指向另一张表的主键即为主表。外键一旦创建后如果外键列对应主表主键与此关联，则无法删除此外键所在的一行数据。

创建外键

首先创建主表并添加几条数据。

mysql> insert into tb_department(name) values('测试人员'),('开发人员'),('UI设计');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from tb_department;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 测试人员     |
|  2 | 开发人员     |
|  3 | UI设计       |
+----+--------------+
3 rows in set (0.00 sec)

接下来我们在创建从表，再而创建外键。创建外键方式大致有如下两种：

创建表时添加外键

mysql> create table tb_employee
(id int primary key auto_increment,
name varchar(255),
dept_id int, 
constraint fk_dept foreign key(dept_id) references tb_department(id));
Query OK, 0 rows affected (0.01 sec)

已有表上添加外键

mysql> alter table tb_employee add constraint fk_dept1 foreign key(dept_id) references tb_department(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表结构：

mysql> show create table tb_employee;
+-------------+--------------------------------------------------------
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------+---------------------------------------------------------+
| tb_employee | CREATE TABLE `tb_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_dept1` (`dept_id`),
  CONSTRAINT `fk_dept` FOREIGN KEY (`dept_id`) REFERENCES `tb_department` (`id`),
  CONSTRAINT `fk_dept1` FOREIGN KEY (`dept_id`) REFERENCES `tb_department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到这张表里面添加了两条外键数据，接下来我们删除最后一次添加的外键。

删除外键

mysql> alter table tb_employee drop foreign key fk_dept1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加外键数据
接下来我们向从表添加两条数据：

mysql> insert into tb_employee(name,dept_id) values('张三',1),('李四',2),('王五',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询从表数据

mysql> select * from tb_employee;
+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
|  1 | 张三   |       1 |
|  2 | 李四   |       2 |
|  3 | 王五   |       3 |
+----+--------+---------+
3 rows in set (0.00 sec)

接下来我们删除主表的数据以验证外键的数据完整性作用。

mysql> delete from tb_department where id =1;

当执行上述SQL语句则会给我提示如下错误：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`tb_employee`, CONSTRAINT `fk_dept` FOREIGN KEY (`dept_id`) REFERENCES `tb_department` (`id`))

因为我们从表有外键引用主表的主键，如果此时主表的数据被删除了那么从表上的外键上的数字则是无意义的。

注意事项

只有表引擎为InnoDB才支持外键以及事务，MYSQL 5.5 之前默认引擎MyISAM不支持外键以及事务，所以演示外键的使用只有引擎为InnoDB才有意义。
在从表创建外键时可以指定在删除、更新主表时对从表相应进行的操作，这些操作包括：RESTRICT、CASCADE、SET NULL、NO ACTION。
首先我们需要知道的是RESTRICT与NO ACTION 作用是一致的即在从表有关联主表记录的情况下限制主表进行更新。CASCADE表示主表在进行更新或者删除时，同步更新和删除从表对应的记录。SET NULL则表达主表在更新或删除的时候，从表的对应的记录为空。下面将演示其使用：

## 首先删除原来从表的外键
mysql> alter table tb_employee drop foreign key fk_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

RESTRICT与 NO ACTION

 alter table tb_employee add constraint fk_dept foreign key(dept_id) references tb_department(id) on delete restrict on update restrict ;

此时我们更新和删除主表的数据如下：

## 更新主表
mysql> update tb_department set id =5 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`tb_employee`, CONSTRAINT `fk_dept` FOREIGN KEY (`dept_id`) REFERENCES `tb_department` (`id`))
## 删除主表
mysql> delete from  tb_department  where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`tb_employee`, CONSTRAINT `fk_dept` FOREIGN KEY (`dept_id`) REFERENCES `tb_department` (`id`))

我们发现无论删除和更新主表的时候是不允许操作的。NO ACTION 与 RESTRICT 作用一致。

CASCADE

## 首先删除原来从表的外键
mysql> alter table tb_employee drop foreign key fk_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来我们使用CASCADE

mysql>  alter table tb_employee add constraint fk_dept foreign key(dept_id) references tb_department(id) on delete cascade on update cascade ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

接下来我们更新主表数据如下：

mysql> select * from tb_department;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 测试人员     |
|  2 | 开发人员     |
|  3 | UI设计       |
+----+--------------+
3 rows in set (0.00 sec)
## 更新主表数据
mysql> update tb_department set id =5 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
## 查看从表数据如下
mysql> select * from tb_employee;
+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
|  1 | 张三   |       5 |
|  2 | 李四   |       2 |
|  3 | 王五   |       3 |
+----+--------+---------+
3 rows in set (0.00 sec)

我们发现当更新主表的主键id后，从表name为张三的列的dept_id 已经更新为5了。接下来我们删除主表的数据。

## 删除主表数据
mysql> delete from tb_department where id =5;
Query OK, 1 row affected (0.00 sec)
## 查看从表数据
mysql> select * from tb_employee;
+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
|  2 | 李四   |       2 |
|  3 | 王五   |       3 |
+----+--------+---------+
2 rows in set (0.00 sec)

我们发现删除主表数据后，从name为张三的那一条数据也被删除了。所以在实际操作中我们需要对删除进行限制对更新设置为同步操作。

SET NULL

## 首先删除原来从表的外键
mysql> alter table tb_employee drop foreign key fk_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来我们使用SET NULL

mysql> alter table tb_employee add constraint fk_dept foreign key(dept_id) references tb_department(id) on delete set null on update set null;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

更新主表数据如下：

## 更新主表
mysql> update tb_department set id=5 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
## 查询从表
mysql> select * from tb_employee;
+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
|  2 | 李四   |    NULL |
|  3 | 王五   |       3 |
+----+--------+---------+
2 rows in set (0.00 sec)

我们发现从表name为李四的对应记录的dept_id为null了，接下来我们删除主表记录如下：

## 删除主表记录
mysql> delete from tb_department where id =5;
Query OK, 1 row affected (0.00 sec)

## 查询从表记录
mysql> select * from tb_employee;
+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
|  2 | 李四   |    NULL |
|  3 | 王五   |       3 |
+----+--------+---------+
2 rows in set (0.00 sec)