【MySQL】多表操作 —— 外键约束

多表关系

MySQL 多表之间的关系可以概括为：

• 一对一
• 一对多/多对一
• 多对多

一对一关系

例子：一个学生只有一张身份证，一张身份证只能对应一学生

在任一表中添加唯一外键，指向另一方主键，确保一对一关系

一般一对一关系较少见，遇到一对一关系的表最好是合并表

一对多/多对一关系

例子：一个部门有多个员工，一个员工只能对应一个部门

实现原则：在多的一方建立外键，指向一的一方的主键

多对多关系

例子：一个学生可以选择很多门课，一门课也可以被很多学生选择

原则：多对多关系实现需要借助第三张中间表，中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来两张表的主键

外键约束

基本概念

MySQL 外键约束（foreign key）是表的一个特殊字段，经常与主键一起使用，对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜这 4 种水果，那么来到水果摊买水果就只能选择苹果、桃子、李子、西瓜，其他水果无法购买

定义一个外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表
• 必须为主表定义主键
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的
• 在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同

一对多/多对一

创建外键约束

方式 1：创建表时设置

在 create table 语句中，通过 foreign key 关键字来指定外键

格式如下：

[constraint 外键名] foreign key (外键字段名[,外键字段名2,...]) references 主表名(主键字段名[,主键字段名2,...])

代码示例：

create table if not exists dept(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名称
);
create table if not exists emp(eid varchar(20) primary key , -- 员工编号ename varchar(20) , -- 员工名字age int , -- 员工年龄dept_id varchar(20) , -- 员工所属部门constraint emp_fk foreign key (dept_id) references dept(deptno)
);

结果如下：

方式 2：创建表后设置

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据

格式如下：

alter table 从表名 add constraint 外键名 foreign key (外键字段名) references 主表名(主键字段名)

代码示例：

create table if not exists dept(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名称
);
create table if not exists emp(eid varchar(20) primary key , -- 员工编号ename varchar(20) , -- 员工名字age int , -- 员工年龄dept_id varchar(20) -- 员工所属部门
);
alter table emp add constraint dept_fk foreign key (dept_id) references dept(deptno);

结果如下：

外键约束下的数据操作

数据插入

代码示例：

insert into dept values ('1001','研发部'),('1002','销售部');
insert into emp values ('1','张三',20,'1001'),('2','李四',20,'1001'),('3','王五',21,'1002'),('4','赵六',22,'1002'),('5','孙七',24,'1003');

结果如下：

由于 emp 表的外键列插入了 dept 表主键列中没有的数据，导致报错

注意事项：

• 必须先给主表添加数据
• 从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列

数据删除

代码示例：

delete from dept where deptno = '1001';

结果如下：

由于 dept 表中主键列中的 1001 被 emp 表中的外键列依赖，所以删除会报错

注意事项：

• 删除表要先删除从表才能删除主表

• 主表的数据被从表依赖时，不能删除，没有被依赖的数据则可以删除

• 从表的数据可以随便删除

删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除，外键一旦删除，就会解除主表和从表间的关联关系

格式如下：

alter table 从表名 drop foreign key 外键约束名;

代码示例：

alter table emp drop foreign key dept_fk;

结果如下：

多对多

在多对多关系中，A 表的一行对应 B 表的多行，B 表的一行对应 A 表的多行，需要新增加一个中间表，来建立多对多关系

student 表和 course 表都是主表，student_course 表是从表，也就是说，在多对多关系中，可以有多个主表

创建外键约束

从表对每个主表都设置一个外键约束

代码示例：

create table if not exists student(sid int primary key auto_increment, -- 学生学号name varchar(20), -- 学生姓名age int -- 学生年龄
);
create table if not exists course(cid int primary key auto_increment, -- 课程编号cname varchar(20) -- 课程名
);
create table if not exists student_course(sid int ,cid int,score double -- 分数
);
alter table student_course add foreign key (sid) references student(sid);
alter table student_course add foreign key (cid) references course(cid);

结果如下：

外键约束下的数据操作

多对多中的外键约束其实跟一对多/多对一中的外键约束没什么区别，这里就直接给出正确代码

数据插入

代码示例：

insert into student values (1,'张三',20),(2,'李四',20);
insert into course values (001,'语文'),(002,'数学'),(003,'英语');
insert into student_course values (1,001,99),(2,001,97),(1,002,100),(2,002,90),(1,003,79),(2,003,99);

结果如下：

注意事项：

• 必须先给主表添加数据
• 从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列

数据删除

代码示例：

delete from student_course;

结果如下：

注意事项：

• 删除表要先删除从表才能删除主表

• 主表的数据被从表依赖时，不能删除，没有被依赖的数据则可以删除

• 从表的数据可以随便删除

删除外键约束

代码示例：

alter table student_course drop foreign key student_course_ibfk_1;
alter table student_course drop foreign key student_course_ibfk_2;

结果如下：