1.多表的概述
实际开发中, 一个项目通常需要很多张表才能完成。
例如, 一个商城项目的数据库, 需要很多张表, 用户表, 分类表, 商品表, 订单表...
2.单表的缺点
冗余, 同一个字段可能会出现大量重复的数据。
3.解决方案
设计多张表通过外键将两张或多张表给联系在一起,外键一般为另一张表的主键。
例如:
-
多表方式设计 employee 员工表: eid ename age
department 部门表: depid, dep_name, dep_location
-
创建员工和部门表
-- 创建员工表
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.表关系分析
-
员工表中有一个字段dep_id与部门表中的主键对应字段,这个字段就叫做关系属性
-
拥有关系属性的员工表被称为 从表, 与关系属性对应的主键所在的表叫做 主表
2.多表设计上的问题
当我们在员工表的dep_id里面输入了不存在的部门编号, 数据依然可以添加, 显然这是不合理的。
例如:
-- 插入一条 不存在部门的数据
insert into employee values(default, '张亿万', 20, 100),
应该保证员工表所添加的dep_id, 必须在部门表dep_id中存在。
解决方案:
使用外键约束, 约束员工表中的dep_id必须在部门表dep_id中存在。
3.外键约束
-
外键
外键指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。
-
外键约束
使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。
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.外键约束的注意事项
-
从表的外键类型必须和主表的主键类型保持一致
-
添加从表数据时
从表中添加的外键值, 必须在主表的主键中存在
-
删除和变更数据主表数据时
先删除从表中的数据或将外键设置为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 |