MySQL 数据表的关联关系

前言

关联关系介绍

在MySQL中，可以通过不同方式建立表之间的关联关系。以下是几种常见的关联关系类型：

• 一对一关联（One-to-One）：这种关联关系表示两个表中的每个记录在另一个表中只有一个匹配记录。示例：将两个表通过相同的主键进行连接。
• 一对多关联（One-to-Many）：这种关联关系表示一个表中的记录可以对应另一个表中的多个匹配记录。示例：在多的一方表中保存与一的一方表的主键关联。
• 多对多关联（Many-to-Many）：这种关联关系表示两个表之间存在多对多的关系，即一个记录在另一个表中可以有多个匹配记录，反之亦然。示例：通过引入第三个关联表来连接两个表，并存储相关联的外键。

通过外键约束，可以实现以下功能：

• 参照完整性（Referential Integrity）：外键约束可以确保在引用表（被引用表）中存在的值不会在另一个表（引用表）中出现无法匹配的情况。换句话说，外键只允许包含引用表已经存在的值，防止出现孤儿记录或失效引用。
• 数据完整性（Data Integrity）：外键约束可以防止在引用表中删除或更新主键时产生不一致的数据。当试图删除或修改引用表中的主键时，外键约束会检查是否有其他表中的数据依赖于该主键，从而保证数据的完整性。

为了建立这些关联关系，可以使用外键（FOREIGN KEY）在表之间创建引用。外键是指一个表中的字段，它与另一个表中的主键相对应，用于确保数据完整性和维护关联关系。通过定义外键约束，可以实现关联表之间的数据一致性和参照完整性。

外键约束

外键约束是一种在关系数据库中用于建立表与表之间关联关系的约束。它定义了一个表的字段（外键），该字段与另一个表的主键或唯一键相对应。

示例：学生表与班级表

创建班级表

create table classes(
    class_id int primary key auto_increment, -- 自增主键
    class_name varchar(20) not null unique,
    calss_remark varchar(200)
);

创建学生表，并添加外键

create table students(
    stu_num char(8) primary key,
    stu_name varchar(10) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    cid int,  -- 若是一对一关系，则添加unique
    constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
    -- constraint 约束名称 foreign key(具体字段) references 关联的表的具体字段
);

或者在创建表后修改表结构

alter table students 
add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id);

删除外键

alter table students drop foreign key FK_STUDENTS_CLASSES;

添加数据

-- 添加班级信息
insert into classes(class_name,calss_remark) values('2022软件1','...');
insert into classes(class_name,calss_remark) values('2022软件2','...');
insert into classes(class_name,calss_remark) values('2022软件3','...');
insert into classes(class_name,calss_remark) values('2022软件4','...');

-- 添加学生信息
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220011','张三','男',17,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220012','李四','男',18,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220013','王五','女',17,2);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220014','赵六','男',20,3);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220015','小文','女',17,3);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220016','小莉','女',19,3);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('20220017','小博','男',18,3);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('20220018','阿亚','男',19);

外键约束-级联

SQL中的级联（Cascade）是指在对一个表进行修改（删除或更新）时，同时对与之相关联的其他表的数据进行相应的操作。具体来说，级联操作可以包括级联删除、级联更新和级联设置为空。

• 级联删除（ON DELETE CASCADE）：当对主表进行删除操作时，自动删除与之关联的从表中的相关数据，以保持数据的一致性。
• 级联更新（ON UPDATE CASCADE）：当对主表进行更新操作时，自动更新与之关联的从表中的相关数据，以保持关联关系的正确性。
• 级联设置为空（ON DELETE SET NULL）：当对主表进行删除或更新操作时，将与之关联的从表中的外键值设置为空（NULL），适用于允许外键字段为空的情况。

可以在创建表时或添加外键时设置级联操作，

示例：

添加外键时，设置级联修改和级联删除

alter table students 
add constraint FK_STUDENTS_CLASSES 
foreign key(cid) references classes(class_id) on update cascade on delete cascade;  

以上示例中on update cascade指定了级联更新操作，on delete cascade指定了级联删除操作。

需要注意的是，使用级联操作时需要谨慎，确保不会破坏数据库的完整性和一致性。正确理解数据之间的关系，并根据实际需求选择合适的级联操作，以避免意外结果的发生。