MySQL中的外键（foreign key）

引言

在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;