MySQL的约束以及表关系设计

一. 数据库约束

约束是 关系型数据库 的一个重要功能, 添加到库中的数据需要保证其的正确性; 约束, 就是让数据库帮助程序员更好的检查数据是否正确.

1.数据库常用约束

• not null -  指示某列不能存储 NULL 值.
• unique - 保证某列的每行必须有唯一的值.
• default - 规定没有给列赋值时的默认值.
• primary key - not null 和 unique 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录.
• foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性.
• check - 保证列中的值符合指定的条件; 对于MySQL数据库，对check子句进行分析，但是忽略check子句(MySQL5中不支持check).

2. not null

创建表时,指定某一列的值不能为null

create table 表名 (变量 类型 not null, ...);

原来的学生表是可以为null值的,我们删除新建一个表后,给id设置为不可为null

3.unique

在创建表时使用,指定某一列的值为唯一的,不可重复的

create table 表名 (变量 类型 unique, ...);

我们重新创建一张表,将名字设为not null, id编号设为 unique

4.default

指定插入数据时，如果某一列没有数据插入 ，则设置为默认值 ：

create table 表名 (变量 类型 default 默认值, ...);

重新创建表,并给名字设置默认值 "未知" , 插入两条数据,一条有name,一条没有name

最后查询表可以看到,没有设置name 的 显示默认值 "未知" 

5.primary key  主键

primary key是主键约束, 是一条记录的身份标识, 相当于not null和 unique结合的效果, 在创建表时使用, 对`某一列使用该约束, 则该列的值必须是唯一的且不能是null.

实际开发中, 大部分的表, 一般都会带有一个主键, 主键往往是一个整数表示的id.

create table 表名 (变量 类型 primary key, ...);

实际开发中,通过自增主键来设置primary key,默认从默认值开始自增,如果我们手动设置了一个值,则会从设置的值的下一位继续自增

create table (变量 类型 primary key auto_increment, ...);

重新创建一张表使id为主键并且能自增,如图默认从1开始自增

自增主键会有一个变量来记录当前的最大值,因此当我们手动设置为20后,下一次自增就是为21了

6.分库分表情况下的自增主键

在学习阶段,一台主机一个数据库足以满足我们学习的需求

在开发中,如果数据量很大,就需要分库分表,把大量的数据分摊到多个主机上进行存储,本质上就是,把一张大表分成两个/多个小的表, 每个数据库服务器, 分别只存一部分数据; 此时的增主键如何保证在多个主机上不重复呢?

在这个场景下, 如果再新插入一个数据, 这个数据就会落在三个服务器之一, 新的这个数据的主键id，如何分配才能保证不重复呢?

这里涉及到一个"分布式系统中唯一id生成算法", 实现公式如下:

实现公式=时间戳＋主机编号＋随机因子

时间戳保证在不同时间下的id不同,

再和主机编号组合保证如果再同一时间有不同的数据分散到不同主机上的id不同,

最后再和一个随机因子组合保证多个数据到同一个主机时id不同,

结合这三个部分,就可以得到一个全局唯一的id.

7.foreign key  外键

外键用于关联其他表的主键或者唯一键

-- 父表已经创建的前提下
create table 表名 (
  变量 类型,... 
  foreign key (子表中的一个变量) references 父表名 (父表中的一个变量)
);

foreign key是外键约束, 用来约束两张表之间的关系(相互约束), 在创建表时使用, 使用该约束要求表中某个记录必须在另外一个表里存在.

创建两张表,班级表有 classId  className  字段,      学生表有 studentId  name  classId  字段

尝试插入数据,因为class表为空,所以此时插入失败, 错误提示:  外键约束失败 

给父表插入数据后,classId已经存在了 1 ,再给学生表插入数据就不会报错了

可以试试将classId修改10,会报错,因为此时class表 没有classId为10的

修改为2则可以修改成功

再试试将class表中 classId为2 的数据删除

报错:不能删除/修改这个父行, 依旧是 外键约束失败报错

因为此时已经被子表的数据所依赖了,因此不能删除

总结:

父表在约束子表,同时子表也在反向约束着父表

子表新增/修改的数据不能超出父表的数据范围(父表要有)

父表不能删除已经被孩子表依赖的数据

二.表的设计

1.一对一关系

2.一对多关系

通过classId将多个 studentId , name  与  className关联起来,就是一对多

3.多对多关系

在数据库表的设计中,首先要明确各个实体(对象)之间的关系,明确之后就可以套用以上的方法,设计出符合需求的表结构