mysql 外键(foreign key)的详解和实例

Wesley13
• 阅读 693

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

白话简介

user 表:id 为主键

profile 表: uid 为主键

简单来说,若表 profileuid 列 作为表外键(外建名称:user_profile),以表 user 做为主表,以其 id列 做为参照(references),且联动删除/更新操作(on delete/update cascade)。则 user 表 中删除 id 为 1 的记录,会联动删除 profileuid 为 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、表引擎必须为InnoDBMyISAM不支持

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中的被参照的字段发生deleteupdate时,B 中的对应的记录也会发生deleteupdate操作,完整性。

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

先把表和索引加好

//学生表 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_studentpid字段作为参照,关联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_classid字段作为参照,关联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_studentid = 1的学生时,其会将my_profileid为此学生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_classid = 1 的班级为 5 时,其会将my_studentcid = 1的学生更新为cid = 5

//更新联动
update my_class set id = 5 where id = 1;

这是外键机制自身执行的处理动作
update my_student set cid = 5 where cid = 1;
这是外键机制自身执行的处理动作

贴出代码:

my_profile:

mysql 外键(foreign key)的详解和实例

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

my_student:

mysql 外键(foreign key)的详解和实例

学生表

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

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

my_class:

mysql 外键(foreign key)的详解和实例

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

实验开始:

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

my_profile.id = my_student.pid的记录

mysql 外键(foreign key)的详解和实例

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

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

mysql 外键(foreign key)的详解和实例

很容易看懂吧,四年级的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`;

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

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL 我自己常用的语句汇总
1,更新,根据一个表更新另一个表,比如批量同步外键  方法一:  update更新表set字段(select参考数据from参考表where 参考表.id 更新表.id);  updatetable\_2m setm.column(selectcolumnfromtable\_1mpwherem
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
mysql中主外键关系
一、外键:1、什么是外键2、外键语法 3、外键的条件4、添加外键5、删除外键1、什么是外键:主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性外键:是另一表的主键,外键可以有重复的,可以是空值,用来和其他表建立联系用的。所以说,如果谈到了外
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究