foreign key外键和表关系

六. foreign key

强调: 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一

1. 定义一张员工表的三个缺点

employee /ɪmˈplɔɪiː/ 雇员 员工 雇员

id	emp_name	emp_gender	dep_name	dep_desc
1	jason	male	外交部	漂泊游荡
2	egon	female	教学部	教书育人
3	tank	male	教学部	教书育人
4	kevin	male	教学部	教书育人
5	oscar	female	技术部	技术能力有限部门

Copy# 定义一张员工表 表中有很多字段. 我们从中可以看出以上表的三个缺点
"""
<1> 该表的组织结构不是很清晰(可忽视)
<2> 浪费硬盘空间(可忽视)
<3> 数据的扩展性极差(无法忽视的)
"""

2. 优化: 将员工表拆分. 拆分成员工表和部门表

员工表: emp

employee /ɪmˈplɔɪiː/ 雇员 员工 雇员

id	emp_name	emp_gender
1	jason	male
2	egon	female
3	tank	male
4	kevin	male
5	oscar	female

部门表: dep

department /dɪˈpɑːtmənt/ 部门

id	dep_name	dep_desc
1	外交部	漂泊游荡
2	教学部	教书育人
3	技术部	技术能力有限部门

3. 外键foreign key介绍

Copy"""
外键就是用 来帮助我们建立表与表之间关系的
"""

4. 四种表关系介绍

Copy# 表与表之间最多只有四种关系:
"""
# 注意!!: 得出表之间的关系结论一定要进行双向的对比. 
<1> 一对多关系
<2> 多对多关系
<3> 一对一关系
<4> 没有关系(了解)
"""

5. 使用foreign key建立一对多关系

员工表: emp

employee /ɪmˈplɔɪiː/ 雇员 员工 雇员

id	emp_name	emp_gender	dep_id
1	jason	male	1
2	egon	female	2
3	tank	male	2
4	kevin	male	2
5	oscar	female	3

部门表: dep

department /dɪˈpɑːtmənt/ 部门

id	dep_name	dep_desc
1	外交部	漂泊游荡
2	教学部	教书育人
3	技术部	技术能力有限部门

Copy# 先确定表与表之间的关系
"""
先站在员工表考虑: 员工表里面的一个员工能否对应部门表里面的多个部门. --> 不能!!
    再站在部门表考虑: 部门表里面的一个部门能否对应员工表里面的多个员工. --> 可以!!
    结论: 员工表与部门表只是单向的一对多，那么员工表和部门表就是"一对多"的表关系.
"""

"""
注意1: 一对多表关系   外键字段建在多的一方.  --> emp
注意2: 在创建表的时候 一定要先建立被关联表.  -->  dep
注意3: 在插入数据的时候 必须先往被关联表插值. --> dep

# foreign key使用完整语法
    foreign key(关联表字段) references 表名(被关联表字段) on update cascade on delete cascade
"""

# =================== 使用foreign key建立关联 ===================
create table dep(
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);
insert into dep(dep_name, dep_desc) values('外交部', '漂泊游荡'), ('教学部', '教书育人'), ('技术部', '技术能力有限部门');

