关联关系介绍

MySQL 是一个关系型数据库, 不仅可以存储数据, 还可以维护数据与数据之间的关系——通过在数据表中添加字段建立外键约束

数据与数据之间的关联关系分为四种:

  • 一对一关联
  • 一对多关联
  • 多对一关联
  • 多对多关联

一对一关联

人 —- 身份证 一个人只有一个身份证、一个身份证只对应一个人
学生 —- 学籍 一个学生只有一个学籍、一个学籍也对应唯一的一个学生
用户 —- 用户详情 一个用户只有一个详情、一个详情也只对应一个用户

方案 1: 主键关联 —— 两张数据表中主键相同的数据为相互对应的数据

方案 2: 唯一外键 —— 在任意一张表中添加一个字段添加外键约束与另一张表主键关联, 并且将外键列添加唯一约束

一对多与多对一

班级 —- 学生 (一对多) 一个班级包含多个学生
学生 —- 班级 (多对一) 多个学生可以属于同一个班级
图书 —- 分类 商品 —— 商品类别

方案: 在多的一端添加外键, 与一的一端主键进行关联

多对多关联

学生 —- 课程 一个学生可以选择多门课、一门课程也可以由多个学生选择
会员 —- 社团 一个会员可以参加多个社团、一个社团也可以招纳多个会员

方法: 额外创建一张关系表来维护多对多关联 —— 在关系表中定义两个外键, 分别与两个数据表的主键进行关联

外键约束

外键约束 —— 将一个列添加外键约束与另一张表的主键 (唯一列) 进行关联之后, 这个外键约束的列添加的数据必须要在关联的主键字段中存在

示例: 学生表和班级表

  1. 先创建班级表
  1. create table classes(
  2.     class_id int primary key auto_increment,
  3.     class_name varchar(40) not null unique,
  4.     class_remark varchar(200)
  5. );
  1. 创建学生表 (在学生表中添加外键与班级表的主键进行关联)
  1. # 【⽅式一】在创建表的时候, 定义 cid 字段, 并添加外键约束
  2. # 由于 cid 列要与 classes 表的 class_id 进⾏关联, 因此 cid 字段类型和⻓度要与 class_id 一致
  3. create table students(
  4.     stu_num char(8) primary key,
  5.     stu_name varchar(20) not null,
  6.     stu_gender char(2) not null,
  7.     stu_age int not null,
  8.     cid int,
  9.     constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
  10. );
  12. #【⽅式⼆】先创建表, 再添加外键约束
  13. create table students(
  14.     stu_num char(8) primary key,
  15.     stu_name varchar(20) not null,
  16.     stu_gender char(2) not null,
  17.     stu_age int not null,
  18.     cid int
  19. );
  20. # 在创建表之后, 为 cid 添加外键约束
  21. alter table students 
  22. add constraint FK_STUDENTS_CLASSES 
  23. foreign key (cid) references classes (class_id);
  25. # 删除外键约束
  26. alter table students drop foreign key FK_STUDENTS_CLASSES;
  1. 向班级表添加班级信息
  1. insert into classes(class_name, class_remark)values('java2104', '...');
  3. insert into classes(class_name, class_remark)values('Java2105', '...');
  5. insert into classes(class_name, class_remark)values('Java2106', '...');
  7. insert into classes(class_name, class_remark)values('Python2106', '...');
  9. select * from classes;
  11. +----------+------------+--------------+
  12. | class_id | class_name | class_remark |
  13. +----------+------------+--------------+
  14. |        1 | java2104   | ...          |
  15. |        2 | java2105   | ...          |
  16. |        3 | java2106   | ...          |
  17. |        4 | Python2105 | ...          |
  18. +----------+------------+--------------+
  1. 向学生表中添加学生信息
  1. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  2. values('20210102', '李斯', '⼥', 20, 4);
  4. # 添加学生时, 设置给 cid 外键列的值必须在其关联的主表 classes 的 classes_id 列存在
  5. insert into students(stu_num, stu_name, stu_gender, stu_age, cid)
  6. values('20210103', '王五', '男', 20, 6);

关键约束 - 级联

当学生表中存在学生信息关联班级表的某条记录时，就不能对班级表的这条记录进⾏修改 ID和删除操作，如下:

  1. select * from classes;
  2. +----------+------------+--------------+
  3. | class_id | class_name | class_remark |
  4. +----------+------------+--------------+
  5. |        1 | java2104   | ...          |
  6. |        2 | java2105   | ...          |
  7. |        3 | java2106   | ...          |
  8. |        4 | Python2105 | ...          |
  9. +----------+------------+--------------+
  11. select * from students;
  12. +----------+----------+------------+---------+------+
  13. | stu_num  | stu_name | stu_gender | stu_age | cid  |
  14. +----------+----------+------------+---------+------+
  15. | 20210101 | 张三     | 男         |      20 |    2 |   
  16. | 20210102 | 李斯     | ⼥         |      20 |    4 |
  17. | 20210103 | 王五     | 男         |      18 |    1 |
  18. | 20210104 | 赵六     | 女         |      18 |    2 |
  19. +----------+----------+------------+---------+------+
  21. # 班级表中 class_id = 1 的班级信息被学生表中的记录关联了, 则不能修改 java2104 的 class_id, 并且不能删除
  22. update classes set class_id = 5 where class_name = 'java2104';
  23. ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_test2`.`students`, CONSTRAINT `FK_STUDENTS_CLASS` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))
  25. delete from classes where class_id = 1;
  26. ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_test2`.`students`, CONSTRAINT `FK_STUDENTS_CLASS` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))

