概述

约束的分类

关于AOTO_INCREMENT 8.0新特性

概述

使用约束就是为了保证数据的完整性。

数据完整性(Data Integrity)：

数据的精确性和可靠性保证了数据的完整性。数据完整性防止数据库中存在不符合语义规范的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

所以我们为了保证数据完整性，可以通过约束对表数据进行额外的条件限制。

数据完整性一般通过如下四方面考虑：

1.实体完整性:例如：同一张表中不能存在两条完全相同的记录(行)。

2.域完整性:例如：年龄范围0-120，性别范围：男/女

3.引用完整性：例如：在员工表中，员工所在部门，要在部门表中存在。

4.用户自定义完整性：例如：用户名唯一，密码不为空等。

约束的分类

角度一：根据约束的字段的个数分为：

单列约束 | 多列约束

角度二：根据约束的作用范围分为：

列级约束：将此约束声明在对应字段后面。

表级约束：在表中所有字段都声明完后，在所有字段的后面声明的约束。

角度三：根据约束的作用（功能）分为：

1.not null （非空约束）

2.unique （唯一性约束）

3.promary key（主键约束）

4.foreign key（外键约束）

5.check（检查约束）

6.default（默认值约束）

如下根据约束的功能进行使用说明。

非空约束

关键字：NOT NULL

特点：

1.声明字段时，字段默认为NULL，所有类型的值都可以时NULL。

2.非空约束只能出现在表对象的列上，只能某列单独设置非空。

3.一个表可以声明多个字段为非空NOT NULL。

4.空字符串不等于NULL，0也不等于NULL。

非空约束的作用

当字段声明了NOT NULL(非空)时，向其字段添加NULL时，就会报错。

#非空约束
CREATE TABLE t_null(
id INT NOT NULL,
`name` VARCHAR(25)
)

INSERT INTO t_null VALUES(NULL,NULL)
#报错:Column 'id' cannot be null

声明约束方式

格式：

CREATE TABLE 表名(
字段名字段类型 NOT NULL
)

CREATE TABLE t_null(
id INT NOT NULL,
`name` VARCHAR(25)
)

查看表结构（NULL列）

添加表约束

如果在表创建之后想要添加该约束，可以alter添加约束：

添加非空约束前

添加约束

ALTER TABLE t_null 
MODIFY id INT NOT NULL;

添加非空约束后

删除表约束

ALTER TABLE t_null 
MODIFY id INT ;

唯一性约束

用来限制某个字段/某列的值不能重复。

关键字：UNIQUE

特点：

1.同一张表可以有多个约束。

2.唯一性约束可以是某一列的值唯一，也可以是多个列组合的值唯一。

3.唯一性约束允许列值为空。

4.在城建唯一约束的时候，如果不给唯一约束命名，默认和列名相同。

5.MySQL会给唯一约束的列上默认创建一个唯一索引。

唯一性约束的作用

当声明了UNIQUE（唯一），字段添加出现重复值时，会报错。

CREATE TABLE t_uni(
id INT UNIQUE
)

INSERT INTO t_uni VALUES(1)#通过
INSERT INTO t_uni VALUES(2)#通过
INSERT INTO t_uni VALUES(1)#拒绝
# 报错:Duplicate entry '1' for key 't_uni.id'

声明约束

CREATE TABLE t_uni(
id INT UNIQUE
)

添加表字段为唯一性约束时，可以添加NULL。例如：

INSERT INTO t_uni VALUES(1);
INSERT INTO t_uni VALUES(NULL);
SELECT * FROM t_uni;

添加表约束

在已经创建了表的情况下，添加UNIQUE的两种方式：

---创建表---
CREATE TABLE t_uni(
id INT ,
`name` VARCHAR(25)
)

---方式1---
ALTER TABLE t_uni 
ADD UNIQUE KEY(id);

---方式2---
ALTER TABLE t_uni
MODIFY id INT UNIQUE;

复合的唯一性约束

声明复合唯一性约束，两个字段下都不允许存在重复数据。

CREATE TABLE t_unis(
id INT,
username VARCHAR(25),
`password` VARCHAR(25),
UNIQUE KEY(username,`password`)
)

DESC t_unis;

