MySQL中的外键（foreign key）

阅读目录

前言

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

一、外键作用及其限制条件

1 外键的定义

外键是某个表中的一列，它包含在另一个表的主键中。

外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。

一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

2 外键的作用

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

阻止执行

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

级联执行

• 主表删除行，连带从表的相关行一起删除。
• 主表修改主键值，连带从表相关行的外键值一起修改。

3 外键创建限制

父表必须已经存在于数据库中，或者是当前正在创建的表。

如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

外键中列的数目必须和父表的主键中列的数目相同。

两个表必须是

InnoDB

表，

MyISAM

表暂时不支持外键。

外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如

int

和

tinyint

可以，而

int

和

char

则不可以；

二、外键创建方法

可以在创建表时创建外键，也可以在已有的表中增加外键。

我们主要讲第二种方式创建外键。

1 创建外键的语法

ALTERTABLE 表名 ADDCONSTRAINT 外键名 FOREIGNKEY(外键字段名)REFERENCES 外表表名(主键字段名)[ONDELETE {RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}][ONUPDATE {RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]

其中，

ON DELETE

和

ON UPDATE

表示事件触发限制，各参数意义如下：
参数意义RESTRICT限制外表中的外键改动（默认值，也是最安全的设置）CASCADE跟随外键改动SET NULL设为null值NO ACTION无动作SET DEFAULT设为默认值

2 举例

（1）创建两张表

CREATETABLE student
(
    id int(11)primarykeyauto_increment,
    name char(255),sex char(255),
    age int(11))charset utf8;CREATETABLE student_score
(
    id int(11)primarykeyauto_increment,
    class char(255),score char(255),
    student_id int(11))charset utf8;

（2）创建外键

ALTERTABLE student_score 
ADDCONSTRAINT s_id FOREIGNKEY(student_id)REFERENCES student (id);

（3）查看表结构

SHOWCREATETABLE student;SHOWCREATETABLE student_score;

CREATETABLE`student`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`char(255)DEFAULTNULL,`sex`char(255)DEFAULTNULL,`age`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8;

CREATETABLE`student_score`(`id`int(11)NOTNULLAUTO_INCREMENT,`class`char(255)DEFAULTNULL,`score`char(255)DEFAULTNULL,`student_id`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`s_id`(`student_id`),CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8;

三、验证外键作用

1 先向主表中添加数据

再向从表中添加数据（从表中的外键已在主表中存在），正常运行。

#向 student 表中添加数据INSERTINTO student (NAME, sex, age)VALUES('小明','男','20');#向 student_score 表中添加数据INSERTINTO student_score (class, score, student_id)VALUES('语文','100',1),('数学','99',1),('英语','98',1);

数据插入正常，无报错、警告信息。

2 触发限制使用默认值 RESTRICT 的情况下

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

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

ERROR 1452(23000) : Cannot ADDORUPDATE a child ROW : a FOREIGNKEYCONSTRAINT fails (`tts`.`student_score`,CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`))

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

UPDATE student_score
SET student_id =2WHERE
    student_id =1;

ERROR 1452(23000) : Cannot ADDORUPDATE a child ROW : a FOREIGNKEYCONSTRAINT fails (`tts`.`student_score`,CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`))

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

DELETEFROM student WHERE id =1;

ERROR 1451(23000) : Cannot DELETEORUPDATE a parent ROW : a FOREIGNKEYCONSTRAINT fails (`tts`.`student_score`,CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`))

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

UPDATE student SET id =2WHERE id =1;

ERROR 1451(23000) : Cannot DELETEORUPDATE a parent ROW : a FOREIGNKEYCONSTRAINT fails (`tts`.`student_score`,CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`))

3 更改事件触发限制为 CASCADE

#删除旧的外键ALTERTABLE student_score DROPFOREIGNKEY s_id;#添加新的外键，修改事件触发限制为 CASCADEALTERTABLE student_score 
ADDCONSTRAINT s_id FOREIGNKEY(student_id)REFERENCES student (id)ONDELETECASCADEONUPDATECASCADE;

（1）查看表结构

CREATETABLE`student_score`(`id`int(11)NOTNULLAUTO_INCREMENT,`class`char(255)DEFAULTNULL,`score`char(255)DEFAULTNULL,`student_id`int(11)DEFAULTNULL,PRIMARYKEY(`id`),KEY`s_id`(`student_id`),CONSTRAINT`s_id`FOREIGNKEY(`student_id`)REFERENCES`student`(`id`)ONDELETECASCADEONUPDATECASCADE)ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;

（2）查看此时两表中的数据

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

UPDATE student SET id =2WHERE id =1;

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

DELETEFROM student WHERE id =2;

4 结论

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

四、删除外键约束

ALTERTABLE 表名 DROPFOREIGNKEY 外键名;ALTERTABLE student_score DROPFOREIGNKEY s_id;