【MySQL】第六弹---数据库表约束详解：从空属性到主键的全方位指南

✨个人主页： 熬夜学编程的小林

💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

目录

1. 表的约束

1.1 空属性

1.2 默认值

1.3 列描述

1.4 zerofill

1.5 主键

---

1. 表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。
表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary
key，auto_increment，unique key 。

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

• 约束的最终目标：保证数据的完整性和可预期性。

1.1 空属性

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

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)mysql> select null + 1;
+----------+
| null + 1 |
+----------+
|     NULL |
+----------+
1 row in set (0.02 sec)

案例：

mysql> create table t1(id int,name varchar(20) not null); # 创建表，name不能为空
Query OK, 0 rows affected (0.03 sec)mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t1 values(1,'张三'); # 正常插入数据，插入成功
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (name) values('李四'); # 单独插入name，插入成功
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 (id) values(2); # 单独插入id，插入失败，没有默认值
ERROR 1364 (HY000): Field 'name' doesn't have a default valuemysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | 李四   |
+------+--------+
2 rows in set (0.00 sec)

1.2 默认值

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

案例一：

创建表

mysql> create table t2 (id int not null,-> gender varchar(2) default '男',-> age tinyint unsigned default 18-> );
Query OK, 0 rows affected (0.03 sec)mysql> desc t2;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(11)             | NO   |     | NULL    |       |
| gender | varchar(2)          | YES  |     | 男      |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据

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

mysql> insert into t2 values(1,'男',15); # 正常插入数据，使用用户输入的数据
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 (id,gender) values(2,'男'); # age用户没有输入数据，使用默认值
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 (id,gender) values(3,'女');
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 (id) values(4); # age和gender用户没有输入数据，均使用默认值
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+----+--------+------+
| id | gender | age  |
+----+--------+------+
|  1 | 男     |   15 |
|  2 | 男     |   18 |
|  3 | 女     |   18 |
|  4 | 男     |   18 |
+----+--------+------+
4 rows in set (0.00 sec)

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

案例二：既有not null又有default

创建表

# 创建表，age既有not null又有default
mysql> create table t3 (id int not null,age tinyint unsigned not null default 18);
Query OK, 0 rows affected (0.03 sec)mysql> desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | NO   |     | 18      |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t3 values(1,20);
Query OK, 1 row affected (0.01 sec)

插入数据

mysql> insert into t3 values(1,20); # 正常插入，使用用户输入的数据
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (id) values(2); # 只插入id值，age使用默认值
Query OK, 1 row affected (0.00 sec)mysql> insert into t3  values(3,NULL); # name插入NULL，插入失败
ERROR 1048 (23000): Column 'age' cannot be null
mysql> select * from t3;
+----+-----+
| id | age |
+----+-----+
|  1 |  20 |
|  2 |  18 |
+----+-----+
2 rows in set (0.00 sec)

• 如果我们没有明确指定一列要插入，用的是default，如果建表中，对应列默认没有设置default值，无法直接插入。
• default 和 not null 不冲突，而是互相补充的。

not null : 当用户想插入的时候 -> 1. NULL  2. 合法数据

default : 当用户忽略这一列的使用 -> 1. 使用默认值(如果设置类)，2. 如果没有设置直接报错

1.3 列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA(Database Administrator -> 数据库管理员)来进行了解。

案例：

创建表

mysql> create table t4 (id int comment '编号',-> name varchar(20) comment  '姓名',-> age tinyint unsigned comment '年龄'-> );
Query OK, 0 rows affected (0.06 sec)mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| name  | varchar(20)         | YES  |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空 

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

查看注释信息

