MySQL篇（约束）(持续更新迭代）

一、作用

二、目的

三、数据的完整性考虑方面

1. 实体完整性（Entity Integrity）

2. 域完整性（Domain Integrity）

3. 引用完整性（Referential Integrity）

4. 用户自定义完整性（User-defined Integrity）

四、约束的类型

1. 表级约束和列级约束

2. MySQL中的约束类型

五、约束和索引

六、约束实操

1. 非空约束

1.1. 建表时添加非空约束

1.2. 建表后添加非空约束

1.3. 删除非空约束

2. 唯一约束

2.1. 建表时添加唯一约束

2.2. 建表后添加唯一约束

2.3. 查看唯一键约束

2.4. 复合唯一

2.5. 删除唯一键约束

2.6. 查看唯一键对应的索引

3. 主键约束

3.1. 特点

3.2. 作用

3.3. 唯一键约束和主键约束区别

3.4. 建表时添加主键约束

3.5. 建表后添加主键约束

3.6. 复合主键

3.7. 删除主键约束

4. 默认值约束

4.1. 作用

4.2. 建表时添加默认值约束

4.3. 建表后添加默认值约束

4.4. 删除默认值约束

5. 约束修饰属性：自增属性

5.1. 自增属性的作用

5.2. 5自增属性的特点

5.3. 建表时添加自增属性

5.4. 建表后添加自增属性

5.5. 删除自增属性

5.6. 演示错误

6. 检查约束

6.1. 作用

6.2. 不同MySQL版本的检查约束效果

6.3. 建表时添加检查约束

6.4. 建表后添加检查约束

6.5. 查看一个表的约束

6.6. 删除检查约束

7. 外键约束

7.1. 使用情况

7.2. 如何理解外键约束

7.3. 作用

7.4. 主表和从表/父表和子表

7.5. 特点

7.6. 建表时添加外键约束

7.7. 建表后添加外键约束

7.8. 查看外键约束名

7.9. 查看外键字段索引

7.10. 删除外键约束

7.11. 外键演示问题

7.12. 设置外键约束行为等级

7.13. 外键约束一般用于多表关系

一、作用

约束就是作用于表中字段上的规则，用于限制存储在表中的数据。

二、目的

约束存在的目的就是为了保证数据的完整性，

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）、

约束是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出

的

三、数据的完整性考虑方面

1. 实体完整性（Entity Integrity）

例如：同一个表中，不能存在两条完全相同无法区分的记录

2. 域完整性（Domain Integrity）

例如：年龄范围0-120，性别范围“男/女”

3. 引用完整性（Referential Integrity）

例如：员工所在部门，在部门表中要能找到这个部门

4. 用户自定义完整性（User-defined Integrity）

例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍

四、约束的类型

1. 表级约束和列级约束

键约束和检查约束是表级约束，即不仅要看约束字段当前单元格的数据，还要看其他单元格的数据

非空约束和默认值约束都是列级约束，即约束字段只看当前单元格的数据即可，和其他单元格无关

所有的表级约束都可以在“information_schema.table_constraints”表中查看

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

总结：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

2. MySQL中的约束类型

知识内容

非空约束：限制该字段的数据不能为NULL，关键字NOT NULL
唯一约束：保证该字段的所有数据都是唯一、不重复的，关键字UNIQUE
主键约束：主键是一行数据的唯一标识，要求非空且唯一，关键字PRIMARY KEY
默认约束：保存数据时，如果未指定该字段的值，则采用默认值，关键字DEFAULT
检查约束（MySQL8.0.16版本）：保证字段满足某一个条件，CHECK
外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性（FOREIGN KEY）

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

知识小结

键约束：主键约束、外键约束、唯一键约束
Not NULL约束：非空约束
Check约束：检查约束
Default约束：默认值约束
自增是键约束字段的一个额外的属性

五、约束和索引

在 MySQL 中键约束会自动创建索引，提高查询效率

约束是一个逻辑概念，它不会单独占用物理空间，索引是一个物理概念，它是会占用物理空间

六、约束实操

1. 非空约束

1.1. 建表时添加非空约束

create table 表名称(
    字段名 数据类型 not null,
    字段名 数据类型 not null,
    字段名 数据类型
);

1.2. 建表后添加非空约束

alter table 表名称 modify 【column】 字段名 数据类型 not null;

/*
    如何该字段已经有值了，给该字段增加非空约束，要求该字段的值不能有NULL值，否则需要先处理NULL值才能加上非空约束
*/

1.3. 删除非空约束

alter table 表名称 modify 【column】 字段名 数据类型;

/*
    如果某个字段有not null非空约束，使用modify修改字段的数据类型、位置、字符集和校对规则、默认值约束时，
    想要保留非空约束，必须把not null带上，否则会在修改字段的数据类型、位置、字符集和校对规则时，会把非空约束给丢掉。
*/

2. 唯一约束

2.1. 建表时添加唯一约束

#在建表时，可以指定唯一键约束
create table 表名称(
    字段名 数据类型 unique key,
    字段名 数据类型 unique key,
    字段名 数据类型
);

create table 表名称(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型,
    unique key(字段名),#字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
    unique key(字段名)
);

2.2. 建表后添加唯一约束

#如何在建表后添加唯一键约束
alter table 表名称 add unique 【key】(字段名);

2.3. 查看唯一键约束

desc 表名称;

show create table 表名称;

show index from 表名称; #查看表的索引信息

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

2.4. 复合唯一

create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

2.5. 删除唯一键约束

alter table 表名称 drop index 索引名;
#删除唯一键约束需要手动删除对应的索引

2.6. 查看唯一键对应的索引

show index from 表名称;
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

3. 主键约束

3.1. 特点

① 保证字段唯一并且非空

② 一个表最多只能有一个主键约束

③ 如果主键是由多列组成，可以使用复合主键

④ 主键列会自动创建索引（能够根据主键查询的，就根据主键查询，效率更高）

⑤ 主键列的唯一并且非空是约束的概念

但是mysql会给每个表的主键列创建索引，会开辟单独的物理空间来存储每一个主键的目录表（Btree结构）

这样设计的意义，可以根据主键快速查询到某一行的记录

⑥ 如果删除主键约束了，主键约束对应的索引就自动删除了

3.2. 作用

用来唯一的确定一条记录

3.3. 唯一键约束和主键约束区别

① 唯一键约束一个表可以有多个，主键约束只能有一个

② 唯一键约束本身不带非空限制，若需要非空，需要单独定义,

主键约束不需再定义NOT NULL，自身就带非空限制

3.4. 建表时添加主键约束

create table 表名称(
    字段名  数据类型  primary key,
    字段名  数据类型,  
    字段名  数据类型  
);
create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名)#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键
);

