表之间的关系
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), );