【MySQL】表的约束

表的约束

点赞👍👍收藏🌟🌟关注💖💖
你的支持是对我最大的鼓励，我们一起努力吧!😃😃

前面我们把列名称和类型都了解了，但是在实际查表得时候它们后面是什么东西呢？今天就来说一说！

前面我们说过数据类型是表的一种约束，真正约束字段确实数据类型，但是只有数据类型这一种约束在建表的时候确实挺单一的。所以需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

表的约束到底是什么意思呢？到底约束谁的呢？
今天如果没有约束那你就可以想怎么写就怎么写，比如体重那一列插入QQ号、身高插入身份证号，你能保证你不会插错吗？所以，

表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员，插入正确的数据。反过来，站在mysql的视角，凡是插入进来的数据，都是符合数据约束的！

约束的最终目标：保证数据的完整性和可预期性。 因此需要更多的约束！

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

1.空属性

在C/C++语言NULL一直代表的是0，但是在MySQL这里NULL真的代表没有。NULL 真的什么都没有， ‘ ’ 空串是有的只不过这个串是空的。

NULL也不会参与计算。

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

在建表的时候把列名称和列类型后面还要在跟上这一列是否为空，可以不带，不带就默认允许为空，如果带比如说not null就代表这一列不能为空。

在做表插入的时候有一列就不想为空就可以设置not null属性，如注册账号，有几列要求你必要要填的，不填就不能提交。就可以在设计这一列的时候给not null属性。

创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：

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

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

create table if not exists myclass(
     class_name varchar(20) not null,
     class_room varchar(20) not null,
     other varchar(20));

Null这里代表未来这一列是否为空，这里是no表示这一列未来不能为空，yes表示这一列未来可以为空。

然后我们在查看建表的语法，这里mysql做了优化。我们发现other这一列我们是只写了一个varchar，没有指定not null，默认是null的，然后面加了一个default null，这里表示你想插就插，不插这一类就给默认值null。

我们接下来看是如何约束的

我们先做的是全列插入，可以插入也完全没毛病，也符合预期，非空约束，我确实没有空。

other这一列去掉，我不插，发现用的是默认值null，也符合预期，因为我没加非空约束

现在把class_room 也都去掉，发现不允许插入，报错是没有默认值。

class_room不去掉，但是我不知道这个教室在哪里，所以我设置为null，发现也不能插。报错不允许为空

这就和上面other形成鲜明对比。没有不指定not null，后面带了默认值。然后不插的时候用的就是这个默认值插了就用你插入的值。但是指定not null了后面就不带默认值了，你不插说你没有默认值，你插null说你插的不对。

这就是非空约束，换句话说只要某列设置了not null，必须要插具体值，不插因为后面没有默认值就报错，插入null也报错。设置默认为null，可以不插用的是后面带的默认值。 关于这个默认值的问题结合下面一起说。

2.默认值

default代表默认值，有时候插入时你的表可能有多列，但是可能并没有把每一列都指明要插入什么数据，在有的没有指明要插入什么数据的时候，这个默认值就起效果了。

默认值：某一列会经常性的出现某个具体的值，可以在一开始就指定好，用户输了就用用户的，用户没输就用默认的。

就比如一张表里面性别这一列默认设为男，你如果不输就默认为男，你输入了女或者男就用你输入的。

可以看到指明为not null 这里就是no，没有指明就是yes说明这一列可以为空，另外指明了default，就用指定的default。 前面的null先不用管。

首先全列插入，虽然有默认值，但是我自己指明要插的是什么，就用自己指明的。

当前我就插入一个name，也是可以插入的，因为age，gender默认可以为空，然后用默认值

这就是默认值。default 如果设置了，用户将来插入，有具体的数据，就用用户的，没有就用默认的。如果自己没设置就默认为null，如果设置了默认的就用自己设置好的默认的。

那not null和default有什么关系呢？

既可以指定not null也可以指定default是没有问题的。

