多表的理解及应用

1.多表的概述

实际开发中, 一个项目通常需要很多张表才能完成。

例如, 一个商城项目的数据库, 需要很多张表, 用户表, 分类表, 商品表, 订单表...

2.单表的缺点

冗余, 同一个字段可能会出现大量重复的数据。

3.解决方案

设计多张表通过外键将两张或多张表给联系在一起，外键一般为另一张表的主键。

例如：

1. 多表方式设计 employee 员工表: eid ename age

   department 部门表: depid, dep_name, dep_location

2. 创建员工和部门表

-- 创建员工表
create table employee(
	eid int primary key auto_increment,
	ename varchar(10),
	age int,
    dep_id int
);
-- 创建部门表
create table department(
	dep_id int primary key auto_increment,
	dep_name varchar(10),
	dep_location varchar(10)
);

3.插入数据

-- 添加2个部门
insert into department values(default, '研发部','广州'),(default, '销售部', '深圳');
select * from department;
-- 添加5个人员工
insert into employee values(default, '贾宝玉', 20, 1), 
						   (default, '林黛玉', 21, 1),
					       (default, '薛宝钗', 20, 1),
						   (default, '元春', 20, 2),
						   (default, '探春', 22, 2),
						   (default, '甄士隐', 18, 2);
select * from employee;

1.表关系分析

 

1. 员工表中有一个字段dep_id与部门表中的主键对应字段，这个字段就叫做关系属性

2. 拥有关系属性的员工表被称为 从表, 与关系属性对应的主键所在的表叫做 主表 

2.多表设计上的问题

当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。

例如：

-- 插入一条 不存在部门的数据 
insert into employee values(default, '张亿万', 20, 100), 

应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在。

解决方案：

        使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在。

3.外键约束

1. 外键

   外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。

2. 外键约束

 

 使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

 

1.创建外键约束

语法格式:

 新建表时添加外键约束

constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段);

为已创建好的表添加外键约束

alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名);

需求: 为employee表的 dep_id字段添加外键约束。

        为已经创建好的employee表添加外键约束

alter table employee add constraint emp_dep_fk foregin key(dep_id) references department(dep_id);

        新创建employee表时, 为dep_id添加外键约束

create table employee(
    eid int primary key auto_increment,
    ename varchar(10),
    age int,
    dep_id int,
    -- 添加外键约束
    constraint emp_dep_fk forign key(dep_id) references department(dep_id)
);

2.删除外键约束

语法格式：

alter table 从表名 drop foreign key 外键约束的名称;

4.外键约束的注意事项

1. 从表的外键类型必须和主表的主键类型保持一致

2. 添加从表数据时

   从表中添加的外键值, 必须在主表的主键中存在

3. 删除和变更数据主表数据时

   先删除从表中的数据或将外键设置为null, 再删除主表中的数据

-- 删除部门表中主键为1的部门信息
delete from department where dep_id = 1;
-- 报错信息如下
-- Cannot delete or update a parent row: a foreign key constraint fails

        4.通过navicat设置外键约的束变更和删除的级联操作

使用说明

选项	作用
RESTRL(默认)	如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
No action	同RESTRICT，都是立刻检查外键约束
CASCADE	在主表update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL	在主表上update/delete记录时，将子表上匹配记录的列设为null 注意：子表的外键列不能为not null