【半夜学习MySQL】表的约束（含主键、唯一键、外键、zerofill、列描述、默认值、空属性详解）

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

---

前言

上一篇文章中介绍了数据类型，数据类型已经对字段做出一定的约束。但是数据类型的约束很单一，需要一些额外的约束，更好的保证数据的合法性，让我们未来插入数据库表中的数据是复合预期的，保证数据在业务逻辑角度的正确性。本文将介绍表的其他约束，如null/not null、default、comment、zerofill、primary key、auto_increment、unique key等

约束本质是通过技术手段，倒逼程序员，插入正确的数据，反过来，站在mysql的视角，凡是插入进来的数据，都是符合数据约束的！

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

空属性

特点介绍
空属性包含两个取值：null和not null，其中字段没有任何约束时，默认自动填null。

★ps：数据库字段在默认情况都填空，但是实际开发时，尽可能保证数据不为空，因为数据为空没办法参与运算。↓↓↓

案例：
创建一张班级表，包含班级名和班级所在教室号。要求班级名不可以为空，但班级所在教室可以为空（有可能某些班级的教室暂未被安排）

所以我们在设计数据表时，需要对班级名字段做出约束，不满足要求的数据将无法插入表中，也就是约束↓↓↓

create table class_info(
class_name varchar(10) not null,
class_loc varchar(12)
);
desc class_info;

insert into class_info values('软件1班', '理工一号楼429');
insert into class_info values('软件4班', null);
insert into class_info values(null, null);

★ps：由于class_name设置了not null约束，只要插入的数据该字段为null，mysql将拒绝插入。

★ps：NULL与""（空串）含义不同。

默认值

特点介绍
某些字段的值经常性的会出现某个具体的值，我们可以给该字段设置一个默认的值，在需要填写真实数据时，用户可以选择性的使用默认值。
案例：
程序员相亲网站需要一个用户信息表，包含用户姓名、性别和年龄，其中，性别默认值为’男’，年龄默认值为0。↓↓↓

create table user(
name varchar(32) not null,
gender varchar(2) default '男',
age tinyint unsigned default 0
);
desc user;

insert into user values('小明', '男', 18);
insert into user(name, gender) values('小雅', '女');
insert into user(name) values('小光');
select * from user;

★ps：在没有给字段指定数值时，会使用默认值填充。只有设置了default的列，才可以在插入数据的时候，对该列进行省略。

★ps：default和not null不冲突，而是互相补充的。当用户设置了默认值，且不能为null时，用户如果没有插入数据，则会使用用默认值；用户如果插入null，则会报错。

列描述

特点介绍
关键字为comment，列描述没有实际的含义，它专门用来描述字段，会保存在表创建语句中，主要用于程序员或DBA了解字段含义。
案例：
下面创建一张学生表，并对每个字段给出列描述↓↓↓

create table stu(
id int not null comment '学号',
name varchar(32) not null comment '姓名',
age tinyint unsigned default 0 comment '年龄',
gender char(2) default '男' comment '性别'
);
desc stu;

★ps：从上图可知，desc查看不到注释信息。如果想查看表的注释信息，需要通过show创建表的语句↓↓↓

show create table stu;
show create table stu \G

★ps：如果使用show查看时，格式比较杂乱，可以通过在结尾带上\G，对输出结果格式化。

zerofill

特点介绍
在之前的文章中介绍到，表中某些字段类型为int(11)，这个11是什么含义呢？首先我们创建一个包含int字段的表，并添加zerofill约束↓↓↓

create table TestZerofill(
num int(5) zerofill
);
desc TestZerofill;

此时对该表中插入数据↓↓↓

insert into TestZerofill values(33);
insert into TestZerofill values(888);
insert into TestZerofill values(123456789);

★ps：从上面结果可知，当字段设置为int(5)时，如果数字个数不足5个，则会在数字前补0，使得数字个数为5个；如果数字个数超过5个，则按实际数据显式。

下面我们对表格中的数据进行查询↓↓↓

select * from TestZerofill where num=33;
select * from TestZerofill where num=888;
select * from TestZerofill where num=123456789;

★ps：zerofill只约束了字段显式时的格式，并不影响数据本身，我们仍可以使用原先插入的数据进行查询操作。

主键

主键概述

特点介绍
primary key用来唯一标识表中的数据，被设置为主键的列，不能重复，不能为空。一张表中最多只能有一个主键；主键所在列通常是整数类型。
案例：
创建某个幼儿园的小朋友的信息表，将学生id设置为主键，名字字段不能为空↓↓↓

create table child_info(
id int unsigned primary key comment '学号',
name varchar(32) not null comment '姓名'
);
desc child_info;

下面我们对该表插入数据↓↓↓

insert into child_info values(1,'a');
insert into child_info values(1,'b');

★ps：主键对应的字段不能出现重复值，一旦重复，mysql将拒绝插入。

主键删除与追加

删除主键

alter table 表名 drop primary key;

示例：

desc child_info;
alter table child_info drop primary key;
desc child_info;

追加主键
当表创建以后但是没有没有主键的时候，可以再次追加主键

desc child_info;
alter table child_info add primary key(id);
desc child_info;

复合主键

在创建表的时候，可以在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，则称为复合主键。例如：公寓号+寝室号+床号才能确定一个唯一的一个学生，则公寓号、寝室号、床号一同组成了复合主键。
案例：
创建一张寝室信息表↓↓↓

create table hotel(
loc_id char(2) not null comment '公寓号',
room_id char(3) not null comment '宿舍号',
bed_id char(1) not null comment '床号',
name varchar(32) comment '学生姓名',
primary key(loc_id, room_id, bed_id)
);
desc hotel;

