MySQL快速入门06----实体关系 & 外键

最新推荐文章于 2025-06-27 09:07:32 发布

原创最新推荐文章于 2025-06-27 09:07:32 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #MySQL #主键 #外键 #实体关系

MySQL 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了MySQL中的一对一、一对多和多对多的实体关系，并详细讲解了外键的概念及其在数据完整性中的作用。外键的级联操作如cascade（级联更新或删除）、set null（设为空）和restrict（拒绝操作）被逐一阐述。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一实体关系

在数据库中，常用的实体关系有三种：一对一、一对多和多对多。

1.1 一对一的实体关系

如果记录的主键值等于另一个关系内记录的主键值，我问称之为一对一的实体关系。

一对一的实体关系（1:1）使得两个表保存的实体之间数据是对等的。

如：一个学生有基本信息和详细信息。

1.2 一对多的实体关系

一对多的实体关系（1：N），A实体对应多个B实体。

如：一个班级内有很多学生，而且一个学生只属于一个班级。

设计：在多的那端，增加一个字段，用于指向该实体所属的另外的实体的标识。

1.3 多对多的实体关系

多对多的关系（M:N），A实体对应多个B实体，同时一个B实体也对应多个A实体。

例：一个讲师可以给多个班级授课。同时一个班级可以由多个来授课

设计：利用一个中间表，标识实体之间的对应关系，中间表的每个记录，标识一个关系，其实质还是一对多的关系。

二外键

概念：如果一个实体的（student)的某个字段(student:class_id)，指向（引用）另个实体(class)的主键（class:class_id)，就称 Student实体的class_id是外键。

被指向的实体，称之为主实体（主表），也叫父实体（父表）。class
负责指向的实体，称之为从实体（从表），也叫子实体（子表）。Student

作用：

保证数据的完整性。
用于约束处于关系内的实体。
增加子表记录时，是否有与之对应的父表记录。
在删除或者更新主表记录时，从表应该如何处理相关的记录。

2.1 定义一个外键

MySQL中，可以对InnoDB引擎使用外键约束：

语法：

Foreign Key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]

此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。

可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝（与外键绑定的那个字段被约束，其他未绑定的字段可以任意修改）。

如果指定了on update或on delete：在删除或更新时，有如下几个操作可以选择：

cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
restrict，拒绝父表删除和更新。

注：

mysql> set names gbk;
Query OK, 0 rows affected (0.01 sec)

这里的set names gbk指的是通信的字符集是gbk；

创建班级表lin_class:

mysql> create table if not exists lin_class(
    -> class_id int primary key auto_increment,
    -> class_name varchar(20) not null default 'StudySQL' comment '学习MySQL'
    -> )character set utf8;
Query OK, 0 rows affected (0.06 sec)

类型展示：

mysql> desc lin_class;
+------------+-------------+------+-----+----------+----------------+
| Field      | Type        | Null | Key | Default  | Extra          |
+------------+-------------+------+-----+----------+----------------+
| class_id   | int(11)     | NO   | PRI | NULL     | auto_increment |
| class_name | varchar(20) | NO   |     | StudySQL |                |
+------------+-------------+------+-----+----------+----------------+
2 rows in set (0.01 sec)

创建学生表lin_student:

mysql> create table if not exists lin_student(
    -> student_id int primary key auto_increment comment '学生ID',
    -> student_name varchar(20) not null default '无名' comment '学习MySQL',
    -> class_id int,
    -> foreign key (class_id) references lin_class (class_id)
    -> )character set utf8;
Query OK, 0 rows affected (0.07 sec)

类型展示：

mysql> desc lin_student;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| student_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| student_name | varchar(20) | NO   |     | 无名        |                |
| class_id     | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

此时向学生中插入数据：