3.5. 建表后添加主键约束

alter table 表名称 add primary key(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

3.6. 复合主键

create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    primary key(字段名1,字段名2)  #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

3.7. 删除主键约束

alter table 表名称 drop primary key;

4. 默认值约束

4.1. 作用

给某个字段/某列指定默认值，当添加时或修改时，可以使用默认约束

4.2. 建表时添加默认值约束

create table 表名称(
    字段名  数据类型  primary key,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);
create table 表名称(
    字段名  数据类型 default 默认值 ,
    字段名  数据类型 not null default 默认值,  
    字段名  数据类型 not null default 默认值,,
    primary key(字段名),
    unique key(字段名)
);

说明：默认值约束一般不在唯一键和主键列上加

4.3. 建表后添加默认值约束

alter table 表名称 modify 字段名 数据类型 default 默认值;

#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

4.4. 删除默认值约束

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束

alter table 表名称 modify 字段名 数据类型  not null; #删除默认值约束，保留非空约束

5. 约束修饰属性：自增属性

5.1. 自增属性的作用

给某个字段自动赋值，这个值是一直往上增加，如果没有特意干扰的，每次自增1.

5.2. 5自增属性的特点

① 一个表只能有一个自增字段，因为一个表只有一个AUTO_INCREMENT属性记录自增字段值

② 并且自增字段只能是key字段，即定义了主键、唯一键等键约束的字段,一般都是给主键和唯一键加自增。

③ 自增字段应该是数值类型，一般都是整数类型

④ 如果自增列指定了 0 和 null，会在当前最大值的基础上自增，如果自增列手动指定了具体值，直接赋值为具体

值

⑤ 如果手动修改AUTO_INCREMENT属性值，必须 > 当前自增字段的最大值

5.3. 建表时添加自增属性

create table 表名称(
    字段名  数据类型  primary key auto_increment,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);
create table 表名称(
    字段名  数据类型 default 默认值 ,
    字段名  数据类型 unique key auto_increment,  
    字段名  数据类型 not null default 默认值,,
    primary key(字段名)
);

5.4. 建表后添加自增属性

alter table 表名称 modify 字段名 数据类型 auto_increment;

5.5. 删除自增属性

alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

5.6. 演示错误

create table employee(
    eid int auto_increment,
    ename varchar(20)
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key   
create table employee(
    eid int primary key,
    ename varchar(20) unique key auto_increment
);
ERROR 1063 (42000): Incorrect column specifier for column 'ename'  因为ename不是整数类型

6. 检查约束

6.1. 作用

① 检查约束用于限制字段中的值的范围

② 如果对单个字段定义 CHECK 约束，那么该字段只允许特定范围的值

③ 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他字段的值在特定的字段中对值进行限制

④ 在MySQL 8.0.16版本之前， CREATE TABLE 语句支持给单个字段定义CHECK约束的语法，但是不起作用

6.2. 不同MySQL版本的检查约束效果

① MySQL8.0之前，给表定义了检查约束，也不起作用

② MySQL8.0.16版本之后，CREATE TABLE语句既支持单个字段定义列级CHECK约束的语法，还支持定义表级CHECK约束的语法

6.3. 建表时添加检查约束

#在建表时，可以指定检查约束
create table 表名称(
    字段名1 数据类型 check(条件),  #在字段后面直接加检查约束
    字段名2 数据类型,
    字段名3 数据类型,
    check (条件)  #可以限定两个字段之间的取值条件
);

#在建表时，可以指定检查约束
create table 表名称(
    字段名1 数据类型 check(条件) enforced,  #在字段后面直接加检查约束
    字段名2 数据类型,
    字段名3 数据类型,
    check (条件)  enforced #可以限定两个字段之间的取值条件
);

如果省略或指定为ENFORCED，则会创建检查约束并强制执行约束，不满足约束的数据行不能插入成功

如果写的是not ENFORCED，则不满足检查约束也没关系

6.4. 建表后添加检查约束

#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);

6.5. 查看一个表的约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema 数据库名（系统库）
#table_constraints 表名称（专门存储各个表的约束）
#WHERE 条件
#table_name = '表名称'条件是指定表名称

6.6. 删除检查约束

alter table 表名称 drop check 检查约束名;

7. 外键约束

7.1. 使用情况

外键约束会影响性能，效率，绝大部分情况下不会添加外键约束!

7.2. 如何理解外键约束

① 如果两个表之间有关系（一对一、一对多）

比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？不是一定的

② 建和不建外键约束有什么区别？

建外键约束操作受限制（例如：创建表、删除表、添加、修改、删除）会，从语法层面受到限制例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到
不建外键约束：操作不受限制（创建表、删除表、添加、修改、删除），要保证数据的引用完整性，依靠程序员的自觉性例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门

③ 建和不建外键约束和查询有没有关系？没有关系

7.3. 作用

① 限定某个表的某个字段的引用完整性

② 比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

7.4. 主表和从表/父表和子表

① 主表（父表）：被引用的表，被参考的表

② 从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表，

部门表是主表，员工表是从表

例如：学生表、课程表、选课表

选课表的学生和课程要分别参考学生表和课程表，

学生表和课程表是主表，选课表是从表

7.5. 特点

在“从表”中指定外键约束，并且一个表可以建立多个外键约
创建(create)表时就指定外键约束的话，先创建主表，再创建从
删表时，先删从表（或先删除外键约束），再删除主表或者先解除关系，再各自删除。
从表的外键列，必须引用/参考主表的键列（主键或唯一键）为什么？因为被依赖 / 被参考的值必须是唯一的
从表的外键列的数据类型，要与主表被参考 / 被引用的列的数据类型一致，并且逻辑意义一致例如：都是表示部门编号，都是int类型
外键列也会自动建立索引（因此根据外键查询效率高
外键约束的删除，不会自动删除外键约束的索引，如果要删除对应的索引，必须手动删除

7.6. 建表时添加外键约束

create table 主表名称(
    字段1  数据类型  primary key,
    字段2  数据类型
);

create table 从表名称(
    字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT] [外键名称] FOREIGN KEY (从表的某个字段) REFERENCES 主表 (主表列名) 
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

注意：从表的字段，换种说法，就是外键字段名

[CONSTRAINT] [外键名称] FOREIGN KEY (从表的某个字段) REFERENCES 主表 (主表列名)

更改为

[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)

7.7. 建表后添加外键约束

alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) 【on update xx】【on delete xx】;

7.8. 查看外键约束名

desc 从表名称; #可以看到外键约束，但看不到外键约束名

show create table 从表名称; #可以看到外键约束名

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema 数据库名（系统库）
#table_constraints 表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称' 条件是指定表名称

7.9. 查看外键字段索引

show index from 表名称; #查看某个表的索引名

7.10. 删除外键约束

删除外键约束，不会自动删除外键约束列的索引，需要单独删除

(1) 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

alter table 从表名 drop foreign key 外键约束名;

(2) 第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名

alter table 从表名 drop index 索引名;

7.11. 外键演示问题

① 失败：不是键列

create table dept(
    did int ,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
    eid int primary key,  	#员工编号
    ename varchar(5),     	#员工姓名
    deptid int,				#员工所在的部门
    foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint  
原因是dept的did不是键列

② 失败：数据类型不一致

create table dept(
    did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
    eid int primary key,  	#员工编号
    ename varchar(5),     	#员工姓名
    deptid char,		  	#员工所在的部门
    foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint  
原因是从表的deptid字段和主表的did字段的数据类型不一致，并且要它俩的逻辑意义一致

③ 成功，两个表字段名一样

create table dept(
    did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
    eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    did int,				#员工所在的部门
    foreign key (did) references dept(did)  
    #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
    #是否重名没问题，因为两个did在不同的表中
);

7.12. 设置外键约束行为等级

约束行为	说明
No action	在父表上update/delete记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新（与 RESTRICT 一致）默认行为
Restrict（默认）	在父表上update/delete记录时，首先检查该记录是否有对应外键，如果有则不允许删除 / 更新（与 No action 一致）默认行为
Cascade	在父表上update/delete记录时，首先检查该记录是否有对应外键，如果有则也删除 / 更新外键在子表中的记录
Set null	在父表上update/delete记录时，首先检查该记录是否有对应外键，如果有设置子表中该外键值为null （要求子表该外键值运行取null）
Set default	父表有变更时,子表将外键列设置成一个默认的值，（Innodb不支持）

7.13. 外键约束一般用于多表关系

一般我们不会添加外键，因为添加外键约束会损耗性能、效率（具体原因，上述外键约束已经归纳好了）

还有我的那个美味来项目就是没有采用外键的情况进行开发表与表之间的关联，只要我们遵守这个规定就好。