create table emp(
    id int primary key auto_increment,
    emp_name varchar(16),
    emp_gender enum('male', 'female'),
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into emp(emp_name, emp_gender, dep_id) values('jason', 'male', 1), ('egon', 'female', 2), ('tank', 'male', 2), ('kevin', 'male', 2), ('oscar', 'female', 3);
desc dep;
desc emp;
select * from dep;
select * from emp;

# 使用外键关联以后2表被外键关联的字段所对应的记录都不能进行更新. 员工表的: dep_id  部门部门表的: id
update emp set dep_id=200 where dep_id=2;
update dep set id=200 where id=2;

# 使用外键的部门表不能进行记录的删除.
delete from dep where id=2;
delete from emp where id=2; # 员工表可以




"""删除的2种方式"""
# 第一种: 先删除关联表(emp), 再删除被关联表(dep). 无论是争对记录的删除delete, 还是表的删除规则都是不变.
delete from emp where id=1;
delete from dep where id=1;

# 第二种: 使用级联更新, 级联删除
'''
级联更新(同步更新) on update cascade
级联删除(同步删除) on delete cascade
'''
drop table emp;
drop table dep;
create table dep(
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);
insert into dep(dep_name, dep_desc) values('外交部', '漂泊游荡'), ('教学部', '教书育人'), ('技术部', '技术能力有限部门');

create table emp(
    id int primary key auto_increment,
    emp_name varchar(16),
    emp_gender enum('male', 'female'),
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);
insert into emp(emp_name, emp_gender, dep_id) values('jason', 'male', 1), ('egon', 'female', 2), ('tank', 'male', 2), ('kevin', 'male', 2), ('oscar', 'female', 3);
desc dep;
desc emp;
select * from dep;
select * from emp;

# 更新部门表的id. 员工表中得dep_id也会发生同样的改变
update dep set id=200 where id=2;
select * from dep;
select * from emp;

# 删除某一个部门表. 与部门表所关联的所有员工都会被清除.
delete from dep where id=1;
select * from dep;
select * from emp;

6. 使用foreign key建立多对多关系

注意: 争对多对多的表关系, 不能在两张原有的表中创建外键进行关联. 需要建立中间表来关联2表之间的关系.

图书表: book

id	title	price	author_id
1	python入门到放弃	21000	1,2
2	葵花宝典	6666	1,2
3	前端	9	1
4	水浒传	123	2

作者表: author

id	name	age	book_id
1	jsaon	18	1,2
2	egon	78	3,4

Copy# 先确定表与表之间的关系
"""
先站在书籍表考虑: 一本书可不可以有多个作者. --> 可以!!
    再站在作者表考虑: 一个作者可不可以写多本书. --> 可以!!
    结论: 书籍表和作者表是双向的多对多, 那么表关系就是"多对多"关系.
"""

create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(autor_id) references author(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

create table author(
    id int primary key auto_increment,
    title varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);

"""按照上述的方式创建 一个都别想成功！！"""

中间表: book2author

注意1: 如果一本书有多个作者必须重新开启一行.

注意2: 建立了中间表以后中间表与上面2表是多对一的关系. 新建的中间表是多的一方, 上面2表是一的一方. 所以因该先创建上面2表, 再创建需要建立外键关联中间表

• 一个book2author中的id可以对应多个作者. --> 不成立
• 一个作者可以对应多个book2author中的多个id --> 成立

id	book_id	author_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	2
6	4	1

Copycreate table book(
    id int primary key auto_increment,
    title varchar(16) not null,
    price int not null
);
insert into book(title, price) values('python入门到放弃', 21000), ('葵花宝典', 6666), ('前端', 9), ('水浒传', 123);

create table author(
    id int primary key auto_increment,
    name varchar(16) not null,
    age int not null
);
insert into author(name, age) values('jsaon', 18), ('egon', 78);


# 针对多对多字段表关系 不能在两张原有的表中创建外键 需要你单独再开设一张 专门用来存储两张表数据之间的关系
create table book2author(
    id int primary key auto_increment,
    book_id int not null,
    author_id int not null,
    foreign key(book_id) references book(id)
    on update cascade   # 级联更新
    on delete cascade,  # 级联删除
    foreign key(author_id) references author(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);
insert into  book2author(book_id, author_id) values(1, 1), (1, 2), (2, 1), (2, 2), (2, 2), (3, 2), (4, 1);

select * from book;
select * from author;
select * from book2author;

7. 使用foreign key建立一对一关系

注意: 对于一对一关系表authordetail_id使用过就不能使用, 应该在外键的基础之上使用unique.

detail /ˈdiːteɪl/ 细节 详情 零件

id	name	age	authordetail_id
1	jsaon	18	1
2	egon	73	2

作者详情表: authordetail

id	postcode	address
1	335000	鹰潭市
2	200050	上海市

Copy# 先确定表与表之间的关系
"""
站在作者的角度:
    一个作者可以对象多个用户详情. --> 不成立
站在详情的角度:
    一个用户详情可以对应多个作者. --> 不成立
结论: 一对一    
"""
# 强调!!!: 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中(author)

create table authordetail(
    id int primary key auto_increment,
    postcode int not null,
    address varchar(64) not null
);
insert into authordetail(postcode, address) values(335000, '鹰潭市'), (200050, '上海市');

create table author(
    id int primary key auto_increment,
    name varchar(16) not null,
    age int not null,
    authordetail_id int not null unique,  # 注意: 对于一对一关系表authordetail_id使用过就不能使用, 应该在外键的基础之上使用unique.
    foreign key(authordetail_id) references authordetail(id) 
    on update cascade
    on delete cascade  
);
insert into author(name, age, authordetail_id) values('jsaon', 18, 1), ('egon', 73, 2);

select * from author;
select * from authordetail;

8. 总结

Copy# 表关系的建立需要用到foreign key
"""
一对多:  外键字段建立在多的一方
多对多:  开设中间表存储
一对一:  外键字段建立在任意一方都可以, 但是推荐建立在查询频率比较高的一方
"""

# 判断表之间关系的方式
"""
从多个角度切入. 如果是2表之间的关系那么只有在2表都换位思考了以后, 才能进行判断表与表之间的关系.
"""

# 使用
foreign key(关联表字段) references 被关联表表名(被关联字段) 
on update cascade 级联更新(同步更新)
on delete cascade 级联删除(同步删除)