MySQL表的约束

---

前言

---

一、表的约束

前面我们在学习数据库知识时，知道了当我们向数据库中插入数据时，如果数据不合法，那么MySQL不会允许数据插入成功，这也算是MySQL对数据合法性的一种约束。但是仅仅靠这一种约束是不够的。虽然真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

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

空属性 not null

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

• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。
  我们需要分清楚在MySQL中NULL表示什么都没有，并且NULL不参与运算。而’ '表示有，但是为空字符串。

下面我们创建一个表，并且为name和room选项添加not null的约束。

然后我们向表中插入数据。我们看到name和room选项不能为NULL了。即添加了not null约束的选项在插入数据时，给定的值就不能为NULL了。而如果我们不添加not null约束，那么默认这个选项就是可以为NULL的。

默认值 default

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

我们看到当向表中插入数据时，对于有默认值约束的选项，如果我们给出了这个选项的值，那么数据库会按照我们给定的值来存储，如果我们没有给定值，那么数据库会存储这个选项的默认值。

我们在创建表时，将name选项设置为not null约束，然后我们给name选项插入的值为NULL时，显示name选项的值不能为null。而当我们直接不给name选项值时，显示的错误是name选项没有默认值。

下面我们将gender选项设为not null约束和默认值约束。

然后我们插入数据给gender选项插入为NULL时，发现插入不成功，因为gender选项有一个not null约束，所以不能为NULL。但是当我们不给gender选项值时，发现可以插入成功数据，因为我们给gender选项设置了默认值，所以当不给gender数据时，数据库会存储默认值。所以我们需要知道，default和not null两个约束不是冲突的，而是互补的。

我们在创建表时给age选项设置了默认值，而因为我们没有给age选项设置not null约束。所以age选项是可以插入NULL的。

下面我们创建一个新表，并且没有给name选项添加约束。我们看到数据库会给name选项自动设置默认值为NULL。而当我们给name选项设置not null后，那么数据库就不会为选项自动添加NULL默认值了。

列描述 comment

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。
我们看到comment不会影响插入等操作，它只会提醒要插入数据的程序员，这个选项应该插入什么样的数据，而不是硬性的约束。

zerofill

zerofill约束会在整数的宽度小于设定的宽度时自动填充0。
我们看到当创建表时，int类型的整数后面会有个10。这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。

下面我们给a选项添加上zerofill约束，然后我们看到a选项的值在显示时，会在前面补0。这就是zerofill约束的作用。

下面我们将b选项设为int(4)，然后给b加上zerofill约束，我们看到当插入的整数的位数不够4位时，会进行补0，而如果整数位数大于4位那么久不会补0了。所以zerofill约束主要做格式化显示的。

虽然显示时是这样显示，但是MYSQL在存储时并没有存储这些补的0。我们可以通过hex函数将结果变为十六进制来验证。

我们看到无符号整数的zerofill属性默认为10。而有符号整型的zerofill默认为11。这是因为一个int类型为4字节，可以存的最大无符号数为42亿多，只需要10位即可显示。而存的最大有符号数为21亿多，加上符号的话只需要11位即可显示。

主键

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

我们可以在创建表的时候直接在字段上指定主键。

我们看到主键约束会使添加了主键的字段中的数据不能重复，一旦重复，那么就会插入数据失败。

我们可以通过主键来查询某一条具体数据，并且对这个数据做修改。

我们可以通过下面的命令来删除主键。当将一个字段的主键约束删除后，那么这个字段的数据重复的话，也可以成功插入到数据库了。

alter table 表名 drop primary key;

当我们将表创建好以后但是没有主键的时候，可以再次追加主键。我们看到第一次设置主键失败，这是因为此时表中id为2的数据有两条，只有我们先消除id冲突，然后才可以将id字段设为主键。

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

复合主键
下面我们来介绍一种复合主键。在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。
可以看到下面的表中我们将id字段和course字段合在一起作为复合主键。这种情况就可以防止有id+course组合的内容重复出现。

我们看到当有复合主键时，向表中插入数据。只有当作为主键的两个字段的数据都一样时，数据才不允许被插入。而当只有一个字段的数据相同时，数据就可以被插入。

自增长约束

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

自增长的特点:

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

下面我们创建一个表，并且将id字段设为主键，再增加自增长约束。

我们看到在向表中插入数据时，并没有给定id字段的数据，但是数据库会自动将id从1开始自增。

当我们重新插入一条数据，并且这个数据指定了id字段的值为1000后。下面再插入数据时就会从1001开始插入数据了。

我们还可以在创建表时设置自增的起始值。然后我们就可以看到会从500开始自增了，而不是从1开始自增。

我们还可以通过下面的语句来查看当前表中数据的最后一个id。

select last_insert_id();

唯一键约束

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

我们看到当向设置了unique约束的字段添加数据时，如果添加为NULL并不会冲突，而如果添加的数据一样了，则会因为数据冲突而添加失败。主键不能为NULL，但是唯一键可以为NULL。因为NULL不参与运算，所以不会判断为两个NULL相等。唯一键也可以设置为not NULL。

外键约束

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

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

我们先创建主键表。

然后我们再创建从表。

我们看到此时直接向stu表中插入数据会出错，因为在myclass表中还没有添加班级1。所以会插入数据失败。

当我们在myclass表中插入数据。然后再向stu表中插入数据时，如果有对应的班级那么就会添加成功。

并且因为此时stu表中还有一个学生的班级为1，所以此时删除myclass表中的id为1的班级也会出错。

而只有当我们将stu表中的班级为1的学生删除时，然后才能成功删除myclass表中的班级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)

要求：

• 每个表的主外键
• 客户的姓名不能为空值
• 邮箱不能重复
• 客户的性别(男，女)

创建goods表。

创建customer表。

创建purchase表。