mysql表之间的关系及级联操作

表之间的关系

foreign key

外键约束,用于指向另一个表的主键字段

# 创建表的时候添加外键
create table teacher(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        dept_id int,
    	foreign key(dept_id) references dept(id)
	);
解释:
foreign key(dept_id) references dept(id)
dept_id 表示当前表的外键字段  
dept 表示要关联哪个表 
dept(id)  id表示关联的dept表的id字段 


主表与从表  
先有主表 再有从表  
先有dept 再有teacher 

foreign key 带来的约束作用:

1.在从表中插入一条记录,关联了一个主表中不存在的id 导致插入失败

必须保证部门id (外键的值) 必须是在主表中存在的

2.插入数据的顺序

​ 先插入主表记录 在插入从表记录

3.从表更新外键时也必须保证 外键的值在主表中是存在的

4.删除主表记录前 要保证从表中没有外键关联被删除的id

​ delete from dept where id = 1;# 报错

​ 必须先删除从表 再删除主表

​ delete from teacher where dept_id = 1;

​ delete from dept where id = 1;

5.更新主表记录的主键时 要保证从表中没有外键关联被删除的id

6.必须先创建主表

7.删除表 必须先删除从表

强调:foreign key就是用来保证两张表之间的关联关系是正确的!

练习： 班级表 和 学员表

主表是班级

从表是学员 外键加给学员

create table class(
        id int primary key auto_increment,
        name char(20)
	);
	
create table student(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        c_id int,
    	foreign key(c_id) references class(id)
	);
	

级联操作 cascade

当我们需要删除部门(主表)信息 时,必须先删除从表中关联的数据,很麻烦

级联操作指的就是,当你操作主表时,自动的操作从表

两种级联操作

1.级联的删除

​ 当删除主表时自动删除从表中相关数据

2.级联更新

​ 当主表的主键更新时自动的更新关联的从表数据

案例:以上面的班级个学员为例:

drop table if exists class;
# 如果这表存在 才执行删除  可以避免报错  if exists
# if not exists  如果不存在才执行
create table class(
        id int primary key auto_increment,
        name char(20)
	);
insert into class values(null,"py9");
insert into class values(null,"py10");

#创建表的时候指定级联操作 
drop table if exists student;
create table student(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        c_id int,
    	foreign key(c_id) references class(id)
         on update cascade
   	 	on delete cascade
	);
# 级联操作可以单独使用 也可以一起使用   空格隔开即可 
insert into student values(null,"jack","m",1);
insert into student values(null,"rose","m",1);
insert into student values(null,"tom","m",2);

外键的使用

什么时候使用外键:

​ 表之间存在关联关系 ,

首先要确定表之间的关系:

多对一

​ 一对多(多对一)

或者

老师和部门的关系 
老师的角度看
	一个老师应该对应有一个部门 
	一个老师可以对应对多个部门?  不行 一个老师只能属于一个部门 (要看具体业务要求)!
	多个老师可以对应一个部门 
	多对一
部门的角度看
	一个部门可以对应多个老师
	一个部门可以对应一个老师
	多个部门可以对应一个老师? 不行 
	一对多 
如何处理一对多(多对一)?
	在老师表中存储 部门id
	即多的一方存储 一的一方的id
	

处理方式

​ 在一的一方即teacher表中保存相应部门(多的一方)的编号;

案例:

	#部门:
	create table dept(
        id int primary key auto_increment,
        name char(20),
        job char(50),
        manager char(10)
	);
	#老师表:
	create table teacher(
        id int primary key auto_increment,
        name char(20),
        gender char(1),
        dept_id int,
        foreign key(t_id) references teacher(id),
	);