最近很多朋友面试的时候遇到了关于外键的问题,因为经验少项目中很少使用,所以面试的时候也很难说清其用法和优势,这里我简单的总结了一些关于外键使用和小例子,让大家比较直白的理解外键的优点和使用方法。
一、外键使用的条件
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!先遣散了学生,再炸了学校,这才是正确的操作。