外键具有保持数据完整性和一致性的机制,对业务处理有着很好的校验作用。
白话简介
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`;
大家可以自行百度一下,这里就不啰嗦了,截稿!