关闭

史上最简单的 MySQL 教程(二十九)「外键(下)」

标签: MySQL数据库外键主键约束
1394人阅读 评论(4) 收藏 举报
分类:

温馨提示:本系列博文已经同步到 GitHub,地址为「mysql-tutorial」,欢迎感兴趣的童鞋StarFork,纠错。

外键

外键foreign key,外面的键,即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键,那么将该字段称之为外键。每张表中,可以有多个外键。

外键作用

首先,给出父表和子表的定义:

  • 父表,指外键所指向的表;
  • 子表,指相对于父表,拥有外键的表。

外键默认的作用有两个,分别对子表和父表进行约束。

第 1 种:约束子表

在子表进行数据的写操作(增和改)的时候,如果对应的外键字段在父表找不到对应的匹配,那么操作就会失败。

执行如下 SQL 语句,进行测试:

-- 插入数据,外键字段在父表不存在
insert into my_foreign2 values(null,'Charies','6'); 

-- 插入数据,外键字段在父表存在
insert into my_foreign2 values(null,'Charies','1'); 

foreign4

如上图所示,在我们向子表my_foreign2插入外键字段为6的时候,提示插入失败,原因就是在父表class中,没有ID6的记录。而在我们向子表my_foreign2插入外键字段为1的时候,提示成功,原因就是在父表class中,有ID1的记录。

第 2 种:约束父表

在父表进行数据的写操作(删和改,且涉及主键)的时候,如果对应的主键字段在子表已经被数据引用,那么操作就会失败。

执行如下 SQL 语句,进行测试:

-- 更新父表记录
update class set id = 5 where id = 1;
update class set id = 5 where id = 3;

foreign5

如上图所示,在我们修改父表classID1的时候,提示修改失败,原因就是在子表my_foreign2中已经引用了该值的主键字段。而在我们修改父表classID3的时候,提示修改成功,原因就是在子表my_foreign2中并没有引用该值的主键字段。

外键条件

在我们使用外键的时候,应该遵循如下条件:

  • 外键要存在,首先必须保证表的引擎是 InnoDB(默认的存储引擎),如果不是 InnoDB 存储引擎,那么外键可以创建成功,但没有约束作用;
  • 外键字段的字段类型(列类型),必须与父表的主键类型完全一致;
  • 每张表中的外键名称不能重复;
  • 增加外键的字段,如果数据已经存在,那么要保证数据与父表中的主键对应。

下面以最后一个条件为例,执行如下 SQL 语句,进行测试:

-- 新增数据
insert into my_foreign1 valuse(1,'Gavin',3);

-- 增加外键
alter table my_foreign1 add foreign key(c_id) references class(id);

foreign6

如上图所示,在新增外键的时候,如果子表中(想要新增外键的字段)的数据已经存在,而父表中又没有与子表中(想要新增外键的字段)的数据相匹配的主键的话,那么操作就会失败;反之,则会成功。

执行如下 SQL 语句,进行测试:

-- 新增数据
insert into class valuse(3,'PM3.4','A115');

-- 增加外键
alter table my_foreign1 add foreign key(c_id) references class(id);

foreign7

如上图所示,显然当父表中存在与子表中(想要新增外键的字段)的数据相匹配的主键的话,增加主键的操作就会成功。

外键约束

所谓外键约束,就是指外键的作用。之前所讲的外键的作用都是默认的作用,实际上,可以通过对外键的需求,进行定制操作。

外键约束有三种模式,分别为:

  • district:严格模式(默认),父表不能删除或更新一个已经被子表数据引用的记录;
  • cascade:级联模式,父表的操作,对应子表关联的数据也跟着被删除;
  • set null:置空模式,父表的操作之后,子表对应的数据(外键字段)被置空。

在此需要注意:以上三种模式,都是对父表的约束

  • 基本语法foreign key(外键字段) + references + 父表(主键字段) + [on delete + 模式 + on update + 模式];

通常一个合理的做法(约束模式)是:删除的时候, 子表被置空;更新的时候,子表进行级联操作。

执行如下 SQL 语句,进行测试:

-- 创建外键,指定模式:删除置空,更新级联
create table my_foreign3(
    id int primary key auto_increment,
    name varchar(20) not null,
    c_id int,
    -- 增加外键
    foreign key(c_id)
    -- 引用父表
    references class(id)
    -- 指定删除模式
    on delete set null
    -- 指定更新模式
    on update cascade
)charset utf8;

foreign8

如上图所示,在我们指定外键的约束模式之后,通过查看表的创建语句,可以看到具体的约束语句。

接下来,执行如下 SQL 语句,继续进行测试:

-- 插入数据
insert into my_foreign3 values(null,'Jobs',1),
(null,'Bill',1),
(null,'Mark',1),
(null,'Swift',2),
(null,'Sellen',1);

foreign9

如上图所示,我们向表my_foreign3中插入了 5 条记录。接下来,我们就可以测试外键的级联模式和置空模式啦!呃,对啦,前提是我们需要把与父表class相关联的除my_foreign3之外的其他子表,也就是my_foreign1my_foreign2的外键删除掉,否则的话,由于这两个子表的外键使用了严格模式,会干扰我们接下来的测试。

