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

一.多表设计概述：

---

二.一对多：

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 name
        unique (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 username
        unique (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.添加外键约束的图形化工具：

---

六.声明：

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

---

原文地址：https://blog.csdn.net/ADCvbV/article/details/144359682

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！