MySQL 外键(FOREIGN KEY)用法案例详解

引子：把所有数据都存放于一张表的弊端

表的组织结构复杂不清晰
浪费空间
扩展性极差

为了解决上述的问题，就需要用多张表来存放数据。

表与表的记录之间存在着三种关系：一对多、多对多、一对一的关系。

处理表之间关系问题就会利用到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));

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);

解决方案：

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);

多对一关系：

多对多的关系：

更多相关文章

随机推荐