4.关联关系介绍与外键约束

一、关联关系介绍

我们的MYSQL是一个关系型数据库，不仅可以存储数据还可以维护数据与数据的关系，通过外键（cid）实现，建立外键约束

8.1、一对一关联

人–身份证

学生–学籍

用户–用户详情

• 方案一

  主键相同的数据就是关联的数据

• 方案二

  在用户表中添加did字段，与详情表的主键进行关联（要求did字段中添加的值必须在其关联的主键中存在，这个就叫外键约束，并且将外键列添加唯一约束）

8.2、一对多关联

班级–学生

在多的一端添加外键，与少的一端的主键进行对应

8.3、多对一关联

学生–班级

在多的一端添加外键，与少的一端的主键进行对应

8.4、多对多关联

学生–课程

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

二、外键约束

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

# 创建学生表(在学生表中添加外键与班级表的主键进行关联)
# cid字段要和班级表一致，所以类型要和班级表的主键保持一致
create table students(
	stu_num char(8) primary key,
    stu_name varchar(40) not null,
    stu_gender char(4) not null,
    stu_age int not null,
    cid int,
    constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
);

# 这里有第二种方式添加外键约束,这里用add而不是modify，因为是添加约束而不是修改
create table students(
	stu_num char(8) primary key,
    stu_name varchar(40) not null,
    stu_gender char(4) not null,
    stu_age int not null,
    cid int,
);
alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)


# 创建班级表
create table classes(
    class_id int primary key auto_increment,
    class_name varchar(40) not null unique,
    class_remark varchar(200)
);

向班级表添加数据

insert into classes(class_name,class_remark) values('Java2021','...');
insert into classes(class_name,class_remark) values('C++2021','...');
insert into classes(class_name,class_remark) values('Python2021','...');

向学生表添加数据

# 添加学生时，设置给cid外键列的值必须在其关联的主表中存在，否则会报错
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values(20181112,'李四','w',22,3);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values(20181113,'赵武','w',22,2);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values(20181113,'赵武','w',22,2);

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

如果一定要修改的话，先将学生表中的CID设置为null，此时才能修改班级表中的ID

update students set cid=null where cid=2;
update classes set class_id=5 where class_name="C++2021";
# 注意null的赋值需要用is
update students set cid=5 where cid is null;

三、外键约束-级联

我们可以使用级联操作来实现，在添加外键时设置级联修改和级联删除

# 删除原有外键
alter table students drop foreign key FK_STUDENTS_CLASSES;

# 重新添加外键，并设置级联修改和级联删除
alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;

• 级联修改

  在修改了class的外键就会相应的修改students的外键

  # 班级信息
  +----------+------------+--------------+
  | class_id | class_name | class_remark |
  +----------+------------+--------------+
  |        1 | Java2021   | ...          |
  |        3 | Python2021 | ...          |
  |        5 | C++2021    | ...          |
  +----------+------------+--------------+
  
  # 学生信息
  +----------+----------+------------+---------+------+
  | stu_num  | stu_name | stu_gender | stu_age | cid  |
  +----------+----------+------------+---------+------+
  | 20181111 | 张三     | m          |      21 |    1 |
  | 20181112 | 李四     | w          |      22 |    3 |
  | 20181113 | 赵武     | w          |      22 |    5 |
  +----------+----------+------------+---------+------+
  
  #直接修改C++2021的class_id,学生信息中相应的cid也会被修改
  update classes set class_id=2 where class_name='C++2021';
  

• 级联删除

  删除了class的相应记录，students中相应的数据也会消失

  delete from classes where class_id = 1;
   
  #此时学生信息
  +----------+----------+------------+---------+------+
  | stu_num  | stu_name | stu_gender | stu_age | cid  |
  +----------+----------+------------+---------+------+
  | 20181112 | 李四     | w          |      22 |    3 |
  | 20181113 | 赵武     | w          |      22 |    2 |
  +----------+----------+------------+---------+------+