mysql 外键 详解与使用

一、基本概念

1. MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

2. 外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。

3. 如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

4. 外键的使用条件

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

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

③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

二、使用方法

1、创建外键的语法：

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值）

⑤ NO ACTION（无动作，默认的）

2、示例

1）创建表1

create table repo_table(

repo_id char(13) not null primary key,

repo_name char(14) not null)

type=innodb;

创建表2

mysql> create table busi_table(

-> busi_id char(13) not null primary key,

-> busi_name char(13) not null,

-> repo_id char(13) not null,

-> foreign key(repo_id) references repo_table(repo_id))

-> type=innodb;

2）插入数据

insert into repo_table values("12","sz"); //success

insert into repo_table values("13","cd"); //success

insert into busi_table values("1003","cd", "13"); //success

insert into busi_table values("1002","sz", "12"); //success

insert into busi_table values("1001","gx", "11"); //failed,提示：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`))

3）增加级联操作

mysql> alter table busi_table

-> add constraint id_check

-> foreign key(repo_id)

-> references repo_table(repo_id)

-> on delete cascade

-> on update cascade;

-----

ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb;

3、相关操作

外键约束（表2）对父表（表1）的含义:

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

关键字

含义

CASCADE

删除包含与已删除键值有参照关系的所有记录

SET NULL

修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)

RESTRICT

拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)

NO ACTION

啥也不做

4、其他

在外键上建立索引:

index repo_id (repo_id),

foreign key(repo_id) references repo_table(repo_id))

摘要: 外键具有保持数据完整性和一致性的机制，目前MySQL只在InnoDB引擎下支持，下面实例下一个小操作来说明下外键的关联操作，用来保持数据的完整性和一致性。
外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。

白话文简介=====

user 表：id 为主键

profile 表： uid 为主键

简单来说，若表 profile 的 uid 列 作为外键（外建名称：user_profile），参考的主表的列（references）为 user 表 的 id，且联动删除更新操作（on delete cascade on update cascade），则 user 表中删除 id 为 1 的记录，会联动删除 profile 表中 uid 为 1 的记录。user 表中更新 id 为 1 的记录至 id 为 2，则profile 表中 uid 为 1 的记录也会被联动更新至 uid 为 2，这样遍保持了数据的完整性和一致性。

B 存在外键 b_f_k，以 A 表的 a_k 作为参照列，则 A 为主表，B 为从表，A 中某记录更新或删除时将会联动 B 中外键与其关联对应的记录做更新或删除操作。

alter table `profile` add constraint `user_profile` foreign key (`uid`) references `user`(`id`) on delete cascade on update cascade;

在 profile 中为 uid 列添加名为 user_profile 的外键，且此外键的参照为 user 表的 id 列，关联的操作为删除和更新

=正文========

1、表引擎必须为InnoDB，MyISAM不支持

2、外键必须建立索引（可以为普通、主键、唯一，事先不建立的话会自动创建一个普通索引），你要用的外键和参照的外表的键，即

alter table B add constraint `b_foreign_key_name` foreign key (`bfk`) 
references A(`afk`) on delete no action on update no action;

时 b_foreign_key_name 为外键名，bfk字段和afk字段都必须存在索引

3、外表为约束表，约束着含有外键的被约束表，即 B 含有一个以 A 作为参考表的外键，则 A 为主 B 为从，若关联on delete on update等动作，则 A 变更 B 会被变更，B 怎样变 A 不必跟随变动，且表 A 中必须事先存在 B 要插入的数据外键列的值，列如 B.bfk作为外键 参照 A.ak ，则 B.bfk插入的值必须是 A.ak 中已存在的

4、把3说的简单点就是若B有以A作为参照的外键，则B中的此字段的取值只能是A中存在的值，从表B会实时受到主表A的约束，同时若关联on delete on update等操作则当A中的被参照的字段发生delete或update时，B中的对应的记录也会发生delete 或 update操作，完整性。