注意设置过not null后面mysql就不会给我带default了，说明这一列不允许为空。

我给name插入null，不让我插，报错不能插空
我不给name插入，报的是没有默认值

报错是不一样的。这说明这次在插入的时候，我忽略了name列，不做插入，此时也没有说向name列中插入对应数据，那么此时约束我的根本就不是not null，约束我的是默认值，而name当前是not null并且后面根本没有带默认值，所以不允许进行插入。

如果我们没有明确指定一列要插入，用的default，如果建表中，对应列默认没有设置default值，无法直接插入。

我们看看是不是这样的。
当前指定gender要插，但是插入的是null，不让我插。这没问题因为not null就限定必须插入合法的数据不能插null

然后gender这一列不指定要插了，这个时候又让我们插了

总结：default和not nul 并不冲突，而是互相补充的。

当用户指明这一列要插的时候，受null和not null 约束，要么插null，要么插合法数据。总之用户指明这一列要插 ，not null来约束。

当用户忽略这一列的时候，如果设置了默认值使用默认值，如果没有就直接报错。总结用户忽略这一列要插，default来约束。

最后再看age这一列只设置了default，我可以不插用default，插了用自己插入的，也可以为null因为没有用not null。

还有一个问题，如果建表的时候， 不给某一列添加任何约束，可以插自己想插入的值，也可以是null，并且不插的时候给我们显示是null。我明明没有指明defalut啊，为什么让我插入了呢？

如果建表的时候， 不给某一列添加任何约束，我们会发现MySQL会对sql语句优化，默认会带上defalut null。所以不插入的时候在表示会显示null。

如果建表时给某一列设置了not null，MySQL就不会在该列后面默认带上default null了。这样这列就不能省略，必须要插入。除非自己在后面在加上defalut。这样插入时受not null约束，不插入时受defalut约束。而且如果只设置default，前面默认是null，可以显示插，用的是defalut默认值。

总之，列后面跟not null属性 这个类必须要插入，没有指定not null默认为null，可以不指明插入用的是default，指明插入用的是自己的。

3.列描述

列描述：comment，没有实际含义，专门用来描述字段，相当于对表中字段的说明。在表中并不会说因为不符合不让你插入。就相当于C/C++里的注释一样。

create table if not exists t5(
     name varchar(20) not null comment '这个是用户的用户名',
     age tinyint unsigned default 18 comment '这个是用户的年龄');

可以理解成软性约束，这是给程序员或数据库管理员来看的，了解这个列的含义。

4.zerofill

实际上zerofill是一个显示上面的约束，在一些标准显示的场景要被用到。
以前我们再说数据类型的时候，float、double、 char、varchar等后面都带()，()里面都带有数字，但是int后面()带数字我们没有说过。int后面带的到底是什么呢？不废话，建一张表再说

create table if not exists t6(
    a int unsigned not null,
    b int unsigned not null
    );

可以看到建表的时候并没有指定int后面带()，但实际会给我带一个()

为什么会有呢？这还是因为mysql回对我们提交的sql进行优化，真正执行的是下面的sql

现在重点不是这个，而是这个10到底是什么？

现在正常插入没有什么问题

但是我们把b列添加zerofill的约束

在查刚才的表，发现b这一列变成这个样子，共10个数，前面补0

所以，zerofill是给特定一列添加zerofill属性，如果显示出字符个数小于int（）里面的数字，就会在前面默认补0，直至个数满足int（）里面的数字。注意数据库里面存的该是多少就是多少不会变。如果显示字符个数正好等于或者超过int（）里面数组，你插入的是多少就显示多少。

按照16进制显示

select a,hex(b)from t6;#指定显示某列

可以看到16进制对应的就是10进制的222，200。数据库里面该存多少就是多少

不超过int（）里面数字补0，等于或者超过插入多少显示多少。

如果以后你想显示出的是001，002就可以设置zerofill。

还有一个细节，为什么int（）后面带的是10？更准确的是为什么 unsigned int（）是10，int（）是11？