如果一定要修改 java2104 的班级 ID, 该如何实现?

  1. 将引用 java2104 班级 id 的学生记录中的 cid 修改为 NULL
  1. update students set cid = NULL where cid = 1;
  2. select * from students;
  3. +----------+----------+------------+---------+------+
  4. | stu_num  | stu_name | stu_gender | stu_age | cid  |
  5. +----------+----------+------------+---------+------+
  6. | 20210101 | 张三     | 男         |      20 |    2 |
  7. | 20210102 | 李斯     | ⼥         |      20 |    4 |
  8. | 20210103 | 王五     | 男         |      18 | NULL |
  9. | 20210104 | 赵六     | 女         |      18 |    2 |
  10. +----------+----------+------------+---------+------+
  1. 再修改班级信息表中 java2104 记录的 class_id
  1. update classes set class_id = 5 where class_name = 'java2104';
  2. select * from classes;
  3. +----------+------------+--------------+
  4. | class_id | class_name | class_remark |
  5. +----------+------------+--------------+
  6. |        2 | java2105   | ...          |
  7. |        3 | java2106   | ...          |
  8. |        4 | Python2105 | ...          |
  9. |        5 | java2104   | ...          |
  10. +----------+------------+--------------+
  1. 将学生表中 cid 设置为 NULL 的记录的 cid 重新修改为 java2104 这个班级的新的 id
  1. update students set cid = 5 where cid IS NULL;
  2. select * from students;
  3. +----------+----------+------------+---------+------+
  4. | stu_num  | stu_name | stu_gender | stu_age | cid  |
  5. +----------+----------+------------+---------+------+
  6. | 20210101 | 张三     | 男         |      20 |    2 |
  7. | 20210102 | 李斯     | ⼥         |      20 |    4 |
  8. | 20210103 | 王五     | 男         |      18 |    5 |
  9. | 20210104 | 赵六     | 女         |      18 |    2 |
  10. +----------+----------+------------+---------+------+

也可使用级联操作来实现:

  1. 在添加外键时, 设置级联修改和级联删除
  1. # 删除原有的外键
  2. alter table students drop foreign key FK_STUDENTS_CLASSES;
  4. # 重新添加外键, 并设置级联修改和级联删除
  5. alter table students add constraint FK_STUDENTS_CLASSES
  6. foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;
  1. 测试级联修改
  1. # 修改 java2104 的 class_id, 关联 java2104 这个班级的学⽣记录的 cid 也会同步修改
  2. update classes set class_id = 1 where class_name = 'java2104';
  4. # 班级信息
  5. select * from classes;
  6. +----------+------------+--------------+
  7. | class_id | class_name | class_remark |
  8. +----------+------------+--------------+
  9. |        2 | java2105   | ...          |
  10. |        3 | java2106   | ...          |
  11. |        4 | Python2105 | ...          |
  12. |        1 | java2104   | ...          |
  13. +----------+------------+--------------+
  15. # 学生信息
  16. select * from students;
  17. +----------+----------+------------+---------+------+
  18. | stu_num  | stu_name | stu_gender | stu_age | cid  |
  19. +----------+----------+------------+---------+------+
  20. | 20210101 | 张三     | 男         |      20 |    2 |
  21. | 20210102 | 李斯     | ⼥         |      20 |    4 |
  22. | 20210103 | 王五     | 男         |      18 |    1 |
  23. | 20210104 | 赵六     | 女         |      18 |    2 |
  24. +----------+----------+------------+---------+------+
  1. 测试级联删除
  1. # 删除 class_id = 1 的班级信息, 学⽣表引⽤此班级信息的记录也会被同步删除
  2. delete from classes where class_id=1;
  4. # 班级信息
  5. select * from classes;
  6. +----------+------------+--------------+
  7. | class_id | class_name | class_remark |
  8. +----------+------------+--------------+
  9. |        2 | java2105   | ...          |
  10. |        3 | java2106   | ...          |
  11. |        4 | Python2105 | ...          |
  12. +----------+------------+--------------+
  14. # 学生信息
  15. select * from students;
  16. +----------+----------+------------+---------+------+
  17. | stu_num  | stu_name | stu_gender | stu_age | cid  |
  18. +----------+----------+------------+---------+------+
  19. | 20210101 | 张三     | 男         |      20 |    2 |
  20. | 20210102 | 李斯     | ⼥         |      20 |    4 |
  21. | 20210104 | 赵六     | 女         |      18 |    2 |
  22. +----------+----------+------------+---------+------+