多表设计(外键)
文章目录
前言
概述:在项目开发中,在进行数据库结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系基本分为三种(一对多、多对多、)。
一、一对多(多对一)
在使用外键之前需要了解子表(从表)、父表(父表)
子表:它是由多条数据来对应父表中的一条数据,一般来说如果要创建外键就需要在子表上面创建,可以简单的理解为儿子表。
父表:可以简单的理解为父亲,而一个父亲可以有多个儿子。
列如:多个员工,对应一个部门其中员工表就是子表,部门表就是父表
1.概念
一对多的意思就是将一张表与多张表联系在一起
列如:一个部门可以有多个员工。其中部门与员工之间的关系就是一对多的一个关系
2.创建外键语法:
constraint foreign key (子表与父表之间的关系字段) references 父表(父表与子表之间的关系字段)
3.如何创建
方法一:
创建表时完成一对多的关系
#首先创建两个表在创建子表的时候对需要外键的字段添加外键约束
# 部门
create table tb2_dept(
id int unsigned auto_increment primary key comment 'ID',
deptName varchar(10) not null unique comment'部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
)comment '部门';
# 员工
create table tb2_emp
(
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间',
# 创建外键约束
constraint foreign key (dept_id) references tb2_dept(id)
) comment '员工表';
当创建完表时想要在创建外键可以直接使用alter … add…
#列如:
alter table tb2_emp add constraint foreign key (dept_id) references tb2_dept(id);
方法二:
使用编辑器中的图形化界面创建外键约束。
外键约束示意图:
二、一对一
1、概念
一对一可以理解为特殊的一对多,只不过是将其中用来连接的字段加上一个唯一的约束(unique)
2、创建一对一
create table tb_user(
id int primary key not null auto_increment comment'ID',
name varchar(10) not null comment'学科名称'
) comment'课程';
create table tb_chinaId(
id int primary key auto_increment not null,
name varchar(10) not null comment'学生名字',
no int not null comment'学号',
#在链接字段使用唯一约束(unique)
userId int unique comment'用户ID',
#创建一对一外键
constraint foreign key (userId) references tb_user(id),
)comment '学生';
三、多对多
1.概念
多对多就是两张表的数据时多对多的。
2.如何来完成多对多的关系:
当想要完成多对多的关系时需要,除了需要准备两个表之外还要准备一个第三张表。并在第三张表中创建三个字段:
一个字段是这张表的ID
一个字段是用来关联第一个表的
一个字段使用来关联第二张表
这样子就可以完成两个表的多对多的关系
3.创建多对多关系的表
# 多对多
create table tb_course(
id int primary key not null auto_increment comment'ID',
name varchar(10) not null comment'学科名称'
) comment'课程';
create table tb_student(
id int primary key auto_increment not null,
name varchar(10) not null comment'学生名字',
no int not null comment'学号'
)comment '学生';
create table tb_student_course(
id int primary key not null auto_increment comment'ID',
studentId int comment'学生id',
courseId int comment'学科ID',
# 创建外键
constraint foreign key (studentId) references tb_student(id),
constraint foreign key (courseId) references tb_course(id)
)
多对多关系示意图:
四、外键的删除行为:
1、删除外键:
语法:
alter table tableName drop foreign key [外键名称];
注意:
创建完外键后父表是不可以直接删除的,需要先删除外键后才可以。而子建是可以直接删除的。
扩展:物理外键、逻辑外键
物理外建:使用foreign key 定义未见关联另一张表。
缺点:
- 影响增、删、改的效率(需要检查外键的关系)。
- 仅用于单节点数据库,不适用与分布式、集群场景。
- 容易引发数据库的死锁问题
逻辑外键:在业务逻辑中,解决外键关联。
优点:通过逻辑外键,就可以方便的解决上述问题。
所以在写创建数据库时尽量不要使用物理外键
总结
1、一对多
在多的一方添加外键,关联另外一方的主键 。
2、一对一
任意一方,添加外键,关联另一方的主键。
1、多对多
通过中间表的来维护,中间表的两个外键,分别冠梁另外两张表的主键。