mysql外键的说明和实例详解

最近很多朋友面试的时候遇到了关于外键的问题,因为经验少项目中很少使用,所以面试的时候也很难说清其用法和优势,这里我简单的总结了一些关于外键使用和小例子,让大家比较直白的理解外键的优点和使用方法。

一、外键使用的条件

1、记住外键是两个表之间发生的事,所以首要条件是要有两张表,其次,存储引擎的选择,有些不支持外键的存储引擎肯定是不能使用的,比如MyISAM暂时就不支持外键,所以我们两张表都要选择如Innodb这样支持外键的引擎。

2、外键列最好创建索引,目前使用的高版本mysql也会在建立外键时自动创建索引。

3、外键关系的两个表中的字段数据类型必须相同或相似,不能出现int和varchar这种类型差别较大互相关联。

二、外键约束的区别作用

我们在创建外键的时候,如果没有加上外键约束,它也会有自己默认的动作RESTRICT。

1、CASCADE:通常分为ON DELETE CASCADE(若父表中做删除操作,同时自动删除表中匹配的行)和ON UPDATE CASCADE(若父表中修改某数据,同时自动修改表中匹配的数据),实践中很常用。

2、SET NULL:若父表中删除或修改,同时将子表中的外键列设为空。注意:外键列没有被设为not null时才生效。同样也有两种模式:ON DELETE SET NULL、ON UPDATE SET NULL

3、NO ACTION:子表有关联数据时,Innodb拒绝删除或更新父表。

4、RESTRICT:和NO ACTION相似(也分为 ON DELETE RESTRICT和ON UPDATE RESTRICT两种)。

实际项目中比较常用的还是两种情况:

1)、子表随父表更新,但不会随父表删除 :ON UPDATE CASCADE ON DELETE RESTRICT

2)、子表随父表更新和删除:ON DELETE CASCADE ON UPDATE CASCADE

三、举例说明

1、我们用一个教室表(room)和学生表(student)两张表来做一个实例,如下:

mysql> create table room(id int(10) primary key auto_increment,name varchar(20))
    -> engine=Innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> create table student(id int(10) primary key auto_increment,rid int(10),name varchar(5),
       -> key rid (rid),
    -> constraint room_student foreign key (rid) references room (id)
    -> )engine=Innodb default charset=utf8;
Query OK, 0 rows affected (0.00 sec

这样我们就建成了两张表,student表中的外键rid生效了,下面我们开始向room表插入数据。

mysql> insert into room(name) values("1班"),("2班"),("3班");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from room;
+----+------+
| id | name |
+----+------+
|  1 | 1班  |
|  2 | 2班  |
|  3 | 3班  |
+----+------+
3 rows in set (0.00 sec)

可以看到班级数据生成成功,那我们开始给班级里添加学生,向student表插入数据。

mysql> insert into student(rid,name) values(1,"小李"),(2,"小红");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | rid  | name |
+----+------+------+
|  1 |    1 | 小李 |
|  2 |    2 | 小红 |
+----+------+------+
2 rows in set (0.00 sec)

学生生成成功,因为我们学生插入的是存在的班级,那我们向不存在的班级中插入学生会怎样呢?

mysql> insert into student(rid,name) values(3,"小白"),(4,"小黑");
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `room_student` FOREIGN KEY (`rid`) REFERENCES `room` (`id`))
mysql> select * from student;
+----+------+------+
| id | rid  | name |
+----+------+------+
|  1 |    1 | 小李 |
|  2 |    2 | 小红 |
+----+------+------+
2 rows in set (0.00 sec)

果然,把学生生成在一个不存在的班级中,学校是不允许的。

我们多添加些有用的数据,后面继续测试。

mysql> insert into student(rid,name) values(3,"小白"),(3,"小黑"),(1,'小花'),(2,'小猪');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | rid  | name |
+----+------+------+
|  1 |    1 | 小李 |
|  2 |    2 | 小红 |
|  5 |    3 | 小白 |
|  6 |    3 | 小黑 |
|  7 |    1 | 小花 |
|  8 |    2 | 小猪 |
+----+------+------+
6 rows in set (0.00 sec)

2、第二步我们测试对父表的修改和删除

mysql> delete from room where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `room_student` FOREIGN KEY (`rid`) REFERENCES `room` (`id`))
mysql> update room set id=10 where id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `room_student` FOREIGN KEY (`rid`) REFERENCES `room` (`id`))

当还有学生的时候,删除和修改教室的信息都不行,学生们不乐意了。删除和修改也可以,但必须把学生们先安顿好。比如:取消教室要先把学生清光,修改教室地址,要先把学生安排到新的地址去才行。

下面我们对外键约束做一定的修改

mysql> alter table student drop foreign key room_student;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> alter table student add foreign key (rid) references room (id) on delete cascade on update cascade;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

修改成功之后,我们再测试修改班级和删除班级试试。

mysql> update room set id=10 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from room;
+----+------+
| id | name |
+----+------+
|  1 | 1班  |
|  3 | 3班  |
| 10 | 2班  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | rid  | name |
+----+------+------+
|  1 |    1 | 小李 |
|  2 |   10 | 小红 |
|  5 |    3 | 小白 |
|  6 |    3 | 小黑 |
|  7 |    1 | 小花 |
|  8 |   10 | 小猪 |
+----+------+------+
6 rows in set (0.00 sec)

学生们都被换到新的地址去了,教室搬迁成功。

mysql> delete from room where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from room;
+----+------+
| id | name |
+----+------+
|  1 | 1班  |
| 10 | 2班  |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | rid  | name |
+----+------+------+
|  1 |    1 | 小李 |
|  2 |   10 | 小红 |
|  7 |    1 | 小花 |
|  8 |   10 | 小猪 |
+----+------+------+
4 rows in set (0.00 sec)

3班被解散了,所有的学生也都被遣散回家了。当然,有的项目对数据安全要求比较高,通常只会设置可以修改但不能删除。就像商城网站想删除某个分类,就需要先判断该分类是否还有商品,不会一股脑子都给删除了,毕竟商品信息还是很重要的。

那我们如果更牛逼的直接删除父表会怎样呢?

mysql> drop table room;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

很显然,不成功,这样直接把学校拆了不安顿学生,学校肯定有责任要受罚的,所以,不可以。

mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table room;
Query OK, 0 rows affected (0.00 sec)

Look!先遣散了学生,再炸了学校,这才是正确的操作。

                  你们的支持是我前进的动力!感谢大家来访!

发表评论