MySQL中的外键（foreign key）

时间：2024-11-13 09:47:17
引言

在MySQL中，我们都对主键比较了解，知道主键的主要作用是唯一区分表中的各个行；但是，对于外键（foreign key） 比较陌生。那么什么是外键呢？外键的作用是什么呢？

一、外键、外键作用及其限制条件
1.外键的定义：
  1. 外键是某个表中的一列，它包含在另一个表的主键中。
  2. 外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
  3. 一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。
2.外键的作用：

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。主要体现在以下两个方面：

阻止执行

  1. 从表插入新行，其外键值不是主表的主键值便阻止插入。
  2. 从表修改外键值，新值不是主表的主键值便阻止修改。
  3. 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
  4. 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

  1. 主表删除行，连带从表的相关行一起删除。
  2. 主表修改主键值，连带从表相关行的外键值一起修改。
3.外键创建限制
  1. 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
  2. 必须为父表定义主键。
  3. 外键中列的数目必须和父表的主键中列的数目相同。
  4. 两个表必须是InnoDB表，MyISAM表暂时不支持外键。
  5. 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。
  6. 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
二、外键创建方法

可以在创建表时创建外键，也可以在已有的表中增加外键。我们主要讲第二种方式创建外键。

1.创建外键的语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

其中，ON DELETE 和 ON UPDATE表示事件触发限制，各参数意义如下：

参数 意义
RESTRICT 限制外表中的外键改动（默认值，也是最安全的设置）
CASCADE 跟随外键改动
SET NULL 设为null值
NO ACTION 无动作
SET DEFAULT 设为默认值
2.举例

（1）创建两张表：

CREATE TABLE student
(
	id int (11) primary key auto_increment,
	name char(255),sex char(255),
	age int(11)
)charset utf8;

CREATE TABLE student_score
(
	id int (11) primary key auto_increment,
	class char(255),score char(255),
	student_id int(11)
)charset utf8;

（2）创建外键：

ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY(student_id)
REFERENCES student(id);

（3）查看表结构

SHOW CREATE TABLE student;
SHOW CREATE TABLE student_score;


在这里插入图片描述

三、验证外键作用
1.先向主表中添加数据，再向从表中添加数据（从表中的外键已在主表中存在），正常运行
#向student表中添加数据
INSERT INTO 
student(name,sex,age)
VALUES('小明','男','20');

#向student_score表中添加数据
INSERT INTO student_score
	(class,score,student_id) 
VALUES
	('语文','100',1),
	('数学','99',1),
	('英语','98',1);

数据插入正常，无报错、警告信息。
在这里插入图片描述
在这里插入图片描述

2.在事件触发限制使用默认值RESTRICT的情况下

（1）向从表插入新行，外键值不在主表中，被阻止

INSERT INTO student_score(class,score,student_id) 
					VALUES('语文','100',2);

在这里插入图片描述

（2）从表修改外键值，新值不是主表的主键值，阻止修改

UPDATE student_score SET student_id = 2 WHERE student_id = 1;

在这里插入图片描述

（3）主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)

DELETE FROM student WHERE id = 1;

在这里插入图片描述

（4）主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)

UPDATE student SET id = 2 WHERE id = 1;

在这里插入图片描述

3.更改事件触发限制为CASCADE
#删除旧的外键
ALTER TABLE student_score DROP FOREIGN KEY s_id;

#添加新的外键，修改事件触发限制为CASCADE
ALTER TABLE student_score ADD CONSTRAINT s_id FOREIGN KEY(student_id)
REFERENCES student(id) ON DELETE CASCADE ON UPDATE CASCADE;

（1）查看表结构

SHOW CREATE TABLE student_score;

在这里插入图片描述

（2）查看此时两表中的数据
在这里插入图片描述
在这里插入图片描述

（3）此时，当主表修改主键值，从表中相关行的外键值将一起修改

UPDATE student SET id = 2 WHERE id = 1;

运行成功，查看两张表中的数据：
在这里插入图片描述
在这里插入图片描述

（4）如果主表删除行，从表中的相关行将一起被删除

DELETE FROM student WHERE id = 2;

运行成功，查看两张表中的数据：
在这里插入图片描述
在这里插入图片描述

4.所以，事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。

事件触发限制条件的不同，会造成两张表中的操作限制不同，其他几个限制条件相对好理解，大家可以自己进行尝试，体会其中的区别。

四、删除外键约束
1.语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
2.例子
ALTER TABLE student_score DROP FOREIGN KEY s_id;

相关文章