【MySQL】表的约束

表的约束

表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段，倒逼用户，插入正确的数据。反过来，在 mysql 角度，凡是插入进来的数据，都是符合数据约束的！约束的最终目的就是保证数据的完整性和可预期性。因此我们需要更多的约束条件！

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是 email，要求是唯一的。

表的约束很多，这里主要介绍如下几个： null/not null，default，comment，zerofill，primary key，auto_increment，unique key .

一、空属性

• 两个值：null(默认的) 和 not null(不为空)
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算，任何数与 null 相加都为 null，如下：

假设我们需要创建一个班级表，包含班级名和班级所在的教室，站在正常的业务逻辑中：

• 如果班级没有名字，你不知道你在哪个班级
• 如果教室名字可以为空，就不知道在哪上课

所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“约束”。所以我们要给班级和姓名加上非空约束：

        create table class( class_name varchar(20) not null, class_room varchar(10) not null );

下面我们查看表结构，我们可以看到 Null 列的内容为 NO，即插入的数据不能为空：

接下来我们插入几组数据测试：

我们可以看到，当班级名或者教室名其中一个为空都不能插入。

二、默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。默认值的关键字为 default.

下面直接创建一个表结构，包括学生的姓名，年龄，性别；我们给学生姓名加上非空约束，年龄和性别给默认值：

            create table t1(
                -> name varchar(20) not null,
                -> age tinyint unsigned default 0,
                -> gender char(2) default '男'
                -> );

下面我们查看一下表结构，我们就可以看到 Default 这一列就给我们加上了默认值：

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

注意：只有设置了 default 的列，才可以在插入值的时候，对列进行省略。

接下来我们给 gender 字段修改加上 not null 约束：

alter table t1 modify gender char(2) not null default '男';

查看表结构，此时 gender 字段就不能为空了：

此时我们插入数据，给 gender 字段给空，是会报错的：

但是我们不给值，让它使用默认值就可以插入：

所以当 not null 和 default 同时出现时，它们并不冲突，而是相互补充的。当用户忽略了这一列的时候，如果设置了 default，就是用默认值，如果没有设置，就直接报错，因为有 not null 约束。

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

三、列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或 DBA 来进行了解。

例如：

            create table if not exists t2(
                -> name varchar(20) not null comment '姓名',
                -> age tinyint unsigned default 0 comment '年龄',
                -> gender char(1) default '男' comment '性别'
                -> );

通过 desc 查看不到注释信息：

通过

show create table t2\G;

可以看到：

四、zerofill

刚开始学习数据库时，很多伙伴对数字类型后面的长度很迷茫。通过 show 看看 t3 表的建表语句：

可以看到 int(10)，这个代表什么意思呢？整型不是 4 字节吗？这个 10 又代表什么呢？其实没有 zerofill 这个属性，括号内的数字是毫无意义的。a 和 b 列就是前面插入的数据，如下：

但是对列添加了 zerofill 属性后，显示的结果就有所不同了。修改 t3 表的属性：

alter table t3 modify a int(5) unsigned zerofill;

查看表结构，a 列有了 zerofill 属性：

再查看数据：

这次可以看到 a 的值由原来的 1 变成 00001，这就是 zerofill 属性的作用，如果宽度小于设定的宽度（这里设置的是 5），自动填充 0；要注意的是，这只是最后显示的结果，在 MySQL 中实际存储的还是1；为什么是这样呢？我们可以用 hex 函数来证明，hex 表示以十六进制显示：

可以看出数据库内部存储的还是1；00001 只是设置了 zerofill 属性后的一种格式化输出而已。

五、主键

主键：primary key 用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

例如，创建表的时候直接在字段上指定主键：

            create table t4(
                -> id int unsigned primary key comment '学号',
                -> name varchar(20) not null
                -> );

查看表结构，id 字段为主键

• 主键约束：主键对应的字段中不能重复，一旦重复，操作失败；例如：

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

例如删除上表的主键：

• 当表创建好以后但是没有主键的时候，可以再次追加主键： alter table 表名 add primary key(字段列表)

