【MySQL 06】表的约束

♬咖啡猫♬

已于 2024-08-15 12:31:18 修改

阅读量2.6k

点赞数 77

分类专栏： MySQL 文章标签： mysql 数据库 linux

于 2024-08-14 05:00:00 首次发布

本文链接：https://blog.csdn.net/shangguanxiu/article/details/141128997

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

🌈 一、约束的概念

在 MySQL 中，可以使用数据类型约束字段，如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败。
但仅仅用数据类型来做约束显然不够全面，还需要一些额外的约束保证数据的合法性，从业务逻辑角度保证数据的正确性。

对此，MySQL 提供了对表的约束，逼着用户在插入数据时一定要在语法上把代码写对，尽可能的保证数据的完整性和可预期性。

表的约束有很多，此处主要介绍几个常用的约束：

null / not null	 	# 空属性约束
default 	     	# 默认值约束
comment	    	 	# 列描述约束
zerofill		 	# 补零约束
primary key		 	# 主键约束
auto_increment	 	# 自增约束
unique key		 	# 唯一键约束

🌈 二、空属性约束

空属性有两个值，分别是 null (默认的) 和 not null (不为空)。
数据库一般都是默认允许字段为空，但在实际开发中要尽可能保证字段不为空，因为空值无法参与运算。

⭐ 1. 空值无法参与运算

通过 select 关键字可以查看 null 的值为 null。

由于空值无法参与运算，因此不管对 null 值进行什么操作最后得到的结果依然是 null。

⭐ 2. 设置非空属性

平常在填表的时候，某些格子旁边可能会有个 * 表示这个格子必须填写，即不能为空。
如果要让某个字段不允许为空，在创建表的时候就可以给对应字段设置 not null 属性。

设置非空属性案例

创建一张名为 myclass 的班级表，表中包含班级名和该班级所在的教室，给这两个字段设置 not null 表示在插入数据时必须要给这两个字段插入。

向表中插入数据时只有这两个字段都不为空时才能插入成功，否则操作会被 MySQL 拦截。

🌈 三、默认值约束

当某个字段经常出现某个值的时候，可以为该字段设置一个 default 默认缺省值。
如果字段设置了 default，用户将来插入数据，如果有具体的数据，就用用户的，否则就用默认的。
如果在建表时，没有给字段指定默认值，也没有给字段设置为 not null，那么 MySQL 会自动将 null 设置为该字段的默认值。

⭐ 1. 默认值使用案例

创建一张名为 user 的用户表，表中包含用户的姓名、年龄、性别三个字段，设置年龄字段默认为 18，性别字段默认为 ‘男’。

向表中插入数据时，如果不指定用户的年龄或性别，就会使用对应的默认值，如果指定了则使用用户所指定的值。

⭐ 2. 同时设置 not null 和 default

在给某个字段设置了默认值之后，由于不插入数据时该字段会用默认值填充，该字段将不会再出现空值。
给字段设置 not null 的目的是让该字段不为空，但设置了 default 之后该字段本来就不会为空，因此在设置了 default 后再设置 not null 就没意义了。

举个栗子

创建一张名为 t11 的表，表中包含姓名和年龄两个字段，对姓名字段设置为 not null，对年龄字段设置为 not null 和 default 18。

在插入数据时，即使不往 age 字段插入数据也没事，因为会用设置好的默认值填充。

🌈 四、列描述约束

comment 列描述，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或 DBA 来了解的相关信息，相当于一种注释。
当程序员看到这些信息之后，就能大概确定对应字段的具体描述是什么，相当于一种软性约束。

举个栗子

创建一张名为 t12 的表，表中包含姓名、年龄、性别三个字段，并在每个字段的后面添加上对该字段的描述。

当用户不知道某个字段的具体含义时，可以使用查看建表语句指令查看对应字段的 comment 字段描述。
```
show create table 表名 \G		# 查看指定表的建表语句
```

🌈 五、zerofill 补零约束

