MySQL表的约束

1.6. 表的约束

1.6.1. 概念

1：为什么需要约束

       真正约束字段的是数据类型 , 但是数据类型约束很单一 , 需要有一些额外的约束 , 更好的保证数据

的合法性,从业务逻辑角度保证数据的正确性，如：有一个字段是身份证, 要求是唯一的

2：定义字段的类型无法满足对表的约束：

 表字段是否可以为 NULL, 有没有默认值 , 表字段的解释能不能加上

 对于数字类型的字段可不可以指定默认表示的位数,可不可以将这个字段设置称为唯一标识该

行的数据

3：本质上 Mysql 是一套数据存储解决方案 , 除了解决基本的数据存储功能之外 , 还要保证数据尽可能

的安全,减少用户的误操作可能性 , 约束的体现 , 不仅仅可以体现在数据库层面 , 在我们进行用户业务

逻辑编码的时候, 我们其实也可以做判断 ( 约束 ), 约束其实不仅仅只能出现在数据库层而上 , 编码层面

也是可以体现

4：约束是什么：约束是在表上强制执行的数据校验规则，本质上是 Mysql 通过限制用户操作的方

式 , 来达到维护数据本身安全及数据完整性的一套方案

5：数据的完整性要从以下四个方面考虑：

实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录

域完整性（Domain Integrity） ：例如：年龄范围 0-120 ，性别范围 “ 男 / 女 ”

引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门

用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理

的工资不得高于本部门职工的平均工资的 5 倍。

6：约束作为数据库对象，存放在系统表中，也有自己的名字

7：常见约束：

非空约束 、默认值约束 、主键约束 、外键约束 、唯一约束 、检查约束

8：创建约束的时机：

    在建表的同时创建

    建表后创建（修改表）

9：约束可定义列级或表级约束

1.6.2. 语法

列级约束：在定义列的同时定义约束

create table 表名 (列名 类型 约束 )

表级约束：在定义了所有列之后定义的约束

create table 表名 constraint 约束名 约束类型 (列信息)

创建表之后添加约束：

alter table 表名 add constraint 约束名 约束类型(要约束的列名)

约束名推荐采用：表名 _ 列名 _ 约束类型简介

1.6.3. 非空约束

作用：限定某个字段 / 某列的值不允许为空

两个值： null （默认的）和 not null( 不为空 )

列字段默认一般都是空 , 但是实际开发时 , 尽可能保证字段不为空 , 因为数据为空没办法参与运算

所有数据类型的值都可以是 NULL ，空字符串不等于 NULL ， 0 也不等于 NULL

示例：

若需要某些字段必须写入，应该怎么处理，如：创建一个班级表，包含班级名和班级所在的教室，

站在正 常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级，如果教室名字可以空，就

不知道在哪上课，所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据

就不能插入到表中，这就是“约束”

以上为创建表时的设置，也可以创建之后修改：

# 语法：

alter table < 数据表名 > change column < 字段名 > < 字段名 > < 数据类型 > not null ;

mysql> alter table student1 change column id id int not null ;

# 也可以通过 modify 修改

mysql> alter table student1 modify name varchar ( 30 ) not null ;

# modify 字句中省略 not null 相当于设置为可以为空

mysql> desc student1;

删除not null

1.6.4. 默认值约束

作用：给某个字段 / 某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋

值，则赋值为默认值。

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值

注意：若某字段都设置了 not null 和 default 约束，则按照不插入时会选择默认值，插入时选择插入

值，所以此时not null 毫无意义

 

# not null 和 defalut 一般不需要同时出现 , 因为 default 本身有默认值 , 不会为空

1.6.5. 列描述 --comment

列描述没有实际含义 , 专门用来描述字段 , 会根据表创建语句保存 , 供操作者来查看该列的含义，相当

于 C/C++的注释

1.6.6. 主键约束

1:作用：数据库存储数据不是唯一的目的 , 还要考虑到提取数据，一般要求数据库表中的记录要有一

个特定的唯一标识，来表示唯一性, 这个唯一的特定标识就是主键 , 类似于序号学号这样的唯一标识 ,

可以根据主键来唯一地筛选出一条记录

2:主键：primary key，用来唯一的约束该字段里面的数据

3:特点：

主键字段不能为空，不能重复

一张表中最多只能有一个主键

主键所在的列通常是整数类型

主键约束是最频繁的约束

4:注意：当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主

键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除

了，需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主

键的 值，就有可能会破坏数据的完整性。

示例： 创建时增加主键

mysql> create table t1 ( id int unsigned primary key comment ' 学号不能为空 ' , name

varchar ( 20 ) not null ,sex char ( 2 ) default ' 男 ' );

mysql> insert into t1(id,name) values ( 1 , ' 黎明 ' );

ERROR 1062 ( 23000 ): Duplicate entry '1' for key 't1.PRIMARY'

# 再次插入出错，因为需要保持唯一性

示例：修改表，追加主键

注意 : 如果该字段内有重复值 , 则不能以该字段新增为主键 , 必须先把重复的数据去掉然后才能添加该

列为主键

