多表间的关系-一对多-多对多-一对一-外键约束
11. 多表间的关系-一对多-多对多-一对一-外键约束
1. 表关系概述
现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，用户和订单、订单和商品、学生和课程等等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！表和表之间的关系分成三种：
- 一对一 (老公和老婆)
- 一对多 (部门和员工, 用户和订单)
- 多对多 (学生和课程)
例如:
双11当天,马哥和东哥两个用户分别在淘宝上下了一些订单,已知马哥下了1个订单,订单总金额为999元. 东哥下了2个订单,订单金额分别为1314元和10元.
思考: 数据库 该如何存放这些数据呢?
没有建立关系前: 通过表数据不能得知数据间的联系,这样存放数据是没有意义的
image-20200529100830282
建立关系后:
通过对该业务的分析,可得知一个用户可以有多个订单,一个订单只属于一个用户.
我们管1的一方,叫主表或1表. 我们管多个一方,叫从表或多表.
通常要在多的一方添加一个字段,用于存放主表主键的值,我们管这个字段叫外键字段.
外键字段的值必须为主表主键的值,若为其他值,则没有意义.
image-20200529101003797
用于限制外键字段取值必须为主表主键的值的约束叫做-外键约束.
2. 一对多
一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品 一对多建表原则: 在从表(多方)创建一个字段,指向主表(一方)的主键.我们把这个字段称之为外键.
3. 多对多
多对多（m:n） 例如：老师和学生，学生和课程，用户和角色 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
4. 一对一
一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。两种建表原则：
-
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一
UNIQUE - 外键是主键：主表的主键和从表的主键，形成主外键关系
5. 外键约束
5.1 什么是外键约束
一张表中的某个字段引用另一个表的主键 主表：约束别人 副表/从表：使用别人的数据，被别人约束
5.2 创建外键
- 新建表时增加外键：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 关键字解释：CONSTRAINT -- 约束关键字 FOREIGN KEY(外键字段名) –- 某个字段作为外键 REFERENCES -- 主表名(主键字段名) 表示参照主表中的某个字段
- 已有表增加外键：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
具体操作:
以"新建表时添加外键"演示
-- 先创建部门表
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
-- 然后创建员工表,添加外键约束
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加一个外键
-- 外键取名公司要求,一般fk结尾
CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
执行如下：
-- 先创建部门表
mysql> CREATE TABLE department (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> dep_name VARCHAR(20),
-> dep_location VARCHAR(20)
Query OK, 0 rows affected (0.02 sec)
-- 添加2个部门
mysql> INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- 查看部门的数据
mysql> select * from department;
+----+-----------+--------------+
| id | dep_name | dep_location |
+----+-----------+--------------+
| 1 | 研发部 | 广州 |
| 2 | 销售部 | 深圳 |
+----+-----------+--------------+
2 rows in set (0.00 sec)
-- 然后创建员工表,添加外键约束
mysql> CREATE TABLE employee (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> NAME VARCHAR(20),
-> age INT,
-> dep_id INT,
-> -- 添加一个外键
-> -- 外键取名公司要求,一般fk结尾
-> CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
Query OK, 0 rows affected (0.02 sec)
mysql>
正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
执行如下：
-- 插入数据到 employee
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES
-> ('张三', 20, 1),
-> ('李四', 21, 1),
-> ('王五', 20, 1),
-> ('老王', 20, 2),
-> ('大王', 22, 2),
-> ('小王', 18, 2);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 查看 employee 表数据
mysql> select * from employee;
+----+--------+------+--------+
| id | NAME | age | dep_id |
+----+--------+------+--------+
| 1 | 张三 | 20 | 1 |
| 2 | 李四 | 21 | 1 |
| 3 | 王五 | 20 | 1 |
| 4 | 老王 | 20 | 2 |
| 5 | 大王 | 22 | 2 |
| 6 | 小王 | 18 | 2 |
+----+--------+------+--------+
6 rows in set (0.00 sec)
mysql>
部门错误的数据添加失败
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
执行如下：
mysql> select * from department;
+----+-----------+--------------+
| id | dep_name | dep_location |
+----+-----------+--------------+
| 1 | 研发部 | 广州 |
| 2 | 销售部 | 深圳 |
+----+-----------+--------------+
2 rows in set (0.00 sec)
-- 可以发现插入的数据外键 dep_id=5, 而 department 表并没有 dep_id=5 的数据。
mysql> INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
mysql>
5.3 删除外键(了解)
ALTER TABLE 从表 drop foreign key 外键名称;
具体操作:
- 删除employee表的emp_depid_ref_dep_id_fk外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
- 在employee表存在况下添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
执行如下：
-- 查看当前 employee 的表结构
mysql> show create table employee;
+----------+-----------------------+
| Table | Create Table |
+----------+-----------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- 外键
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)
-- 删除外键 emp_depid_ref_dep_id_fk
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看删除外键后的 employee
mysql> show create table employee;
+----------+------------------------+
| Table | Create Table |
+----------+------------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`) -- 外键已经被删除了
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-------------------------+
1 row in set (0.00 sec)
-- 在employee表存在况下添加外键
mysql> ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
Query OK, 6 rows affected (0.34 sec)
Records: 6 Duplicates: 0 Warnings: 0
-- 查看当前employee结构
mysql> show create table employee;
+----------+----------------------+
| Table | Create Table |
+----------+----------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `emp_depid_ref_dep_id_fk` (`dep_id`),
CONSTRAINT `emp_depid_ref_dep_id_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) -- 重新添加的外键
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+----------+-----------------------+
1 row in set (0.00 sec)
mysql>
5.4 数据操作注意事项
添加数据时: