多表设计-一对多一对多-外键

一.多表设计概述：

---

二.一对多：

1.需求：

根据 页面原型 及 需求文档，完成部门及员工模块的表结构设计

-->部门和员工就是一对多，因为一个部门下会有多个员工，但一个员工只归属一个部门

2.页面原型：

3.需求文档：

4.代码实现：

a.创建员工表：

-- 员工
create table tb_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 '入职时间',create_time datetime         not null comment '创建时间',update_time datetime         not null comment '修改时间'
) comment '员工表';

b.创建部门表：

-- 部门
create table tb_dept
(id          int unsigned primary key auto_increment comment 'ID',name        varchar(10) not null unique comment '部门名称',create_time datetime    not null comment '创建时间',update_time datetime    not null comment '修改时间'
) comment '部门表';

c.完善员工表：员工对应一个部门，因此员工表中还需要一个部门的属性(每个员工归属一个部门)

员工表中部门命名为dept_id，且部门表中用id记录部门，部门表中id是什么属性，员工表中dept_id就是什么属性

-- 员工
create table tb_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 '修改时间'
) comment '员工表';

d.对比：

其中部门表是1的一方，员工表是多的一方（一个部门下有多个员工，一个员工只归属一个部门）

1的一方也是父表，多的一方也是子表（一个爹可以有多个儿子，但一个儿子只能有一个爹）

---

三.一对多关系实现：

在数据库表中多的一方添加字段(外键)，来关联一的一方的主键。

---

四.一对多-外键：

1.准备工作：插入数据

a.部门表：

-- auto-generated definition
create table tb_dept
(id          int unsigned auto_increment comment 'ID'primary key,name        varchar(10) not null comment '部门名称',create_time datetime    not null comment '创建时间',update_time datetime    not null comment '修改时间',constraint nameunique (name)
)comment '部门表';
​
-- 插入测试数据
insert into tb_dept (id, name, create_time, update_time)
VALUES (1, '学工部', now(), now()),(2, '教研部', now(), now()),(3, '咨询部', now(), now()),(4, '就业部', now(), now()),(5, '人事部', now(), now());

​

b.员工表：

-- auto-generated definition
create table tb_emp
(id          int unsigned auto_increment comment 'ID'primary key,username    varchar(20)                  not null comment '用户名',password    varchar(32) default '123456' null comment '密码',name        varchar(10)                  not null comment '姓名',gender      tinyint unsigned             not null comment '性别, 说明: 1 男, 2 女',image       varchar(300)                 null comment '图像',job         tinyint unsigned             null comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',entrydate   date                         null comment '入职时间',dept_id     int unsigned                 null comment '归属的部门ID',create_time datetime                     not null comment '创建时间',update_time datetime                     not null comment '修改时间',constraint usernameunique (username)
)comment '员工表';
​
​
-- 插入测试数据
insert into tb_emp -- insert into也可以写成INSERT INTO
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 1, '2007-02-01', 1, now(), now()),(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 2, '2012-11-01', 2, now(), now()),(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),(17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

c.运行结果：

2.删除部门表中的一行数据：

此时部门表里1号部门已经被删除，但员工表中仍然有员工在1号部门里，此时就出现问题了

->部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整，不一致的问题

->原因就是目前上述的两张表即员工表和部门表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

->为了使员工表和部门表建立联系，可使用外键约束。外键约束可保证数据的一致性和完整性。

本例中dept_id这个字段就是一个外键字段

->此时需要为员工表tb_emp添加一个外键约束，外键字段就是dept_id，所关联的主表就是部门表tb_dept的id字段

->给员工表tb_emp添加一个外键约束:

说明：外键(Name)名称根据需求自定义即可；目标表(Target table)即当前添加的外键约束所关联的主表；右边的列(Columns)需要指定当前表的某个字段关联到目标表的某个字段；

注：关联的两个字段类型必须一致；

结果：dept_id有蓝色钥匙修饰，代表已经外键约束

此时再删除id为1的部门就会报错：因为员工表中有dept_id为1的

此时删除id为5的部门就不会报错：因为员工表中没有dept_id为5的

---

五.外键约束：外键关键字foreign key

1.添加外键约束有两种方式：

• 创建表时指定

• 创建完表后，再添加外键

2.添加外键约束的图形化工具：

---

六.声明：

物理外键通常不用，大多用逻辑外键，即使用代码来保证数据的一致性和完整性。

---