mysql> insert into lin_student values(null, 'Apple', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`student_1`.`lin_student`, CONSTRAINT `lin_student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `lin_class` (`class_id`))

此时向学生表中插入数据提示出错，因为在学生表关联的教师表中没有对应的班级编号，也就是该外键的值为空，无法插入成功，此时应该先建立班级编号，再向拥有该班级编号的教室中插入数据，如下所示：

插入班级信息：

mysql> insert into lin_class values(1, 'MySQL');
Query OK, 1 row affected (0.02 sec)

mysql> insert into lin_class values(2, 'CPP');
Query OK, 1 row affected (0.03 sec)

mysql> insert into lin_class values(3, 'Java');
Query OK, 1 row affected (0.02 sec)

班级信息列表：

mysql> select * from lin_class;
+----------+------------+
| class_id | class_name |
+----------+------------+
|        1 | MySQL      |
|        2 | CPP        |
|        3 | Java       |
+----------+------------+
3 rows in set (0.00 sec)

插入学生信息：

mysql> insert into lin_student values(null, 'Apple', 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into lin_student values(null, 'Lin', 2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into lin_student values(null, 'Qian', 3);
Query OK, 1 row affected (0.03 sec)

学生信息列表：

mysql> select * from lin_student;
+------------+--------------+----------+
| student_id | student_name | class_id |
+------------+--------------+----------+
|          2 | Apple        |        1 |
|          3 | Lin          |        2 |
|          4 | Qian         |        3 |
+------------+--------------+----------+
3 rows in set (0.00 sec)

2.2 设置级联操作

所谓级联操作就是在主表数据发生改变时，与之关联的从表数据应该如何处理，它包括两个部分，主表更新（on update）和主表删除（on delete）。

允许的级联动作（都是从主表发生改变时对从表的处理）：

cascade：关联操作，如果主表被更新或删除，那么从表也会执行相应的操作;
set null：设置为null，表示从表不指向任何主表记录;
restrict：拒绝主表的相关操作;

2.2.1 set null

删除外键定义：

alter table tbl_name drop foreign key 外键标识;

如删除lin_student中的外键：

mysql> alter table lin_student drop foreign key lin_student_ibfk_1;
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

t添加外键定义：

alter table tbl_name add foreign key 外键定义

向lin_student中添加新的外键：

mysql> alter table lin_student add foreign key (class_id) references lin_class (class_id) on delete set null;
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

上述外键表明当主键数据内容删除的时候，lin_student所对应的班级会自动设置为空（null）。

例如，删除班级表中班级为1的编号：

mysql> delete from lin_class where class_id=1;
Query OK, 1 row affected (0.05 sec)

那么学生表中对应班级为1的编号会设置为null：

mysql> select * from lin_student;
+------------+--------------+----------+
| student_id | student_name | class_id |
+------------+--------------+----------+
|          2 | Apple        |     NULL |
|          3 | Lin          |        2 |
|          4 | Qian         |        3 |
+------------+--------------+----------+
3 rows in set (0.00 sec)

2.2.2 cascade

级联操作，执行对应的操作，删除后子数据也删除，更新后子数据也更新。

删除学生表中原有的外键：

mysql> alter table lin_student drop foreign key lin_student_ibfk_1;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

向班级表中插入新的外键：

mysql> alter table lin_student add foreign key (class_id) references lin_class (class_id) on delete cascade;
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

当教室表中某一班级删除的时候，学生表中对应该班级的学生信息也将被删除：

mysql> delete from lin_class where class_id=2;
Query OK, 1 row affected (0.03 sec)

学生信息：

mysql> select * from lin_student;
+------------+--------------+----------+
| student_id | student_name | class_id |
+------------+--------------+----------+
|          2 | Apple        |     NULL |
|          4 | Qian         |        3 |
+------------+--------------+----------+
2 rows in set (0.00 sec)

2.2.3 restrict

禁止主表发生变化，删除或者更新！

alter table lin_student add foreign key (class_id) references lin_class (class_id) on delete cascade on update restrict;

当删除主表中的内容时，从表中对应的内容将会被删除，当更新主表中的内容时会被禁止！！！