再为上表添加主键：

注意，一张表中最多只有一个主键，不意味着一个表的主键，只能添加给一列！一个主键可以被添加到一列，或者多列上，这种叫做复合主键。

在创建表的时候，在所有字段之后，使用 primary key (主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

例如，创建一个具有复合主键的表结构：

            create table t5(
                -> id int unsigned,
                -> course char(10) comment '课程代码',
                -> score tinyint unsigned not null comment '成绩',
                -> primary key(id, course)  --- id 和 course 设置为复合主键
                -> );

查看表结构：

其中主键由 id 和 course 组成，我们尝试插入数据：

如上，当重复插入 id 为 1，course 为 1001 的数据时会报错，因为主键已经存在了。

六、自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值 +1 操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

例如，创建一个有自增长的表结构：

create table t6( id int unsigned primary key auto_increment, name varchar(10) not null);

只在 name 字段插入数据：

然后查看表数据：

我们发现 auto_increment 默认是从 1 开始计数的，如果我们中途插入一个其它 id 的数据，然后继续默认插入呢？如下：

继续不给 id 插入：

我们可以看到，auto_increment 是从上一次插入的结果中继续往下增长的；在插入后获取上次插入的 auto_increment 的值（批量插入获取的是第一个值），我们可以通过

select last_insert_id();

查看上一次插入的 auto_increment 的值：

索引：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的 SQL 语句执行得更快，可快速访问数据库表中的特定信息。

我们会在后面详细讲索引的概念。

七、唯一键

唯一键：unique；一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键；唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

• 关于唯一键和主键的区别：

我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。这样说好像没啥区别，举一个例子：

假设一个场景，比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。

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

实例：

            create table student(
                -> id char(10) unique comment '学号，不能重复，但可以为空',
                -> name varchar(20)
                -> );

查看表结构：

插入相同的 id 会报错，因为 id 字段具有唯一键：

但是可以插入空：

八、外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或 unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为 null.

语法：

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

实例：

假设我们对上面的实例进行设计：

先创建 stu 表：

            create table stu(
                -> id int primary key,
                -> name varchar(20) not null,
                -> class_id int
                -> );

再创建 class 表：

            create table class( 
                -> class_id int primary key,
                -> name varchar(20) not null
                -> );

随后正常插入数据，先插入 class 表：

再插入 stu 表：

但是此时这两张表是两张独立的表，它们只有对应的关联关系，并没有任何的约束关系。假设我们现在来了一位新学生，它隶属于 class_id 为 30 的班级：

但是我们知道，在 class 表中却没有 class_id 为 30 的班级，但是这位学生却进入了 stu 表中，所以没有约束关系可能会导致我们错误地插入了一个不存在班级的学生；除此之外，如果我们删除了 class_id 为 20 的班级，也是不会报错的，因为它们之间没有约束关系，这也会导致我们可能删除了一个还有学生的班级！

所以以上两张表现在只有关联关系，却没有约束关系，是有问题的！外键就很好地解决了这个问题，外键就是为这两张表建立外键约束。

那么我们要为哪个表添加外键约束呢？我们知道，一个学生一定是隶属于某一个班级的，所以 stu 应该是从表，我们要为从表添加外键约束！主表则是 class.

所以我们需要重新创建一个 stu 表，为 stu 表添加外键约束。我们先删除原来的 stu 表：

drop table stu;

再重新创建 stu 表：

            create table stu(
                -> id int primary key,
                -> name varchar(20) not null,
                -> class_id int,
                -> foreign key(class_id) references class(class_id)
                -> );

接下正常插入数据：

然后插入一个不存在的班级的同学，会出现错误，因为外键约束：

我们再尝试删除一个还有同学的班级：

delete from class where class_id=10;

我们还可以插入一个班级 id 为空的同学，代表目前还没有分配班级：

如上就是外键约束为我们解决的问题。建立外键的本质其实就是把相关性交给 mysql 去审核了，提前告诉 mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql 不允许你插入。