删除主键：alter table 表名 drop primary key;

复合主键：在创建表的时候 , 在所有字段之后 , 使用 primary key( 主键字段列表 ) 来创建主键 , 如果有多

个字段 作为主键, 可以使用复合主键 , 这些字段合在一起是一个主键，也就是让多个字段联合在一起

作为唯一标识 , 单个字段主键重复是没有问题的, 只要不是成为复合主键的字段一起冲突就行

1.6.7. 主键自增长

作用：给主键添加自增长的数值

auto_increment： 当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的

最大值+1 操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键

注意：

1:自增长字段必须是整数 , 自增长字段可以不设置初始值 , 默认从 1 开始递增 .

2:被自增长的字段必须作为主键或者其他具有唯一性的键使用 ,( 必须保证这一列字段具有唯一性的

字段)

3:自增长字段也可以插入数据 , 只要不与已有数据重复即可 , 之后新增数据会从最大值开始递增 .

4:任何一个字段要做自增长 , 前提是本身是一个索引（ key 一栏有值） .

5:一张表当中最多只能有一个自增长的列

6:约束的字段必须具备 NOT NULL 属性

示例

mysql> insert into student1(name) values ( ' 关六 ' );

Query OK, 1 row affected ( 0.00 sec)

mysql> select * from student1;

# 发现自动增长

指定自增长的起始值：如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值

开始自 增。例如，如果表中插入的第一条记录的 id 值设置为 5 ，那么再插入记录时， id 值就会从

5 开始往上增加

1.6.8. 唯一性约束

1:产生原因：一张表中有往往有很多字段需要唯一性 , 数据不能重复 , 但是一张表中只能有一个主键 ,

唯一键就可以解决表中有多个字段需要唯一性约束的问题

2:作用：限制某个字段 / 某列的值不能重复

3:唯一键和主键的关系：

1):主键更多的是标识唯一性

2):唯一键更多的是保证在业务上 , 不要和别的信息出现重复

3):主键只能有一个 , 唯一键能设置多个

4):主键和唯一键不是对立关系而是补充关系 , 目的是让数据库创建出一个表具有非常强的约束 , 彻底

杜绝用户的误插入，在一张表当中,可以有一个主键, 也可以同时具有唯一键 , 它们不是用来对比的 ,

只是负责数据在不同层面的唯一性,既要保证选择成为主键的属性字段不能重复不能为空, 同时也要

保证本身具有唯一性的列的字段不能出现冲突

5):主键用来查找 , 唯一键用来保证数据的完整性

6):如：学生信息中有身份证号和学号两个字段，可以把身份证号设置成为主键 , 而所有学生的学号

也不能重复,此时就可以把学号设置成唯一键

示例：创建表时实现

唯一键冲突，自增涨字段值不连续

1.6.9. 外键约束

1：作用：限定某个表的某个字段的引用完整性

2：概念

主表（父表）：被引用的表、被参考的表

从表（子表）：引用别人的表，参考别人的表

外键：从表中的某个字段引用自主表的某个字段或多个字段

引用键：主表被引用的字段

3：外键约束主要定义在从表上 , 主表则必须是有主键约束或唯一键约束 . 当定义外键后 , 要求外键列

数据必须在 主表的主键列存在或为 null.

格式：foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联

示例：

示例：

1.6.10. 检查约束

1：作用：检查约束（ check ）是用来检查数据表中字段值有效性的一种手段，一般用于设置值的

范围

2：注意

设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入

在默认值和非空约束可看作是特殊的检查约束

注意检查约束在 8.0.16 之前， MySQL 默认但不会强制的遵循 check 约束（写不报错，但是不生效，

需要通触发器完成），8 之后就开始正式支持这个约束了

示例1：创建表时设置检查约束

示例2：修改表时添加检查约束

# 格式：

alter table 表名 add constraint < 检查约束名 > check ( 检查约束 )

mysql> alter table t5 add constraint check_id check (id> 0 );

Query OK, 1 row affected ( 0.02 sec)

Records: 1 Duplicates: 0 Warnings : 0

1.6.11. 删除表的约束：

1.6.12. 存储引擎

默认情况下， 创建表不指定表的存储引擎， 则会使用配置文件的 my.ini 中 default-storage-

engine=InnoDB 指定的 InnoDB

查看支持的引擎命令： show engines \g

查看当前默认存储引擎

在创建表时， 可以指定表的存储引擎: create table (...) engine = InnoDB ;

1.6.13. 总结 - 完整的建表语句：

1.6.14. 练习

创建销售数据库 mydb5_sales

使用该数据库

创建产品表 pro_tb ( 产品编号 pro_num int(10) 唯一键 ，产品名 pro_name varchar(30) ，产品单价

pro_price float(5,2) , 产品规格 pro_spe varchar （ 8 ） 默认值：件 )

创建订单表 orders ( 订单号 id int(6) , 客户号 customer_id int(4), 产品编号 pro_num int(10) 外键 , 签订

日期 signing_date date,金额 amount float(5,2) ) , 其中订号单为主键、自增长，金额设置 check （大

于 0 ）