下面我们以一个简单的学生信息管理系统的数据表做为实例

先把表和索引加好

//学生表 cid作为外键关联班级表 pid作为 档案表外键的关联 所以这俩货都得有索引
create table my_student(

	`id` int unsigned not null auto_increment primary key,
	`name` varchar(25) not null comment 'student name',
	`pid` int unsigned not null comment 'student profile id',
	`cid` int unsigned not null comment 'student class id',
	key `cid`(`cid`)，
	key `pid`(`pid`)
)engine=InnoDB default charset=utf8 auto_increment=1;

//班级表 id作为 学生表外键的关联 已为主键索引

create table my_class(
	`id` int unsigned not null auto_increment primary key,
	`cname` varchar(25) not null comment 'class name',
	`info` tinytext not null default ''
)engine=InnoDB default charset=utf8 auto_increment=1;

//档案表 id作为外键 关联 学生表 已为主键索引

create table my_profile(
	`id` int unsigned not null auto_increment primary key,
	`pname` varchar(25) not null comment 'profile name',
	`info` tinytext not null default '' comment 'student info'，
)engine=InnoDB default charset=utf8 auto_increment=1;

这里我们将my_student作为my_profile的外表，即约束表，即my_profile以自身id作为 外键 关联 以 my_student 的pid字段作为参照，关联delete联动操作，update不做任何操作，如下

alter table my_profile add constraint profile_student foreign key (`id`) references my_student(`pid`) on delete cascade on update no action;

这里我们将my_class作为my_student的外表，即约束表，即my_student以自身cid作为 外键 关联 以 my_class 的id字段作为参照，关联update联动操作，delete不做任何操作，如下

alter table my_student add constraint student_class foreign key (`cid`) references my_class(`id`) on update cascade on delete no action;

则当我删除my_student中 id=1 的学生时，其会将my_profile中id为此学生pid的记录删掉

//删除id为1的学生记录，因档案表以学生表作为外表约束，且关联 on delete cascade操作

delete from my_student where id = 1;

这是外键机制自身执行的处理动作

delete from my_profile where id = (select pid from my_student where id = 1);

这是外键机制自身执行的处理动作
则当我更新my_class中 id=1 的班级为5时，其会将my_student中cid=1的学生更新为cid=5

//更新联动

update my_class set id = 5 where id = 1;

这是外键机制自身执行的处理动作

update my_student set cid = 5 where cid = 1;

这是外键机制自身执行的处理动作
贴出代码：

my_profile:

id做为外键，参照my_student以其pid作为关联，关联删除联动，更新无动作，则档案表受学生表的删除约束，当学生表中id为xx的记录被删除时，档案表中id为此记录pid的记录也会呗删除掉。

my_student:

学生表

pid作为档案表的外键关联所以要建立key pid 索引

以cid作为外键 参照 班级表的id 关联更新操作 删除无关联（用意为当班级的id发生变动时，学生表中每个学生的cid也会关联更新，这样即使班级表中的班级id发生变化，学生所属班级仍然保持着完整且一致）

my_class:

班级表，id作为学生表的外键参照，为主键索引

实验开始：

1、删除学生表中的某个学生，则将其作为外表参照且关联删除联动操作的档案表中的记录也会被删除掉，关联关系为

my_profile.id = my_student.pid的记录

很容易看懂吧，删除id为22的学生时，他的pid为2，则档案表里id为2的记录也被关联删除了

2、修改班级id，学生表cid外键的更新联动 关联 班级表中的id，即当我变更班级id时，学生表中的cid也会被更新

很容易看懂吧，四年级的id由4更新为5时，以其作为参照表的学生表中属于四年级的小红的cid也由4更新为5。

on delete on update的联动操作有四种

no action

cascade

set null

restrict

添加外键

alter table B add constraint `bfk` foreign key ('fk_column_name') references A('column_name') on delete no action on update no action;

删除外键

alter table B drop foreign key `bfk`;