数值类型后面的圆括号中的数字，代表的是显示宽度，比如 int(5) 表示该字段的数字占 5 个宽度单位。
如果没有 zerofill 这个属性，括号内的数字是毫无意义的，对应数值类型在设置了 zerofill 属性后，如果数据的宽度小于设定的宽度则自动补 0。

举个栗子

创建一张名为 t13 的表，表中包含两个类型为 int(5) 的字段，将这两个字段的显示宽度都设置成 5，其中 a 字段设置了 zerofill 约束，b 字段没有。

分别往 a、b 两个字段各自插入一个数字 1，设置了 zerofill 的 a 字段会自动补 0 直到宽度达到设置的 5，而没有设置 zerofill 的 b 字段则无事发生。

注意：zerofill 的作用仅仅只是让数据以特定的方式进行显示而已，数据底层的储存方式并没有发生变化。
- 通过 hex 函数可以看到 a 字段中显示的 00001 实际在底层存储的还是个 1.

zerofille 是一种至少的行为，如果插入的数据宽度 > 指定宽度 (这里的指定宽度为 5)，则 zerofill 不会起作用，该怎么样就还是怎么样。

🌈 六、主键约束

⭐ 1. 主键

primary key 主键：是用来唯一的约束字段里面的数据的，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
- 当向表当中插入数据后，可以选择其中的某一字段作为键值 key，当需要查找数据时就可以根据这个键值 key 来查找对应的数据。

1. 主键使用示例

创建一张名为 student 的学生表，表中包含学生的学号和姓名，由于学号不会重复，且能稳定通过学号查询到指定学生，因此可将学号设为主键。
- 虽然在创建表的时候没有给学号设置 not null 属性，但由于主键本身就是不能为空的，因此 id 默认也就不能为空了。

主键字段的值都是唯一的，给表中的主键字段插入数据时，如果插入了重复的数据，MySQL 会拦截本次数据插入的操作。

2. 根据主键查询指定信息

由于主键字段的值在表中唯一，因此可以根据主键字段的值查找到指定信息。
- 比如在学生表中，就能根据学号准确的找出一个学生的所有信息 (注：已经在表中备好了 3 个学生的信息)。

3. 删除指定表的主键

alter table 表名 drop primary key;

因为一个表只有一个主键，因此删除主键时只用指明要删除哪张表的主键即可。
- 例如：删除 student 学生表的主键后再查看表结构，会发现看到 id 对应的 Key列的 PRI 消失了。

4. 给已存在的表添加主键

alter table 表名 add primary key(字段名);

只有字段当中的值不为空且不重复的字段才能被设置成主键。
- 例如：重新将 id 这一列设置成主键后再查看表结构，可以看到 id 对应的 Key列的 PRI 又回来了。

主键可以在建表时或建表后设置，但不要在使用了表之后再设置主键，不然就纯属坑人了。

⭐ 2. 复合主键

虽然一张表最多只能由一个主键，但并不表示一个表中的主键只能添加给一列，可以有多个字段共享一个主键，这种就叫做复合主键。
复合主键用来唯一约束多个字段里面的数据，表中被设置了复合主键的这多个字段不能同时重复也不能为空。
- 设置了复合主键后，被设置复合主键的多个字段合起来才是一个主键。
```
primary key(字段名1, 字段名2, ..., 字段名n)
```

1. 复合主键使用示例

创建一张名为 select_course 的选课表，表中包含 id、课程代码、成绩三个字段，由于 id 和课堂代码都不能重复，因此可设置成复合主键。

在向选课表中插入数据时，只有 id 和 course 同时与旧数据冲突才会被 MySQL 拦截，这两个任意一个冲突都能将数据插入成功。
- 为了演示冲突情况，已提前在表中插入了 id 为 1，course 为 123，score 为 60 的数据。

查看表中的数据可以看到，表中会有重复的 id，也会有重复的 course，但就是不会出现 id 和 course 都重复的情况。

