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

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

白话简介

user 表：id 为主键

profile 表： uid 为主键

简单来说，若表 profile 的 uid 列 作为表外键(外建名称：user_profile)，以表 user 做为主表，以其 id列 做为参照(references)，且联动删除/更新操作(on delete/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/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`;

大家可以自行百度一下，这里就不啰嗦了，截稿！