INSERT INTO t_unis VALUES(1,aa,123);
INSERT INTO t_unis VALUES(2,aa,124);#报错:Unknown column 'aa' in 'field list'
INSERT INTO t_unis VALUES(2,bb,123);#报错:Unknown column 'bb' in 'field list'

删除约束

说明：

1.添加唯一性约束的列上会自动创建唯一索引。

2.删除唯一约束只能通过删除唯一索引名的方式删除。

3.删除时需要指定唯一索引名，声明时未指定唯一索引名时：如果是单列，就默认和列名相同，如果是组合列，就默认和声明时第一个列名相同。

可以通过SHOW INDEX FROM 表名; —查看索引名，例如：

SHOW INDEX FROM t_unis;

删除约束操作：

---删除唯一性约束---
ALTER TABLE t_unis
DROP INDEX username;

SHOW INDEX FROM t_unis;

主键约束

用来唯一标识表中的一行记录。

关键字：PRIMARY KEY

特点：

1.主键约束要求唯一且非空，相当于非空约束 + 唯一性约束。

2.一个表中最多只能有一个主键约束。

3.MySQL的主键名总是PRIMARY，就算是自己声明了也没用。

4.声明主键约束时，会自动创建主键索引，删除约束，索引及消失。

主键约束的作用

当在字段上声明了主键约束，该字段添加数据时，不能为空(NOT NULL)，不能重复(UNIQUE)。举例如下:

主键约束的声明

CREATE TABLE t_pri(
id INT PRIMARY KEY,
`name` VARCHAR(25)
)

INSERT INTO t_pri VALUES(1,'Tom');#通过
INSERT INTO t_pri VALUES(NULL,'Tom');#报错Column 'id' cannot be null
INSERT INTO t_pri VALUES(1,'Jerry');#报错:Duplicate entry '1' for key 't_pri.PRIMARY'


---查看表结构---
DESC t_pri;

添加约束

在已经创建表的情况下，添加主键约束。

CREATE TABLE t_pri(
id INT ,
`name` VARCHAR(25)
)

---添加主键约束---
ALTER TABLE t_pri
ADD PRIMARY KEY(id)

删除约束

注意：删除主键约束时，不需要指定主键名，因为主键在表中只允许存在一个，删除主键约束后，非空约束还存在，唯一性不存在。

ALTER TABLE t_pri DROP PRIMARY KEY;

---删除后查看表结构---
DESC t_pri;

删除主键约束后发现非空约束还存在。

自增列 INTO_INCREMENT

使声明的字段值自增

关键字： INTO_INCREMENT

特点：

1.一个表中最多只能有一个自增列。

2.当字段需要产生顺序增长时，可以设置自增长。

3.声明的自增长列字段要求具有唯一性。

4.自增约束的列数据类型要求是整数类型。

自增列声明

当字段声明自增长时，添加记录，自增字段值不需要指定，会自动赋值。

一般和主键约束一起使用。

声明自增约束

CREATE TABLE t_incre(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(25)
)

INSERT INTO t_incre(`name`) VALUES('Tom');
INSERT INTO t_incre(`name`) VALUES('Jerry');

---查看结果---
SELECT * FROM t_incre;

自增约束添加和删除

CREATE TABLE t_incre1(
id INT PRIMARY KEY ,
`name` VARCHAR(25)
)

---添加自增约束---
ALTER TABLE t_incre1
MODIFY id INT AUTO_INCREMENT;

---移除自增约束---
ALTER TABLE t_incre1
MODIFY id INT;

关于AOTO_INCREMENT 8.0新特性

自增计数特性：当添加3个记录，自增显示为:1,2,3 当把第三个删掉，再添加一条记录，自增显示为4，此时显示：1,2,4。

说明：由于两版本的机制不同，MySQL5.7中，只要重启服务，计数就会回归正常，也就是说:当重启服务后，在添加数据时，计数会重上一个值开始按顺序累加。

例如：在上方特性中，当添加了3条记录，删掉第三个，重启服务，再此时添加一条记录，此时就会显示：1,2,3

但是MySQL8.0中，给自增主键添加了持久化操作，则重启服务后，计数还是和没重启前一致，存在裂缝问题。