int占4个字节，有符号取值范围 -2^31 - 2^31-1，无符号取值范围 2^32-1，无论是 2^31，还是 2^32 转成10进制是21亿多，42亿多，这么大的数字其实最后表示处理也就是10位，8位千万9位亿10位十亿。 用10就可以把所有整数数据位全都表示出来。int 多出一个1是因为它是有符号的，多一位标识符号位。

5.主键

mysql建表的时候，可能会存在各种各样的约束的，其中有一种约束叫做主键约束。主键约束指的是一张表在建立时需要一列信息用来标定该一条记录的唯一性。 因此选定或者说是设置某一列为主键。

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

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

create table if not exists t8(
    id int unsigned primary key comment '学号',
    name varchar(20) not null
    );

id我们设为主键，我们发现这个表结构有个属性列key，key代表当前这个列是否为主键列。
pri代表是主键，其次我并没有给id设置not null约束，你会发现id这里直接就no，不允许为空。
所以默认你只设置主键，同样的自动会加上not null约束。

创建表的语句和我们自己写的不太一样。

当我给主键列进行重复插入的时候，你会发现报主键冲突的错误。

所以相同key不让你插入，必须插入不同的值

主键约束对于程序员来讲，未来想往这个表里面插对应插入的数据主键列不能冲突，一旦冲突不让你插入，所以倒逼程序员插的时候尽量不要出现主键冲突。其次站在mysql视角凡是插入这个表里面的数据主键一定是不冲突的。这样的好处是根据主键绝对能拿出来确定的一条记录！

有了主键可以有针对性的对数据进行增删查改

update t8 set name='王五' where id=2;//更新表中数据

创建主键有两种方法：

1. 创建表的时候就把主键设置好
2. 表建好之和但没有主键，可以追加主键

第一种刚才就是，下面看第二种如何做，首先原始表中的主键先去掉，然后再添加主键：

删除主键
因为一张表主键就一个，所以直接删就行了

alter table 表名 drop primary key;

没有主键约束了，虽然id重复了但是也可以插入

添加主键

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

当我重新添加主键，它不让我添加了，2号主键冲突了

原因是我们刚才故意插入了id重复的值，所以不让我插入了。所以你想把某列设为主键，必须要先保证这一列没有数据或者有数据但不重复。

删除表中某个记录

delete from t8 where name='赵六';

删完之后再设置发现就可以了，然后id主键约束这一列就不可以插入重复的值了

虽然主键这里不能重复，但是name这里没有，也还可以重复，

虽然一张表中最多只能有一个主键，但是并不意味着一个表中的主键只能添加给一列！也就是说一个主键可以被添加到一列，或者多列上。

一个主键被添加到多列上的数据我们就叫做复合主键

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

下面创建表我们让两列合起来充当一个主键，如创建一张表让一个学生不能选修同样的课程。

create table t9(
     id int unsigned,
     course_id int unsigned comment '课程编号',
     score tinyint unsigned comment '课程得分',
     primary key(id,course_id));

可以看到id和course_id 都叫做主键。可是刚才不是说了一张表只有一个主键吗，这里我怎么看到有两个主键啊。

有两个PRI，但并不证明有两个主键！而是这两个都是主键，两个都是主键如何理解呢？它们两个合起来才是一个主键！

现在是1234这个同学选了编号40的课得到90分，这没什么问题

124这个同学也选40这个课程考了80分，插入也没有问题，可是我们course_id也是主键啊，但是故意插一样的mysql也没有拦我，只不过124和上一个不一样。允许不同同学选同一个课

再来一个，123这个同学又选了50课程也没有拦我，允许同一个同学选不同的课

但是我们不允许，同一个同学选同一个课！出现主键约束了。它是把id和course_id作为一个整体的。

换言之，可以选择一列作为主键，也可以选择多列作为主键，但是多个合起来做一个主键，都不一样可以插，有一个不一样可以插，只有多个同时和历史数据一样才会出现主键冲突。 这就是复合主键。