★ps：上图中loc_id、room_id、bed_id字段被标为主键，并不表示这三个字段分别是主键，而是这三个字段一同构成了一个主键。

下面尝试插入数据↓↓↓

insert into hotel values('12', '321', '8', '小明');
insert into hotel values('12', '321', '2', '小张');
insert into hotel values('02', '321', '2', '小光');
insert into hotel values('02', '321', '2', '小乐');

★ps：只有公寓号、宿舍号、床号组成的主键完全相同时，才会触发主键的唯一性约束。

自增长

特点介绍
当某个字段被设置为auto_increment，插入数据时，该字段如果不直接给值，系统会自动将该字段设置为自增默认值+1（每添加一次数据，自增默认值将自增1）。通常和主键搭配使用，作为逻辑主键。

自增长的特点：
● 任何一个字段要做自增长，前提是该字段本身是一个索引（desc 表名时，对应key一栏有值，表示该列有索引）；
●自增长字段类型必须是整数；
●一张表最多只能有一个自增长。
案例：
创建一张会员表，会员编号每次自增1↓↓↓

create table vip(
id int unsigned primary key auto_increment,
name varchar(32) not null
);
desc vip;

对该表插入数据↓↓↓

insert into vip(name) values('小明');
insert into vip(name) values('小光');
insert into vip(name) values('小翔');
select * from vip;

在插入后，可以通过select last_insert_id();获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

如果在vip表插入id为1000的会员，后序插入的需要，将在该基础上自增↓↓↓

insert into vip(id, name) values(1000, 'xiao ming');
insert into vip(name) values('xiao guang');
insert into vip(name) values('xiao xiang');
select * from vip;

★ps：关于索引：
●在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
●索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
●索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
●数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
关于索引的更多细节，将于后序文章介绍

唯一键

特点介绍：
一张表中往往有很多字段需要唯一性约束，即某个字段的数据不能重复。但是一张表中只能有一个主键，此时，唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键和主键差不多，但唯一键允许字段中的数据为空，且可以是多个数据为空，空字段将不参与唯一性比较。

★ps：主键和唯一键的区别：主键更多的是标识数据的唯一性。而唯一键更多的是保证在业务上，不要和别的信息出现区别。关于它们的区别，可以在下述案例中体会出来↓↓↓

案例：
创建一张员工信息表，包含员工的员工id（设为主键），员工姓名（不能为空），员工电话号码（允许为空，但必须唯一，即设置为唯一键）↓↓↓

create table woker(
id int unsigned primary key comment '员工号',
name varchar(32) not null comment '姓名',
phone char(11) unique comment '电话'
);
desc woker;

此时向该表插入数据↓↓↓

insert into woker values(1000, '小明', 18800444111);
insert into woker values(1001, '小光', 18800444222);
insert into woker values(1002, '小翔', null);
insert into woker values(1003, '小天', null);
insert into woker values(1004, '小仔', 18800444222);

★ps：从上图可知，设为唯一键的字段无法插入相同的数据，但可以插入多个值为null的数据。

外键

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

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

案例：
如果此时有一张学生表（包含学生id、姓名、性别、班级id），一张班级表（包含班级id、班级名称、班级教室）。需要使得这两个表产生关联，因为学生所在的班级的id必须存在于班级表中。一个学生不能归属于一个不存在的班级id。

下面先创建班级表↓↓↓

create table class(
class_id int unsigned primary key,
class_name varchar(12) not null,
class_loc varchar(12) unique
);
desc class;

此时在班级表中插入数据↓↓↓

insert into class values(1001, '软件1班', '理工一429');
insert into class values(1002, '软件2班', '理工一431');
insert into class values(1003, '计科1班', '科学楼321');
select * from class;

创建学生表，并使班级id为class表class_id的外键↓↓↓

create table student(
id int unsigned primary key,
name varchar(32) not null,
gender char(2) not null,
class_id int unsigned,
foreign key (class_id) references class(class_id)
);
desc student;

此时向学生表中插入数据↓↓↓

insert into student values(20001, '小明', '男', 1001);
insert into student values(20002, '小光', '男', null);
insert into student values(20003, '小项', '女', 4321);

★ps：外键可以是class表中class_id字段的任意值，也可以为空。但是不能是class_id以外的值。

综合案例

jammingrpo开了一家无人自助超市，配套的软件需要三张表，要求如下：
●商品表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
字段解释 =>	订单号	客户号	商品号	购买数量

要求如下：
●每个表都设置合适的主键及外键
●客户的名字不能为空值
●邮箱不能重复
●客户的名别只能是男、女或保密

下面创建的表是商品表↓↓↓

create table if not exists goods(
goods_id int unsigned primary key auto_increment comment '商品编号',
goods_name varchar(36) not null comment 
'商品名称',
unitprice decimal not null default 0 comment '单价',
caregory varchar(12) comment '商品分类',
privider varchar(12) comment '供应商'
);
desc goods;

下面创建的是客户表↓↓↓

create table if not exists customer(
customer_id int unsigned primary key auto_increment comment '客户编号',
name varchar(32) not null comment '客户姓名',
address varchar(256) comment '客户地址',
email varchar(64) unique key comment '电子邮箱',
gender enum('男', '女', '保密') not null comment '性别',
char_id char(18) unique key comment '身份证号'
);
desc customer;

最后创建的是购买表↓↓↓

create table if not exists purchase
(
order_id int unsigned primary key auto_increment comment '订单号',
customer_id int unsigned comment '客户编号',
goods_id int unsigned comment '商品编号',
nums int unsigned default 0 comment '购买数量',
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id)
);
desc purchase;

大家可自行插入数据进行测试

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d