例如：在上方特性中，当添加了3条记录，删掉第三个，重启服务，再此时添加一条记录，此时还是会显示：1,2,4 （裂缝问题存在）

外键约束

外键约束促进了表的数据完整性，因为声明了外键约束的字段，当引用别的表的某个字段时，引用的表的字段对应数据需要存在。

关键字：FOREIGE KEY

主表和从表：

主表(父表)：被引用的表，被参考的表。

从表(子表)：引用别人的表，参考别人的表。

特点：

1.从表外键列引用主表的列必须声明为主键约束或唯一性约束。因为参考值需是唯一的。

2.在创建一个外键约束时，如果不给外键约束起名，则会自动产生一个外键名。

3.需要建表时指定外键约束，先创建主表，再创建从表。

4.有外键约束，删除表时，先删除从表，再删除主表。

5.从主表中的字段数据被从表参考时，主表该数据不允删除，如需删除，先解决从表依赖。

6.一个表可以创建多个外键约束。

7.从表与主表对应的列，列名可以不相同，但是数据类型必须相同。

8.当创建外键约束时，系统默认会在所在列上创对应的建普通索引，索引名为列名。

9.删除外键约束后，需要手动删除对应索引。

外键约束的声明

#创建主表
CREATE TABLE t_group(
group_id INT PRIMARY KEY ,
group_name VARCHAR(25),
group_desc VARCHAR(255)
)


#创建从表
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(25),
group_id INT ,

#创建表级约束-指定约束名
CONSTRAINT fk_user_group_id FOREIGN KEY (group_id) 
REFERENCES t_group(group_id)
)

对于外键约束的测试：

INSERT INTO t_group
VALUES(101,'AG','描述信息')#为主表添加一条记录

---以下为从表添加记录---

INSERT INTO t_user
VALUES(0,'Tom',102)#报错，因为在主表中无group_id为102的.

INSERT INTO t_user
VALUES(0,'Tom',101)#成功，因为在主表存在group_id为101的.

外键约束等级说明

外键约束等级有许多种，约束等级是为了设置主表和从表之间数据依赖性紧密程度的。

约束等级分为：

1.Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。

2.Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null。

3.No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作。

4. Restrict方式：同no action，都是立即检查外键约束 Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别。

如果没有指定等级，就相当于Restrict方式。

对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

此方式为：更新时同步数据，不允许删除。

声明约束等级

#创建主表
CREATE TABLE t_group1(
group_id INT PRIMARY KEY ,
group_name VARCHAR(25),
group_desc VARCHAR(255)
)

#创建从表
CREATE TABLE t_user1(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(25),
group_id INT,

#声明外键约束和约束等级
FOREIGN KEY (group_id) REFERENCES t_group1(group_id) ON UPDATE CASCADE ON DELETE SET NULL
)

#此约束等级说明:当主表更新时，从表同步更新，主表删除时，从表设置为NULL

删除外键约束

#删除外键约束
#1.查看某个表的外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = 't_user1';

#2.根据索引名删除外键约束
ALTER TABLE t_user1 DROP FOREIGN KEY t_user1_ibfk_1 ;

#3.查看索引
SHOW INDEX FROM t_user1; #查看某个表的索引名

#4.删除索引
ALTER TABLE t_user1 DROP INDEX group_id;

外键名删除前

外键名删除后

索引名删除前后不作展示。

检查约束

检查某个值添加时是否符合某要求。

关键字：CHECK

说明：在MySQL5.7版本中，默认值约束是不生效的，在MySQL8.0中才会生效。

检查约束作用演示

CREATE TABLE t_check1(
id INT PRIMARY KEY ,
`name` VARCHAR(25),
age INT CHECK(age > 18)
);

INSERT INTO t_check1 VALUES(1,'Tom',18);#报错Check constraint 't_check1_chk_1' is violated.
INSERT INTO t_check1 VALUES(1,'Tom',19);#通过，添加时检查age是否大于18。

默认值约束

当字段未显示赋值，给该字段赋默认值。

关键字：DEFAULT

默认值约束声明使用

CREATE TABLE t_def1(
id INT PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(255),
author_name VARCHAR(25) DEFAULT 'Tom'
)

DESC t_def1;

INSERT INTO t_def1(book_name) VALUES('书名')

SELECT * FROM t_def1;

查询结果