当前位置：首页 > article >正文

【MySQL入门指南】外键约束使用详解

article 2024/9/27 7:24:53

一、为什么需要外键？

MySQL是一种关系型数据库，现实中的业务往往是相互关联的，这也就决定了数据库中的表也是存在相互关系的。而表与表之间的相互关系就是通过外键来维护的。给大家举一个现实的例子来帮助理解：

现在我们需要在数据库中存放学生的选课信息。如果我们将课程具体信息全部设计在学生选课表后面，那就会出现大量的数据冗余：

所以我们不妨在学生选课表中只存放课程的id，课程的具体信息再由id从课程信息表中检索即可，也就是说，我们要建立 stu.course_id 与 course.id 的映射关系。

至此，我们看似实现了“外键”，通过id建立了两张表的关系，但我们没有建立外键约束。怎么理解呢？

管理员向stu表中插入数据时，由于误操作可能插入一个不存在的 course_id
C++课程突然从course数据表中被删除了，但stu表中还有选这门课的学生

上面的两种情况都会导致错误关系的建立。也就是说，我们虽然看似实现了语义上的外键，但是两张表在本质上还是独立的。因此需要由MySQL来维护一种“强制的”，可靠的外键约束。

二、如何设置外键？

foreign key (字段名) references 主表(列)

外键用于定义主表和从表之间的关系。外键约束主要定义在从表上，主表则必须是有主键约束或唯一键约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。在上面的案例中，stu表就属于从表，course表属于主表。

创建主表

mysql> create table course(
    -> id int primary key,
    -> name varchar(20) not null,
    -> time varchar(64) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

创建从表

mysql> create table stu(
    -> id int primary key,
    -> name varchar(10) not null,
    -> course_id int,
    -> foreign key(course_id) references course(id) -- 设置外键
    -> );
Query OK, 0 rows affected (0.05 sec)

正常插入数据

mysql> insert into course values(11, 'C++', '9:00am');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu values(100, '张三', 11);
Query OK, 1 row affected (0.00 sec)

插入的课程不存在。在外键约束下插入失败

mysql> insert into stu values(101, '李四', 12);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`))

允许插入的值为NULL，表示还没有选课

mysql> insert into stu values(101, '李四', NULL);
Query OK, 1 row affected (0.00 sec)

在外键约束下删除课程失败，因为还有学生选这门课程

mysql> delete from course where id=11;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`))