【Mysql】多表、外键约束

多表

1.1 多表简述
实际开发中，一个项目通常需要很多张表才能完成。
例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表…
1.2 单表的缺点
1.2.1 数据准备

创建一个数据库 db3

CREATE DATABASE db3 CHARACTER SET utf8;

数据库中创建一个员工表 emp ,
包含如下列 eid, ename, age, dep_name, dep_location
eid 为主键并自动增长, 添加 5 条数据

-- 创建emp表 主键自增
CREATE TABLE emp(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT ,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

-- 添加数据 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('张百万', 20, '研发部', '广州'); 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('赵四', 21, '研发部', '广州'); 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('广坤', 20, '研发部', '广州'); 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('小斌', 20, '销售部', '深圳'); 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('艳秋', 22, '销售部', '深圳'); 
 INSERT INTO emp (ename, age, dep_name, dep_location) VALUES ('大玲子', 18, '销售部', '深圳');

1.2.2 单表的问题
•冗余, 同一个字段中出现大量的重复数据
在这里插入图片描述

1.3 解决方案
1.3.1设计为两张表
1)多表方式设计
•department 部门表 : id, dep_name, dep_location
•employee 员工表: eid, ename, age, dep_id
2) 删除emp表, 重新创建两张表

-- 创建部门表
-- 一方,主表
CREATE TABLE department(
     id INT PRIMARY KEY AUTO_INCREMENT,   
     dep_name VARCHAR(30),  
     dep_location VARCHAR(30)
);

-- 创建员工表
-- 多方 ,从表
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT
);

3)添加部门表数据

-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); 

SELECT * FROM department;

添加员工表数据

-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

SELECT * FROM employee;

1.3.2 表关系分析
部门表与员工表的关系
1.员工表中有一个字段dept_id 与部门表中的主键对应,员工表的这个字段就叫做外键。
2.拥有外键的员工表被称为从表, 与外键对应的主键所在的表叫做主表。
在这里插入图片描述

1.3.3 多表设计上的问题
当我们在员工表的 dept_id 里面输入不存在的部门id ,数据依然可以添加显然这是
不合理的。

-- 插入一条 不存在部门的数据
INSERT INTO employee (ename,age,dept_id) VALUES('无名',35,3);

在这里插入图片描述

实际上我们应该保证,员工表所添加的 dept_id , 必须在部门表中存在.
解决方案:
•使用外键约束,约束 dept_id ,必须是部门表中存在的id
1.4 外键约束
1.4.1 什么是外键
•外键指的是在从表中与主表的主键对应的那个字段,比如员工表的 dept_id,就是外键。
•使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性。

在这里插入图片描述

多表关系中的主表和从表
•主表: 主键id所在的表, 约束别人的表。
•从表: 外键所在的表多, 被约束的表。
在这里插入图片描述

1.4.2 创建外键约束
语法格式:
1)新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

2)已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

重新创建employee表, 添加外键约束

-- 先删除 employee表
DROP TABLE employee;

-- 重新创建 employee表,添加外键约束
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    -- 添加外键约束
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

插入数据

-- 正常添加数据 (从表外键 对应主表主键)
INSERT INTO employee (ename, age, dept_id) VALUES ('张百万', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('赵四', 21, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('广坤', 20, 1); 
INSERT INTO employee (ename, age, dept_id) VALUES ('小斌', 20, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('艳秋', 22, 2); 
INSERT INTO employee (ename, age, dept_id) VALUES ('大玲子', 18, 2); 

-- 插入一条有问题的数据 (部门id不存在)
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (ename, age, dept_id) VALUES ('错误', 18, 3);

4)添加外键约束,就会产生强制性的外键数据检查, 从而保证了数据的完整性和一致性
在这里插入图片描述

1.4.3 删除外键约束
语法格式

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

删除外键约束

-- 删除employee 表中的外键约束,外键约束名 emp_dept_fk
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

再将外键添加回来, 语法格式

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);

SQL示例

-- 可以省略外键名称, 系统会自动生成一个
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

1.4.4外键约束的注意事项
1)从表外键类型必须与主表主键类型一致否则创建失败.
在这里插入图片描述

添加数据时, 应该先添加主表中的数据.

-- 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');

-- 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);

删除数据时,应该先删除从表中的数据.

-- 删除数据时 应该先删除从表中的数据
-- 报错 Cannot delete or update a parent row: a foreign key constraint fails
-- 报错原因 不能删除主表的这条数据,因为在从表中有对这条数据的引用
DELETE FROM department WHERE id = 3;
-- 先删除从表的关联数据
DELETE FROM employee WHERE dept_id = 3;

-- 再删除主表的数据
DELETE FROM department WHERE id = 3;