3. 删除指定表的复合主键

alter table 表名 drop primary key;

即使是复合主键，在每张表中也是唯一的，因此可以直接指定表名来删除复合主键。
- 例：删除选课表的复合主键后再查看表结构，可以看到 id 和 course 字段的 PRI 消失了。

4. 给已存在的表添加复合主键

alter table 表名 add primary key(字段名1, 字段名2, ..., 字段名n);

对于已经创建好了的表，也能对多个列形成复合主键，被选取的多个列当中的值不能为空且不能同时出现重复。
- 例：将 id 和 course 设置成选课表的复合主键后再查看表结构，可以看到 id 和 course 对应的 Key 列的 PRI 又出现了。

🌈 七、自增长约束

auto_increment：设置了自增长属性的字段，插入数据时如果不给该字段值，系统会对当前字段中已有的最大值进行 +1 操作后插入该字段。
自增长通常搭配主键使用，作为逻辑主键。
一般而言，建议将主键设计成与当前业务无关的字段，避免因为业务逻辑的调整而需要修改主键。

⭐ 1. 自增长的特点

任何一个字段要做自增长，前提是字段本身必须是一个索引 (Key一栏有值)。
自增长字段必须是整型，一张表最多只能有一个自增长字段。

⭐ 2. 自增长使用案例

创建一张 t14 的表，表中包含 id 和 name 两个字段，将 id 字段设为主键和自增长字段。

当往表中插入第一条数据时，如果没有指定自增长字段的值，自增长字段的值默认会从 1 开始。

之后再向表中插入数据时，如果也不指明自增长字段的值，自增长字段的值逐次 +1 递增。

插入数据时，也可以自行指定自增长字段的值，前提是不能与旧数据发生冲突。

由于自增长字段是从已有数据的最大值开始依次递增的，即下次自增长的值以 100 为基准开始递增。

⭐ 3. 建表时设置自增长字段的起始值

可以在建表语句的 ; 前 ) 后添加一段 auto increment=起始值设置自增长字段的起始值。

示例

创建一张名为 t15 的表，表中包含 id 和 name 两个字段，再将 id 字段设为主键和自增长。
- 将自增长字段的值设置成从 500 开始。

之后再插入数据时，只要不指定 id 字段的值，就会从 500 开始自增。

🌈 八、唯一键约束

一张表中通常有很多字段的数据都是不能重复的，这些字段也需要有唯一性，但一张表又只能有一个主键，此时就需要拿出唯一键 unique了。
- 注：unique key 可以简写成 unique。
unique 唯一键的本质和主键差不多，都能够保证字段中数据的唯一性，但唯一键允许字段为空，且可以多个字段为空，空字段不做唯一性比较。
唯一键并不能保证录入的数据一定是正确的，只能保证能够录入到表中的数据是合法的。
- 例：在录入数据时，录入数据的人就是把数据填错了，但这个数据又恰好不与表中的其他数据冲突，这个数据就是合法但错误的。

⭐ 1. 主键和唯一键的区别

主键更多的是用来标识唯一性，而唯一键更多的是保证在业务上不和别的信息重复。
主键只能有一个，而唯一键可以有多个。

1. 举个栗子

某公司需要有一个员工管理系统，系统中有一张员工表，表中有两列信息，分别是身份证号、员工工号，可以选择身份证号作为主键。
但是在设计员工工号的时候，也需要一种约束保证所有员工的工号不能重复。
这里的唯一键约束具体指的就是在公司的业务上不能重复，在设计表的时候需要这个约束，因此就能将员工工号设计成唯一键。

2. 尽量将主键设计成和业务无关的字段

建议将主键设计成和当前业务无关的字段，这样，当业务调整的时候，可以尽量不会对主键做过大的调整。

⭐ 2. 唯一键使用案例

创建一张名为 stu 的学生表，表中包含学生的 id (身份证号)、姓名、学号，将 id 设置成主键，但学号也应该具有唯一性，即对学号设置唯一键。

