--创建学生信息表 create table StInfo( StuNum varchar(20), StuName varchar(20), sex varchar(10), department varchar(30) ); --插入学生数据 insert into StInfo values('001','张三','男','计算机系'),('002','李四','男','计算机系'),('003','王五','女','体育系'),('004','铁蛋','女','体育系'); --查询表数据 select* from StInfo;
对于上表,我们发现department列存在数据冗余,如果计算机系改名为计算机部,需要修改多个数据,那怎么解决呢?
2. 解决办法:思路:创建两个表,学生表和系表,如下图,想修改计算机系为计算机部的话,只需要通过建立外键和级联,直接修改depmt系表当中的计算机系为计算机部即可。
(1)步骤一:创建两个表学生表和部门表,为学生表添加外键。
create table StInfo( StuNum varchar(20), StuName varchar(20), sex varchar(10), dep_id int, --设置外键 --格式:constraint 外键名 foreign key (本表列名) reference 主表名(主表列名); constraint s_d_id foreign key (dep_id) references depmt(id) ); insert into StInfo values('001','张三','男','1'),('002','李四','男','1'),('003','王五','女','2'),('004','铁蛋','女','2'); select* from StInfo;
create table depmt( id int PRIMARY key, department varchar(20) ); insert into depmt values(1,'计算机系'),(2,'体育系'); select *from depmt;
添加外键后,修改、删除、添加外键列数据有了限制
--比如修改dep_id=3操作报错id,因为只有1和2 update StInfo set dep_id = 3 where dep_id=1; > 1452 - Cannot add or update a child row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`)) update depmt set id = 3 where id = 1; > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`)) --删除外键关联列,报错 alter table depmt drop id; > 1025 - Error on rename of '.\db1\#sql-c80_b' to '.\db1\depmt' (errno: 150) alter table StInfo drop dep_id; > 1025 - Error on rename of '.\db1\#sql-c80_b' to '.\db1\StInfo' (errno: 150) --添加数据时,dep_id只能为1或2,否则报错 insert into StInfo values('005','钢蛋','男','3'); > 1452 - Cannot add or update a child row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`))
(2)步骤二:创建级联更新和级联删除:
--补充知识:建表后的删除外键和创建外键操作 删除:alter table 表名 drop foreign key 外键名; 创建:alter table 表名 add constraint 外键名 foreign key (本表列名) reference 主表名(主表列名); 级联更新和级联删除: 1. 级联更新:ON UPDATE CASCADE 2. 级联删除:ON DELETE CASCADE --对StInfo创建级联更新和级联删除 alter table StInfo drop foreign key s_d_id;--删除外键 alter table StInfo add constraint s_d_id foreign key (dep_id) references depmt(id) on update cascade on delete cascade;--创建外键、更新删除级联 --级联创建完成了!验证一下 --将depmt表id=1修改为3,观察StInfo表中dep_id=1的是否变为3 update depmt set id=3 where id=1; select* from depmt; select* from StInfo;
--在depmt中删除id=3的行,发现StInfo中dep_id=3的也被删除了 delete from depmt where id =3; select* from depmt; select* from StInfo;
**总结:(1)添加外键后,消除了部分数据冗余,只需要将depmt系表中的计算机系修改为计算机部,只改动了一个数据即可.
(2)添加级联后,提升了工作效率,只需要修改或删除depmt中的id,StInfo中所有dep_id=id的都发生改变。
**