数据库技术：MySQL 多表，外键约束，数据库设计，索引，视图，存储过程，触发器，数据控制，数据备份与恢复

---

MySQL: Multi-Table, Foreign Key and Database Design

Multi-Table Database

In the development environment, a project usually consists of multiple tables. For Example, in a Online Shopping Mall project, it has user table, category table, product table, order table, et cetera. Also, there are some relationships among these tables.

Multi-table solves the redundancy problem of Single table in Database Design.

-- 单表：有冗余问题，同一个字段中出现大量的重复数据
# dep_name 和 dep_location 数据会重复出现
CREATE TABLE emp(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT ,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

-- 删除emp表, 重新创建两张表
DROP TABLE emp;
# 主表，部门表。字段 id 为主键
CREATE TABLE department(
     id INT PRIMARY KEY AUTO_INCREMENT,   
     dep_name VARCHAR(30),  
     dep_location VARCHAR(30)
);
# 从表，员工表。外键 dept_id 与部门表中的主键对应
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT
);

-- 多表设计上的问题：
# 在员工表的 dept_id 里面输入不存在的部门 id，
# 数据依然可以添加，显然这是不合理的

-- 解决方法：
# 使用外键约束，约束 dept_id 为部门表中存在的 id

Foreign Key Constraint

What is Foreign Key?

外键指的是在“从表”中与“主表”的主键对应的那个字段，比如员工表的 dept_id，就是外键。使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性。

主表：主键 id 所在的表，约束别人的表；从表：外键所在的表，被约束的表。

Create Foreign Key Constraint

添加外键约束，就会产生强制性的外键数据检查，从而保证了数据的完整性和一致性。

-- 已有表添加外键约束
# 可以不写 CONSTRAINT emp_dept_fk
ALTER TABLE employee 
ADD CONSTRAINT emp_dept_fk 
	FOREIGN KEY (dept_id) 
	REFERENCES department(id);

-- 重新创建表，添加外键约束
# 先删除 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)
);

Delete Foreign Key Constraint

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

-- 再将外键约束添加回来
# 省略外键约束名称, 系统会自动生成一个约束名称
ALTER TABLE employee 
ADD FOREIGN KEY (dept_id) 
	REFERENCES department (id);

Notes for Using Foreign Key Constraint

从表外键类型必须与主表主键类型一致，否则外键约束创建失败。（Error: Cannot add foreign key constraint）

添加数据时，应该先添加主表中的数据。

# 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','广州');
# 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('张人大',24,1);

删除数据时，应该先删除从表中的数据。（Error: Cannot delete or update a parent row: a foreign key constraint fails）

-- 错误删除：直接删除主表数据
# 不能删除主表的这条数据，因为在从表中有对这条数据的引用
# DELETE FROM department WHERE id = 1;

-- 正确删除
# 先删除从表的所有关联数据
DELETE FROM employee WHERE dept_id = 1;
# 再删除主表的数据
DELETE FROM department WHERE id = 1;

Cascade Deletion

级联删除操作：实现删除主表数据的同时，也删除掉从表数据。

# 重新创建
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) 
    	ON DELETE CASCADE # 添加级联删除
);
# 添加数据
...
# 删除部门编号为 1 的记录，
# 同时，员工表中外键值是 1 的记录也自动删除了
DELETE FROM department WHERE id = 1;

Creating Multiple Tables and Table Relationships

一对多关系

最常见的关系，班级对学生，部门对员工，客户对订单，商品对分类。

实现方式：主表（一方）的主键为从表（多方）的外键。在多的一方建立外键，指向一的一方的主键。

-- 省和市表：一个省包含多个市
# 创建省表。主表，要添加主键约束
CREATE TABLE province(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20)
);
# 创建市表。从表，外键类型要与主表主键一致
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20),
    pid INT,
    CONSTRAINT pro_city_fk 
    	FOREIGN KEY (pid) 
    	REFERENCES province(id)
);

多对多关系

学生对课程，学生对老师，用户对角色。

实现方式：需要借助一张中间表，中间表中至少保存两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

-- 演员与角色表：多演员对多角色
# 创建演员表
CREATE TABLE act