多表间的关系-一对多-多对多-一对一-外键约束

原创

海洋的渔夫 2022-07-01 18:57:22 博主文章分类：Mysql系列 ©著作权

文章标签 mysql 数据库 sql sqlserver innodb 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者海洋的渔夫的原创作品，请联系作者获取转载授权，否则将追究法律责任

11. 多表间的关系-一对多-多对多-一对一-外键约束

1. 表关系概述

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，用户和订单、订单和商品、学生和课程等等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！表和表之间的关系分成三种：

一对一 (老公和老婆)
一对多 (部门和员工, 用户和订单)
多对多 (学生和课程)

例如:

双11当天,马哥和东哥两个用户分别在淘宝上下了一些订单,已知马哥下了1个订单,订单总金额为999元. 东哥下了2个订单,订单金额分别为1314元和10元.

思考: 数据库该如何存放这些数据呢?

没有建立关系前: 通过表数据不能得知数据间的联系,这样存放数据是没有意义的

多表间的关系-一对多-多对多-一对一-外键约束_sqlserver

image-20200529100830282

建立关系后:

通过对该业务的分析,可得知一个用户可以有多个订单,一个订单只属于一个用户.

我们管1的一方,叫主表或1表. 我们管多个一方,叫从表或多表.

通常要在多的一方添加一个字段,用于存放主表主键的值,我们管这个字段叫外键字段.

外键字段的值必须为主表主键的值,若为其他值,则没有意义.

多表间的关系-一对多-多对多-一对一-外键约束_sqlserver_02

image-20200529101003797

用于限制外键字段取值必须为主表主键的值的约束叫做-外键约束.

2. 一对多

一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品一对多建表原则: 在从表(多方)创建一个字段,指向主表(一方)的主键.我们把这个字段称之为外键. 多表间的关系-一对多-多对多-一对一-外键约束_sql_03

3. 多对多

多对多（m:n）例如：老师和学生，学生和课程，用户和角色多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。多表间的关系-一对多-多对多-一对一-外键约束_mysql_04

4. 一对一

一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表。两种建表原则：

外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE
外键是主键：主表的主键和从表的主键，形成主外键关系

5. 外键约束

5.1 什么是外键约束

一张表中的某个字段引用另一个表的主键主表：约束别人副表/从表：使用别人的数据，被别人约束多表间的关系-一对多-多对多-一对一-外键约束_mysql_07

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 数据操作注意事项

添加数据时:
 先添加主表中的数据,再添加从表中的数据
删除数据时:
 先删从表中的数据,再删主表中的数据
修改数据时:
 如果主表中的主键被从表引用了,不能修改此主键的值

上一篇：数据库范式

下一篇：数据库-多表查询-连接查询

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