【MySQL从小白到高手】第四篇:外键约束详解和表的设计思路

前言

MySQL的约束~

FOREIGN KEY~

简述~:

foreign key : 外键约束 (针对两张表,进行了关联~~)

语法格式~:

foreign key (字段名) references 主表(列) 

案例背景~:

假设有两张表~

每个学生都得属于一个具体的班级,这个班级得存在!!!如果学生表这里出现了一个记录,班级classid是10000,此时这样的数据就是非法的~~这种情况下就可以使用外键约束来描述这种关系.学生表依赖了班级表.就把学生表称为"子表",班级表称为"父表".

操作案例~:

创建外键约束一个基本写法~

外键约束写在创建表的末尾,描述的是两张表的两个列之间的"依赖关系".子表依赖于父表(子表引用自父表),要求自表中对应的记录得在父表中存在~

下面插入几条记录来感受一下外键约束的效果

看一下准备好的班级表:

再看一下我们创建好的学生表:

接下来我们尝试进行一个插入操作:

这条记录中指定的class_id为1,在class表中存在,就可以插入成功.

在插入成功之前,mysql会先拿着这个class_id去class表里去查一下,看看是否存在,存在才能插入成功~

那我们再尝试插入一条记录:

此时我们的这个插入操作就失败了

报错信息提示无法添加或者更新子表

这条记录中,指定的class_id为100,在class表中不存在,不能插入成功~

所以我们要想插入成功势必就得保证我们插入的class_id得在表中存在

那么我们重新插入一下

这样我们的插入都成功了,因为这里面的2,3也是在班级表的class_id中存在的~

我们再看一种情况:

我们先看一下我们插入好的几条记录

我们能不能一下修改class_id的值 ?

尝试一下

报错了

报错信息和上述一样不能添加或者更新子表

所以说不仅仅是新增的时候要考虑到外键约束,新增成功的数据如果进行修改,也一样是会存在问题

但是如果我们要是改成一个班级表里面存在的,这样就是完全可以的

上面都是针对子表在操作~

外键约束同样也在约束父表~~

操作案例~:

假设我们要删除班级表class_id为2的这条记录

同样的这样也是会出现错误的~

外键约束,同样也在约束着父表,当父表中的某个记录被子表中依赖着的时候,此时尝试进行删除或者修改,都会失败~

如何才能删除成功呢

我们可以这样做:

现在为什么就可以删除成功了呢

针对class表里面class_id为2的记录来说,并没有在student表中被依赖到,就可以删除~

同时如果我们不删记录尝试把整个表都删掉也是不可以的.

也是因为受到外键约束的影响~

我们再说一下外键约束的工作原理:

在子表中插入新的记录的时候,会先根据对应的值,在父表中先查询,查询到之后,才能够执行后续的插入

这里的查询操作,可能是一个成本较高的操作(比较耗时)

外键约束其实要求,父表总被依赖的这一列,必须要有索引,有了索引就能大大的提高查询速度~

表现形式class表的class_id这一列,得是primary key或者unique(有了这俩约束的列,就会自动创建出索引了)

后续详细讲

表的设计

所谓的"数据库设计" "表的设计" 其实就是根据实际的问题场景,把表给创建出来了~~

但凡是和"设计"相关的话题,都比较抽象.一般来说都是需要大家有一定的经验积累~~

给你一个问题场景,如何设计数据库,如何设计表?

一个典型的通用的办法:先找出这个场景中涉及到的"实体",然后再来分析"实体之间的关系".实体就可以理解为Java中的对象,就可以视为是需求中的一些关键性的名词~~

一个典型的场景:

实现一个学生管理系统.

1.表示学生的基本信息.

2.表示班级的基本信息.

3.表示学生学习的课程的基本信息.

这样的一个场景中所涉及到的关键名词就是学生,班级,课程.这就是我们所说的实体

对于咱们找到的实体来说,就需要创建对应的表来表示相关的信息~~(每个实体表,里面的基本信息,结合需求都是容易确定的)

很多时候,实体和实体之间,并不是孤立的,而是存在对应关系这样的对应关系,也需要体现在表中(实体之间的关系,这个是隐含的,是需要进一步分析才能想清楚的,这里实体之间的不同的关系,会对表的设计产生直接影响)

实体之间的关系:

1.一对一的关系

2.一对多的关系

3.多对多的关系

4.没关系~

分析实体之间的关系,就是"小学生造句"

我们造几个句子来感受一下:

我们拿学生管理系统为例:

1.一对一的关系

学生管理系统中有一个

studenrt表(学生的id,学生姓名,学生班级.......)

user表(用户的账户,密码......)

这里面就有两个实体,学生是一个实体,用户也是一个实体,这就是一对一的关系

造的句子:

一个用户对应到一个学生,一个学生也只有一个账户

在数据库中如何表示这种一对一的关联关系?

方法一:可以把这两个实体用一张表来表示~~就叫做userstudent表,这一张表里面既包含用户名密码也包含学生的信息

方法二:可以用两张表来表示,其中一张表包含了另一张表的id例如:user表里面加上一个student_id,学生表里面也可以加一个user_id.根据这个对应关系,就可以随时找到某个账户对应的学生是谁,也可以找到某个学生对应的账户是啥

2.一对多关系

student表(学号,姓名........)

class表(班级编号,班级名称.......)

造句:一个学生应该处于一个班级中,一个班级可以包含多个学生~

这就叫做一对多关系,一个学生只能在一个班级,一个班级可以有多个学生

在数据库中表示一对多的关系,也有两种典型的方案:

方法一:在班级表中,新增一列,表示这个班级的学生id都有啥~~

形如:

student 表(学号,姓名.....)

class 表(班级编号,班级名称,学生列表)

1 java100 1, 2, 3, 4, 5 这5个学生都是在java100班

2 java101 6, 7, 8, 9, 10

通过这种方式就可以表示一个班级都对应到哪些同学~~

方法二:班级表不变,在学生表中新增一列,classid

class 表(班级编号,班级名称)

1 java100

2 java101

student 表(学号,学生姓名,所在班级)

1 张三 1

2 李四 1

根据这种方式,同样也能知道一个班级对应到哪些同学~

对于MySQL来说;表示一对多的时候,只能采用方案二,不能采用方案一

因为MySQL中没有提供类似于"素组"这样的类型~

但是像Redis这样的数据库,就有数组类型

就可以考虑使用方法一的方式来表示~~

3.多对多的关系

也是固定套路~

学生表(学号,姓名)

课程表(课程编号,课程名称)

学生和课程就是多对多关系~

造句:一个学生就可以选多门课程,一个课程可以包含多个学生~

M个学生,可以选N门课~

多对对关系,在数据库设计中,就一招,使用一个关联表,来表示两个实体之间的关系~~

学生表(学号,姓名)

1 张三

2 李四

3 王五

课程表(编号,名称)

1 语文

2 数学

3 英语

创建一个关联表

学生-课程 表(学号,课程编号)

1 1

学号为1的同学选了课程编号为1的课程

课程编号为1的课程,包含了一个学号为1的同学

张三选了语文课

语文课上有张三