向表中插入数据时，不填主键，让它自增去，只要唯一键的学号不与现有数据冲突，即可插入成功，如果发生冲突，则会被 MySQL 拦截。

由于唯一键允许为空，在向表中插入数据时可以不指明唯一键字段的值，此时该字段就会默认为空，不做唯一性比较。
- 前一个插入失败由于是由冲突所导致的，自增长字段的 3 被上一条冲突而导致失败的记录占用了，因此下一个值从 4 开始。

🌈 九、外键约束

foreign key 外键是用来定义主表和从表之间的关系，外键约束主要定义在从表上，而主表则必须要有主键约束 / 唯一键约束。
当定义好外键后，外键列数据必须在主表的主键列存在或为 null。
外键更强调的有两点：表跟表之间的关联、表跟表之间的约束。

添加外键的语法格式

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

在从表中，可以通过定义外键，与主表的某个字段进行映射，从而找到主表。

⭐ 1. 外键约束案例

创建主表：创建一张名为 class 的班级表作为主表，表中包含班级的 id 和班级名，将班级 id设置为主键。
- 为了和从表进行区分，主表的班级 id 用 class_id_1 表示。

创建从表：再创建一个名为 student 的学生表作为从表，表中包含学生的 id、姓名、学生所在班级所对应的班级 id。将学生表中的班级 id 字段设置成外键，与班级表的班级 id 字段进行关联。
- 为了和主表进行区分，从表的班级 id 用 class_id_2 表示。

先往 class 主表中插入两条数据，方便后续演示。
- 表示当前只存在班级 id 为 1000 和 1001 的班级。

往 student 学生表中插入数据时，如果插入的数据对应的班级 id 是在 class 班级表中存在，则能够插入成功。

如果要往 student 表中插入一个班级 id 为 1002 的学生信息，由于 class 表中没有班级 id 为 1002 的班级，因此本次插入会被 MySQL 拦截。

但如果往 student 表中插入一个班级 id 为空的学生信息 (目前还没有分配班级)，此时是允许插入的。

删除 class 主表中班级 id 为 1000 的班级，由于该班级还有学生存在，MySQL 不允许执行该操作。

⭐ 2. 理解外键约束

这个世界是数据很多都是相关性的。
在上述例子中，如果没有创建外键约束，就能在没有对应班级 id 的情况下，却能将学生插入到不存在的班级。或者某个班级在还有学生的情况下，却能直接将对应 id 的班级删除掉，学生还在呢，班却没了。
这两种情况显然都是不合理的，因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。
解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给 MySQL 去审核，提前告诉 MySQL 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，MySQL 就会拦截用户的操作。

🌈 十、综合案例

1. 案例描述

实现一个商店的数据库，用来记录客户及购物情况，该数据库中有三张表，各自的表名以及每张表中的字段名如下：
- 商品表 goods：商品编号 goods_id，商品名称 goods_name, 商品单价 unitprice, 商品类别 category, 供应商 provider)。
- 客户表 customer：客户编号 customer_id，客户姓名 name，客户住址 address，客户邮箱 email，客户性别 sex，身份证号 card_id。
- 购物表 purchase：订单编号 order_id，客户编号 customer_id，商品编号 goods_id，购买数量 nums。

2. 案例要求

设置主键、外键。
客户的姓名不能为空值。
邮箱不能重复。
客户的性别（男，女）。

3. 案例实现

创建 store 数据库：创建一个名为 store 的商店数据库，然后使用该数据库。

创建 goods 商品表：设置商品编号 goods_id 字段为主键，并将其设为自增长字段，其他字段按需设置约束即可。

创建 customer 客户表：将客户编号 customer_id 设置成主键，并设置成自增长字段，给客户姓名 name 设置 not null 属性，将客户邮箱 email 设置成唯一键，将客户性别 sex 设置成 enum 类型并仅提供男女性别选项，将客户身份证号 card_id 设置成唯一键。