MySQL 外键(FOREIGN KEY)用法案例详解
时间：2021-11-18 14:11:14|栏目：Mysql|点击： 次
引子：把所有数据都存放于一张表的弊端
- 表的组织结构复杂不清晰
- 浪费空间
- 扩展性极差
为了解决上述的问题，就需要用多张表来存放数据。
表与表的记录之间存在着三种关系：一对多、多对多、一对一的关系。
处理表之间关系问题就会利用到FOREIGN KEY
多对一关系：
寻找表与表之间的关系的套路
举例：雇员表：emp表 部门：dep表
part1：
- 先站在表emp的角度
- 去找表emp的多条记录能否对应表dep的一条记录。
- 翻译2的意义：
左表emp的多条记录==》多个员工
右表dep的一条记录==》一个部门
最终翻译结果：多个员工是否可以属于一个部门？
如果是则需要进行part2的流程
part2：
- 站在表dep的角度
- 去找表dep的多条记录能否对应表emp的一条记录
- 翻译2的意义：
右表dep的多条记录==》多个部门
左表emp的一条记录==》一个员工
最终翻译结果：多个部门是否可以包含同一个员工
如果不可以，则可以确定emp与dep的关系只一个单向的多对一
如何实现？
此时就可以用到外键了，在emp表中新增一个dep_id字段，该字段指向dep表的id字段
foreign key会带来什么样的效果？
约束1：在创建表时，先建被关联的表dep，才能建关联表emp
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp
insert into dep(dep_name,dep_comment) values
('教学部','辅导学生学习，教授课程'),
('公关部','处理公关危机'),
('技术部','开发项目，研究技术');
insert into emp(name,gender,dep_id) values
('monicx0','male',1),
('monicx1','male',2),
('monicx2','male',1),
('monicx3','male',1),
('lili','female',3);
约束3：更新与删除都需要考虑到关联与被关联的关系。
解决方案：
1、先删除关联表emp，再删除被关联表dep，准备重建
2、重建：新增功能，同步更新，同步删除
create table dep(
id int primary key auto_increment,
dep_name char(10),
dep_comment char(60)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade
on delete cascade
);
此时再去修改：
得到结果：
此时再去删除：
得到结果：
多对多的关系：
两张表记录之间是一个双向的多对一关系，称之为多对多关系。
如何实现？
建立第三张表，该表中有一个字段foreign key左表的id，还有一个字段是foreign key右表的id
create table author(
id int primary key auto_increment,
name char(16)
);
create table book(
id int primary key auto_increment,
bname char(16),
price int
);
insert into author(name) values
('monicx1'),
('monicx2'),
('monicx3')
;
insert into book(bname,price) values
('python从入门到入土',200),
('liunx从入门到入土',400),
('java从入门到入土',300),
('php从入门到入土',100)
;
#建立第三张表：
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
insert into author2book(author_id,book_id) values
(1,3),
(1,4),
(2,2),
(2,4),
(3,1),
(3,2),
一对一关系左表的一条记录唯一对应右表的一条记录，反之也一样
create table customer(
id int primary key auto_increment,
name char(20) not null,
qq char(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name char(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #此时外键的字段一定要保证unique
on delete cascade
on update cascade
);
上一篇：mysql tmp_table_size优化之设置多大合适
栏 目：Mysql
下一篇：MySQL服务维护笔记第1/2页
本文标题：MySQL 外键(FOREIGN KEY)用法案例详解
本文地址：http://www.codeinn.net/misctech/183905.html
