MySQL之外键介绍

引言

表与表之间建关系

定义一张员工表，表中有很多字段
id、name、gender、dep_name、dep_desc

# 1. 该表的组织结构不是很清晰（可忽视）
# 2. 浪费硬盘空间（可忽视）
# 3. 数据的扩展性极差（无法忽视）

# 如何优化？
'''上述问题就类似于你将所有的代码都写在了一个py文件中'''
解决方案：将员工表拆分为部门表

外键（foreign key）

外键就是用来帮助我们建立表与表之间关系的。在了解表关系之前我们先要直到级联更新和级联删除这两个概念。

级联更新和级联删除

添加级联更新和级联删除时需要在外键约束后面添加
在删除父表中的数据时，级联删除子表中的数据 on delete cascade
在更新父表中的数据时，级联更新子表中的数据 on update cascade
以上的级联更新和级联删除谨慎使用，因为级联操作会将数据改变或删除【数据无价】
在修改约束条件时，建议可以将原先的约束删除再重新添加约束条件

使用

删除外键约束

alter table t_student drop foreign key t_student_classno_fk;

添加级联删除的外键约束

alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on delete cascade;

添加级联更新的外键约束

alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on update cascade;

表关系

表与表之间最多只有四种关系
	一对多关系
		在MySQL的关系中没有多对一这么说的，一对多，多对一都叫一对多！！
	多对多关系
	一对一关系
	没有关系

一对多关系

判断表与表之间关系的时候，前期不熟悉的情况下，一定要按照换位思考的思想来看，分别站在两张标的角度考虑
先站在员工表
	思考一个员工能否对应对个部门（一个员工数据能否对应多条部门数据）
	不能（不能直接得出结论，一定要两张表都要考虑完全）
再站在部门表
	思考一个部门能否对应多个员工（一个部门数据能否对应多条员工数据）
	能
然后得出结论
	员工表和部门表为单向的一对多关系
	所以表关系就是一对多
	
foreign key
	1. 一对多关系  外键字段建立在多的一方
	2. 在创建表的时候，一定要先建被关联表，因为被关联表中没有外键约束
	3. 在录入数据的时候，也必须先录入被关联表
# SQL语句建立表关系
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others'),
    dep_id int,
    foreign key(dep_id) references dep(id)
    # 首先声明dep_id是外键字段，其次表明跟哪张表的哪个字段有关系
);
insert into emp(name,dep_id) values('xiao',1);
# ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`day02`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

所以先插部门数据
insert into dep(dep_name,dep_desc) values ('sb教学部','教书与人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('xiao',3),('quan',1),('zheng',2);

# 删除emp里面的dep_id字段或者修改dep表里面的id字段(不行)
update dep set id=200 where id=2;
# ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day02`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
delete from emp;

# 1. 先删除教学部对应的员工数据，之后再删除部门
	操作太过繁琐
# 2. 真正做到数据之间有关系
	更新就同步数据
	删除就同步删除
	
"""
级联更新
级联删除
"""
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others'),
    dep_id int,
    foreign key(dep_id) references dep(id)
    on update cascade # 同步更新
    on delete cascade # 同步删除
);
insert into dep(dep_name,dep_desc) values ('sb教学部','教书与人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('xiao',3),('quan',1),('zheng',2);
select * from dep;
+----+-------------+--------------------------+
| id | dep_name    | dep_desc                 |
+----+-------------+--------------------------+
|  1 | sb教学部    | 教书与人                 |
|  2 | 外交部      | 多人外交                 |
|  3 | nb技术部    | 技术能力有限部门         |
+----+-------------+--------------------------+
select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | xiao  | NULL   |      3 |
|  2 | quan  | NULL   |      1 |
|  3 | zheng | NULL   |      2 |
+----+-------+--------+--------+

update dep set id=200 where id=2;
select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   1 | sb教学部    | 教书与人                 |
|   3 | nb技术部    | 技术能力有限部门         |
| 200 | 外交部      | 多人外交                 |
+-----+-------------+--------------------------+
select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | xiao  | NULL   |      3 |
|  2 | quan  | NULL   |      1 |
|  3 | zheng | NULL   |    200 |
+----+-------+--------+--------+

delete from dep where id=1;
select * from dep;
+-----+-------------+--------------------------+
| id  | dep_name    | dep_desc                 |
+-----+-------------+--------------------------+
|   3 | nb技术部    | 技术能力有限部门         |
| 200 | 外交部      | 多人外交                 |
+-----+-------------+--------------------------+
 select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | xiao  | NULL   |      3 |
|  3 | zheng | NULL   |    200 |
+----+-------+--------+--------+

多对多关系

图书表和作者表
create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
"""
按照上述的方式创建，一个都不会成功
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系，不能在两张表原有的表中创建外键
需要你单独再开设一张专门用来存储两张表数据之间的关系
"""

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int
);

insert into book(title,price) values('java',10000),('python',20000),('go',15000);
insert into author(name,age) values('xiao',18),('quan',77);

create table book2author(
	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
); 
desc book2author;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| author_id | int(11) | YES  | MUL | NULL    |                |
| book_id   | int(11) | YES  | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
insert into book2author(author_id,book_id) values(1,1),(1,2),(2,3);
select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         2 |       3 |
+----+-----------+---------+

delete from author where id = 1;

select * from book;
+----+--------+-------+
| id | title  | price |
+----+--------+-------+
|  1 | java   | 10000 |
|  2 | python | 20000 |
|  3 | go     | 15000 |
+----+--------+-------+
select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  3 |         2 |       3 |
+----+-----------+---------+
通过第三张表来建立两张表之间的联系

一对一关系

"""
id name age addr phone hobby email
如果一个表的字段特别多，每次查询又不是所有的字段都用的到
就可以将表一分为二
例如：
用户表   id name age 
用户详情表  id addr phone hobby email

站在用户表
	一个用户能否对应多个用户详情  不能！！！
站在用户详情表
	一个详情表能否属于多个用户  不能！！！
结论：单向的一对多都不成立，那么这个时候两者之间的表关系就是一对一或者是没关系

"""
"""
客户表和学生表

在报名之前是客户
报名之后是学生，不是所有客户都能成为学生
"""

一对一 外键字段建在任意一方都可以，但是推荐你建在查询频率比较高的表中

create table authordetail(
	id int primary key auto_increment,
    phone int,
    addr varchar(32)
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id)
    on update cascade
    on delete cascade
);

总结

表关系的建立需要用到foreign key
- 一对多
  外键字段建在多的一方
- 多对多
  自己开设第三张表存储
- 一对一
  建在任意一方都可以，但是推荐你建在查询频率比较高的表中
判断表之间关系的方式：换位思考
- 员工与部门
- 图书与作者
- 作者与作者详情
删除带有外键约束的表
- 先删除关联表
- 再删除被关联表

posted @ 2024-03-03 18:11 Xiao0101 阅读(17) 评论(0) 收藏举报

刷新页面返回顶部

xiao

MySQL之外键介绍

引言

表与表之间建关系

外键（foreign key）

级联更新和级联删除

使用

表关系

一对多关系

多对多关系

一对一关系

总结

公告