mysql 外键（foreign key）的详解和实例

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

mysql  外键  Foreign  key  on  delete  cascade

外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。

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

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

?

1 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.afk ，则 B.bfk插入的值必须是 A.afk 中已存在的

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

                                                                                                                                                                            

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

                                                                                                                                                                            

先把表和索引加好

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

//学生表 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不做任何操作，如下

?

1	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不做任何操作，如下

?

1	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的记录删掉

?

1 2 3 4 5 6

//删除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

?

1 2 3 4 5 6

//更新联动 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

添加外键

?

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

删除外键

?

1	alter  table  B  drop  foreign  key  `bfk`;