最后说一下，主键是用来衡量mysql表中一行一行记录的唯一性的。未来可以通过设定主键的方式对一行一行表中的记录进行唯一性甄别。后面可以支持通过主键进行快速查找。

6.自增长

auto_increment：对应的字段设置自增长，插入时不用管它，它会自动的被系统触发，系统会从该字段中已经有的最大值进行+1操作，得到一个新的不同的值。必须和主键搭配使用，作为逻辑主键。

自增长的特点:

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

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

Extral下面内容表示这一列是自增的

插入时，我们可以指定插入其他列，id这一列就不管了。可以看到虽然我并没有告诉id要插什么，但是id是自动帮我们插入的，并且是增长的。和别人不冲突并且连续的，这就是自增长主键。

当我们指定id要插入的时候，也能插进行。然后再插入id相同值得时候确实能够履行主键的职责发生主键冲突。

当把重复的值去掉之后也能插入，发现它是从1000开始自增，

自增主键在插入时没有设置任何默认值，默认是从1开始插入的。如果手动插入一个新的起始值，这个起始值比历史值都要大，它就会从新的起始值开始进行插入。

凭什么能做到这一点呢，以及mysql凭什么知道下一次从哪插入呢？
主要原因在于创建表的时候除了在表内设置auto_increment约束，在表外auto_increment的值是可以被设置的。代表下一次插入的起始值。

当对一个表内设置的auto_increment的时候，可以在表外设置auto_increment的起始值。而且刚才我们在插入1000的时候，就已经自动的把表外auto_incremen的值改成下一次起始值了

create table t11(
    id int unsigned primary key auto_increment,
    name varchar(20) not null
    )auto_increment=500;

在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

select last_insert_id();

索引：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

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

7.唯一键

下面写个案例先把唯一键的约束体现出来，然后回过头再看它的特点

create table student(
    id char(20) unique comment '这是一个学生的唯一键',
    name varchar(32) not null
    );

id字段添加唯一键，key这里出现的时UNI

我们发现id列插入相同的值，报唯一建冲突问题

id这一列插入null，是允许插入的。在插入null也还是允许的！不冲突！

主键一旦设置好，不能为空，不能重复，但唯一键表达了类似主键的功能。
唯一键设置好，可以为空，不能重复。

唯一键看起来像是主键的阉割版，不就是一个能为空，一个不能为空，但是都能保证唯一性，有什么区别呢？如何选择呢？

一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，你选择某一列作为主键，但其他列也是需要唯一性的，而唯一键就可以解决表中有多个字段需要唯一性约束的问题。

所以主键和唯一键并不冲突反而是相互补充的。

下面我们重新创建一个学生信息表，目前仅给id添加主键，没给其他列添加唯一键，当插入两个id值一样的不让插入，没什么问题，但是我插入不同的人但是具有相同值得电话竟然让我插入了，现实生活中有电话一样的吗？显然是没有的。从业务逻辑来讲，这属于数据插错了。

并且查的时候分不清楚这个电话号码到底是谁的

我们主键要保证唯一性是毫无疑问的，同时并不否认其他列也需要唯一性！所以理解唯一键不能光从技术角度，还要从业务角度理解。

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

关于唯一键和主键的区别：
我们可以简单理解成，主键通常用来标定某一行记录在整表中的唯一性。而唯一键的侧重点让自己插入的列值和该列不要冲突从而保证在业务上的字段唯一性不要出现冲突。

把表中电话字段添加唯一键 unique key 和 unique 都可以。现在电话在插入相同的值不让你插入。暂时不知道电话可以插入null

唯一键也可以手动设置为非空，该字段后面加not null约束。

主键保证表中记录唯一性，只能有一个。唯一键保证这一列不能出现重复，可以设置多个。

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。
而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。
具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。
一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

8.外键

外键更为强调的是两点：1. 表与表之间关联 ，2. 表与表之间约束。