在我们删除表my_foreign1my_foreign2的外键之后,执行如下 SQL 语句,测试级联模式:

-- 更新父表主键
update class set id = 8 where id = 1;

foreign10

执行如下 SQL 语句,测试置空模式:

-- 删除父表主键
delete from class where id = 2;

foreign11

通过以上测试,我们已经验证了级联模式和置空模式的效果。其实,在我们进行删除置空操作的时候,有一个前提,那就是:子表的外键字段必须允许为空,否则的话,操作是无法成功的

至此,我们已经把外键的相关操作都演示了一遍。在这里,我们会发现外键的功能非常强大,能够进行各种的约束,也正是由于外键这种约束的强大,其降低了开发语言对数据的可控性,因此在实际的开发中,很少使用外键来处理数据。


温馨提示:符号[]括起来的内容,表示可选项;符号+,则表示连接的意思。


———— ☆☆☆ —— 返回 -> 史上最简单的 MySQL 教程 <- 目录 —— ☆☆☆ ————

1
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

史上最简单的 MySQL 教程(二十八)「外键(上)」

外键外键:foreign key,外面的键,即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键,那么将该字段称之为外键。每张表中,可以有多个外键。新增外键外键既可以在创建表的时候增...
  • qq_35246620
  • qq_35246620
  • 2017-08-19 23:02
  • 1798

mysql 设置外键约束(foreign key)

建立外键约束可以采用列级约束语法和表级约束语法,如果仅仅对单独的一个数据列建立外键约束,则使用列级约束语法即可;如果需要对多个列组合创建外键约束,或者需要为外键约束指定名字则必须使用表级约束语法。 ...
  • cherry_na
  • cherry_na
  • 2015-02-06 13:43
  • 15579

MYSQL外键的使用以及优缺点

主键和索引是不可少的,不仅可以优化数据检索速度,开发人员还省不其它的工作, 矛盾焦点:数据库设计是否需要外键。这里有两个问题:一个是如何保证数据库数据的完整性和一致性;二是第一条对性能的影响。 ...
  • u010695055
  • u010695055
  • 2016-11-15 11:18
  • 1377

mysql外键设置及作用

数据库 mysql 建立外键的前提:本表的列必须与外键类型相同(外键 必须是外表主键)。 外键作用: 使两张表形成关联,外键只能引用外表中的列的值! 指定主键关键字: foreign k...
  • u011057506
  • u011057506
  • 2013-07-30 12:33
  • 2822

浅谈MySQL连接查询与外键

连接查询是同时查询多张表,通过多张表之间的关系得到最终的结果。连接查询又分成内连接、外链接和自然连接。内连接:从左表中取出每一条记录,去右表中与所有的记录进行匹配;匹配必须是某个条件在左表中与右表中相...
  • James_shu
  • James_shu
  • 2017-02-05 14:59
  • 1584

MySQL外键约束中几个值的含义

快要下班了,趁着活已经差不多干完了,自己倒腾了一下MySQL中外键约束的各个值到底是什么含义。之前也基本清楚,这次就算是亲手实践下,顺便加深理解。 我的做法是在数据库建了一张a表和一张b表。a表做为...
  • u012884726
  • u012884726
  • 2016-09-22 17:55
  • 1062

MySQL 修改被外键约束的列

前言为啥我需要修改已经被外键约束的表? 有的时候,建表时考虑不够仔细,导致有的时候突然需要为一张表进行一些更改,如:增加一列属性;将主键设为自增属性等。 数据库处理这种东西时,不让你进行修改,因为...
  • jcq521045349
  • jcq521045349
  • 2017-01-10 14:43
  • 2353

05、MySQL外键、联合查询和子查询

MySQL外键、联合查询和子查询
  • qq_33349750
  • qq_33349750
  • 2017-07-18 20:38
  • 429

MySQL中主键与外键的区别和联系

主键与外键的关系,通俗点儿讲,我现在有一个论坛,有两张表,一张是主贴 thread,一张是回帖 reply 先说说主键,主键是表里面唯一识别记录的字段,一般是帖子id,体现在访问的时候,例如是 th...
  • junoohoome
  • junoohoome
  • 2017-03-31 10:04
  • 1835

MySQL命令行&MySql外键设置详解

MySQL命令行&MySql外键设置详解   MySQL 命令行  第一招、mysql服务的启动和停止 net stop mysql net start mysql ...
  • qq1121674367
  • qq1121674367
  • 2014-06-07 18:10
  • 1588
    个人资料
    • 访问:833929次
    • 积分:12009
    • 等级:
    • 排名:第1442名
    • 原创:258篇
    • 转载:85篇
    • 译文:11篇
    • 评论:900条
    博主的 GitHub 账号
    GitHub : Charies Gavin

        鉴于 CSDN 糟糕的用户体验,博主会将一些优质的文章迁移到 Charies Gavin's Blog  欢迎大家在 GitHub 上 Follow 博主,以及 Fork、Star、Watch 博主的项目。


      青春不老 奋斗不止


      好学若饥虚心若愚
    博客专栏