命令行操作MySQL - 列的完整性约束

这是命令行操作MySQL数据库系列博客的第十篇，今天这篇博客记录《列的完整性约束》的用法。（主键、外键、非空、唯一、自增长和默认值约束）

---

简介

完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不再执行用户的操作。MySQL中基本的完整性约束条件如表所示。下表是完整性约束条件:

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系的某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值自动增加，这是MySQL语句的特色
DEFAULT	为该属性设置默认值

---

主键约束（PRIMARY KEY，PK）

主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。比如身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

一、单字段主键

即该张表中单个字段使用了主键约束

例：

1. 创建表

   create table 表名(字段1 类型 PRIMARY KEY, ...);
create table 表名(字段1 类型, ..., PRIMARY KEY(字段1));

   两种方式创建效果一致

   create table class1(id int PRIMARY KEY, name varchar(32), sex enum('男', '女', '未知'));
   # 两种方式创建效果一致
   create table class2(id int, name varchar(32), sex enum('男', '女', '未知'), PRIMARY KEY(id));
   

   

   可以使用desc 或者使用 show 方式验证
   

2. 插入数据

   insert into class1 values(1, '老王', '男');
   # 插入失败，因为主键约束，必须是唯一值
   insert into class1 values(1, '刘女士', '女');
   

   

另外，Mysql 支持给主键设置名字

create table class3(id int, name varchar(32) CONSTRAINT pk_id PRIMARY KEY(id));

忘记定义了有啥用了，貌似是区分作用吧。。。

二、多字段主键

主键是由多个属性组合而成时，在属性定义完之后统一设置主键。

例：

1. 创建表
   create table 表名(字段1 类型, 字段2 类型, ..., PRIMARY KEY(字段1, 字段2));

    create table class4(id int, name varchar(32), sex enum('男', '女', '未知'), PRIMARY KEY(id, name));
   

   将表中的id和name字段设置为主键约束
   可以使用desc进行验证
   

2. 插入数据

   insert into class4 values(1, '老王', '男');
   insert into class4 values(1, '老李', '男');
   insert into class4 values(2, '老李', '男');
   

   主要两个键不同时出现重复，都可以插入
   

3. 插入失败的情况

   insert into class4 values(2, '老李', '男');
   

   当再次插入一条重复的数据时，将无法成功插入
   

---

外键约束（FOREIGN KEY，FK）

外键是表的一个特殊字段，外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。

设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。

在具体设置外键约束时，设置外键约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为空（NULL）。

父表：
create table class(id int PRIMARY KEY, name varchar(32), teacher varchar(32));

创建外键有两种方式：

以下创建外键均已上表为参照

1. 定义字段时标明外键（不推荐使用）
   create table 表名(字段1 类型 PRIMARY KEY, 字段2 类型, 字段3 类型 REFERENCES class(id), 字段4 类型);
   这里定义字段3为外键，参照class表中的id主键字段

   create table student1(id int PRIMARY KEY, name varchar(32), class_id int REFERENCES class(id), sex enum('男', '女'));
   

   

2. 定义完字段后再标明外键（推荐使用）
   create table 表名(字段1 类型 PRIMARY KEY, 字段2 类型, 字段3 类型, 字段4 类型, FOREIGN KEY(字段3) REFERENCES class(id));
   这里定义字段3为外键，参照class表中的id主键字段

   create table student2(id int PRIMARY KEY, name varchar(32), class_id int, sex enum('男', '女'), FOREIGN KEY(class_id) REFERENCES class(id));
   

   

3. 插入数据

   “父表”插入数据
   

   “子表”插入数据

   insert into student2 values(1, '王同学', 3, '男');
   insert into student2 values(2, '李同学', 1, '男');
   insert into student2 values(3, '刘同学', 2, '女');
   

   

4. 插入外键允许为空

   insert into student2(id, name, sex) values(4, '杨同学', '男');
   

   

desc查看结构

建议使用第二种方式定义外键，不要问什么，问就是小编也不知道怎么回事就是定义不成功，目前还没找出问题所在，所以就先使用第二种方式定义外键保险！

---

非空约束（NOT NULL, NK）

当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用非空约束进行设置。非空约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。

设置表中某字段的非空约束非常简单，其创建语法如下：
create table 表名(字段1 类型 NOT NULL, 字段2 类型 NOT NULL);

例：

1. 创建表

   create table class5(id int PRIMARY KEY, name varchar(32) NOT NULL);
   

   

2. 插入数据

   insert into class5 values(1, '一班');
   
   insert into class5(id) values(2); # 无法插入空值
   

   

检验class5表中字段name是否被设置为非空约束，执行SQL语句DESCRIBE，具体如下:（describe简写desc）

主键自带非空，name被设置为非空约束了。

---

唯一约束（UNIQUE，UK）

当数据库表中某个字段上的内容不允许重复时，可以使用唯一约束进行设置。唯一约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。

其创建语法如下：
create table 表名(字段1 类型 PRIMARY KEY, 字段2 类型 UNIQUE);

例：

1. 创建表

   create table class6(id int PRIMARY KEY, name varchar(32) UNIQUE);
   

   

2. 插入数据

   insert into class6 values(1, '一班');
   
   insert into class6 values(2, '一班'); # 错误，不允许插入错误的值
   

   

可以使用desc查看结构

注意： unique 约束允许受约束的字段出现多个空值 NULL, 不算重复

---

自动增加约束（AUTO_INCREMENT）

AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。

其创建语法如下：
create table 表名(字段1 类型 AUTO_INCREMENT PRIMARY KEY, 字段2 类型);

例：

1. 创建表

   create table class7(id int AUTO_INCREMENT PRIMARY KEY, name varchar(32));
   

   

2. 插入数据

   insert into class7(name) values('一班');
   insert into class7(name) values('二班');
   

   不指定内容插入时，默认从1开始，如果里面已经有数据，那么就会在最大的数后面接着插入。每增加一条记录，记录中该字段的值就会在前一条记录(或已存在的最大值（包括曾经存在的）)的基础上加1。
   

也可以使用desc查看一下结构

注意: mysql8 中,AUTO_INCREMENT 必须设为键(主键、外键和唯一键均可)！ 否则会报错！

---

默认值约束（DEFAULT）

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字DEFAULT来设置。

其创建语法如下：
create table 表名(字段1 类型, 字段2 类型 DEFUALT 默认值);

例：

1. 创建表

    create table class8(id int PRIMARY KEY, name varchar(32) DEFAULT '十班');
   

   

2. 插入数据

   insert into class8 values(1, '一班');
   insert into class8(id) values(2);
   

   

注意： 如果没有设置，则默认为空

---

总结:

花了一天总结出来的，这其中自己也敲了几遍，对里面的约束有了更多深刻的理解，如果文中有错误的地方，欢迎评论纠正。（当然，此篇博客参考腾讯课堂骑牛学院vip课程的讲课文档）