之前学的都是一张表内某些字段做约束，但是mysql是关系型数据库表与表之间势必产生关联，所以除了要有表内的约束之外，还要再来一种表和表之间的约束关系。

假如有一张学生表，一张班级表。每个表都有自己内部约束和主键。但常识告诉我一个学生将来一定隶属于一个班级，你如果愿意当然可以在学生中增加更多信息把班级表中的属性放到学生表中搞成一张表，但是这一张表的信息会出现很多重复，一些学生可能同属于同一个班级。所以把学生表和班级表拆开，拆开之后表和表之间会产生某种关联，尤其是一个学生一定隶属于某个班级，所以给学生表里面一个列属性，只包含学生对应的班级id。因为班级有自己的主键，所以可以用班级表中特定的列属性充当所属班级情况，这样仅重复一列信息，更过其他班级信息就不会重复了。

像这种用一个特定的列属性和其他表产生某种关联，这个值一定意义上可以称为‘外键’了，但是并不完整，因为这里仅有关联关系。

我们一般把班级表称为主表，学生表称为从表，因为学生是依附班级的。通过班级id和班级表产生关联关系。

初识外键：

先建两张表，一张学生表，一张班级表，外键先不加。

先插入两个班级信息，再插入一个学生信息，想看这个学生属于那个班级，因为有class_id，所以可以查一下班级表在班级表查一下id，就知道这个学生属于哪一个班了

然后接着录入学生信息有属于不同的班级来了，这样当然也没有问题。

但是这个录入学生信息的老师，在录入田七的时候粗心了，在class_id这一列录入一个不存的班级。首先mysql这里也允许插入，学生和班级是两张表，你插你的我插我的。而在上层逻辑中是不会被允许存在一个学生的班级在班级表不存在的。所以mysql就要通过一定的技术手段归并这样并不符合逻辑的情况。

还有一种情况，有三个同学属于1班，现在不删这三名同学，现在直接计算机101班级删掉。我们发现mysql也允许我们删掉一个还有学生的班级。但这并不合理！

虽然可以正常插入，删除但是并不符合逻辑，那怎么解决呢？
第一种方法很简单，在插入删除的时候判断一下，这是我们自己要做的。但是变得很复杂。

刚才这两张表，我们可以说它是具有外键之名的，学生表是从表，班级表是主表，学生表确实有class_id与班级主键id产生关联。从某种意义上来讲这个class_id具有外键之名。但是外键必须要考虑两层关系：1. 表与表之间关联 ，2. 表与表之间约束。 这个class_id 只有外键之名（关联关系），但是没有外键之实（没有约束！）

学生的class_id 在班级id里不存在就不允许插入，逼着你必须插入合法的class_id。另外删除班级，必须是班级里没有学生了才可以删。必须有约束关系！

外键约束：

语法：

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

指明外键列并且将外键列和主表中特定列建立外键约束关系

当这次插入不存在的班级id，就发生了外键约束，不允许插入。

并且删一个还存在学生的班级也不能删，因为这个主键列还被从表关联着。也不能删。

这就是外键约束，可以保证表与表逻辑关系以及数据完整性

当把这个class_id=1的学生删除，这个班级id=1就可以删了。

最终总结一下：

外键： 1. 从表和主表的关联关系 ，2. 产生外键约束。外键的本质产生关联，增加约束来整体保证表和表之间的完整性。表和表之间有关系建立外键，没有关系不用建立。

外键通常用于定义主表和从表之间的关系：外键主要定义在从表上，要求主表必须是有主键约束或unique约束。当定义外键后，要求从表中外键列数据必须在主表的主键列存在或为null(针对于主表中你选择某列作为约束的是主键约束还是unique约束来说的)。

最后你可能看见别人外键前面会加上constraint，这里代表的是可以给这个外键约束起名字。如果不起也有默认的名字。可以不用管。

约束学完我们就可以成功建立一张或多张带有约束的表了。