mysql> show create table t4 \G;
*************************** 1. row ***************************Table: t4
Create Table: CREATE TABLE `t4` (`id` int(11) DEFAULT NULL COMMENT '编号',`name` varchar(20) DEFAULT NULL COMMENT '姓名',`age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)ERROR: 
No query specified

1.4 zerofill

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

mysql> create table t5(a int unsigned ,b tinyint);
Query OK, 0 rows affected (0.04 sec)
mysql> show create table t5 \G;
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (`a` int(10) unsigned DEFAULT NULL,`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

mysql> insert into t5 values(1,2);
Query OK, 1 row affected (0.01 sec)mysql> select * from t5;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

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

mysql> alter table t5 modify a int(5) unsigned zerofill;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show create table t5 \G;
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE `t5` (`a` int(5) unsigned zerofill DEFAULT NULL,`b` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

对a列添加了zerofill属性，再进行查找，返回如下结果：

mysql> select * from t5;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
+-------+------+
1 row in set (0.00 sec)

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

mysql> select a,hex(a) from t5;
+-------+--------+
| a     | hex(a) |
+-------+--------+
| 00001 | 1      |
+-------+--------+
1 row in set (0.00 sec)

• hex()为转换为16进制数的函数。

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

1.5 主键

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

案例：
创建表

# 学号为主键，name不能为NULL
mysql> create table t6 (id int comment '学号' primary key,name varchar(20) not null );
Query OK, 0 rows affected (0.03 sec)mysql> desc t6; # PRI为主键
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据

mysql> insert into t6 values(1,'张三');
Query OK, 1 row affected (0.01 sec)mysql> insert into t6 values(1,'李四'); # 不可以插入相同学号的数据
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into t6 values(2,'李四');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values(3,'李四'); # 可以插入相同name的数据
Query OK, 1 row affected (0.01 sec)mysql> select * from t6;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 李四   |
+----+--------+
3 rows in set (0.00 sec)

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

按照 id 查询数据

mysql> select * from t6 where id = 1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)mysql> select * from t6 where id = 2;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
+----+--------+
1 row in set (0.00 sec)

对主键列查询能查询到唯一的一条结果。

• 当表创建好以后但是没有主键的时候，可以再次追加主键(前提该列数据没有重复)。

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

创建表 

mysql> create table t7 (id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据 

mysql> insert into t7 values(1,'张三');
Query OK, 1 row affected (0.01 sec)mysql> insert into t7 values(2,'李四');
Query OK, 1 row affected (0.01 sec)mysql> insert into t7 values(2,'王五');
Query OK, 1 row affected (0.01 sec)mysql> select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    2 | 王五   |
+------+--------+
3 rows in set (0.00 sec)

添加主键 

mysql> alter table t7 add primary key(id);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

给 id 添加主键，但是 id 有重复数据，不能添加主键，因此需要删除重复的内容。

删除name = 王五的数据 

语法：

delete from 表名 where 条件;

删除重复的数据 

mysql> delete from t7 where name = '王五';
Query OK, 1 row affected (0.01 sec)mysql> select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

添加主键

mysql> alter table t7 add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 删除主键

alter table 表名 drop primary key;

因为一个表只能有一个主键，因此直接删除主键即可。

mysql> alter table t7 drop primary key;
Query OK, 2 rows affected (0.15 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> desc t7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 复合主键

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

创建复合主键表

mysql> create table t8 (id int unsigned,-> course int unsigned comment '课程编号',-> score int unsigned comment '分数',-> primary key(id,course));
Query OK, 0 rows affected (0.05 sec)mysql> desc t8;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id     | int(10) unsigned | NO   | PRI | NULL    |       |
| course | int(10) unsigned | NO   | PRI | NULL    |       |
| score  | int(10) unsigned | YES  |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据

mysql> insert into t8 values(1,123,60);
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values(1,124,75);
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values(2,124,80);
Query OK, 1 row affected (0.01 sec)mysql> insert into t8 values(2,124,90);# 复合主键内容都相同则报错
ERROR 1062 (23000): Duplicate entry '2-124' for key 'PRIMARY'

当复合主键的那几列数值均相同才报错，凡是一列不相同均能插入成功。