【MySQL】约束

一、基本概念

1、什么是约束
约束是表级的强制规定

2、为什么使用约束
是为了保证表中数据的完整性，完整性又可以拆分为精确性和可靠性

3、怎么去保证数据完整性呢，从以下四个角度进行考虑

• 实体完整性：一张表中，不能存在两条完全相同无法区分的记录
• 域完整性：限制某一个字段，例如性别只能是男/女，但是他不会约束年龄
• 引用完整性：例如员工所在的部门，要在部门表中能找到这个部门的信息
• 用户自定义完整性：例如用户名唯一、密码不能为空等

4、约束的分类

• 从约束字段的个数来看：分为单列约束、多列约束
• 从约束的作用范围来看：分为列级约束（声明在对应字段的后面）、表级约束（表中所有字段都声明完后，在所有字段后单独声明的约束）
• 从约束的功能上来看
  • not null：非空约束
  • unique：唯一性约束
  • primary key：主键约束
  • foreign key：外键约束
  • check：检查约束
  • default：默认值约束

5、如何添加约束

• 在CREATE TABLE时添加约束
• 使用ALTER TABLE添加约束，也可以使用这个删除约束

6、如何查看约束
需要到information_schema``这个库下，查询TABLE_CONSTRAINTS这个表
例如我需要查询t_decade_test表中存在哪些约束，就可以使用

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 't_decade_test';

二、约束类型介绍

1、not null：非空约束

• 作用：限制某个字段/某列的值不允许为空
• 关键字：NOT NULL
• 注意：
  • 空字符串不等于null，0也不等于null
  • 如果没有使用not null约束，那么默认情况下所有的类型都是可以为null的
  • 非空约束只能设置成列级约束

1）如何在建表时创建非空约束

CREATE TABLE t_decade_test_not_null(
id INT NOT NULL,
last_name VARVHAR(20) NOT NULL,
email VARCHAR(25),
salary DECIMAL(10,2)
);

2）创建完这个表后，如下几种情况会报错

• 使用insert插入数据，id或者last_name的值为null
• 使用insert插入数据，没有给id或者last_name字段赋值，且没有指定它的默认值
• 只用update更新数据，给id或者last_name字段传递的值为null

3）如何使用ALTER TABLE添加约束，我们还是以之前的t_decade_test_not_null为例
对email字段添加一个非空约束，但是如果email字段已经存在null值，那么下方的语句是无法执行的

ALTER TABLE t_decade_test_not_null
MODIFY email VARCHAR(25) NOT NULL;

4）如何使用ALTER TABLE删除约束呢？其实只要把NOT NULL改成NULL即可

ALTER TABLE t_decade_test_not_null
MODIFY email VARCHAR(25) NULL;

2、unique：唯一性约束

• 作用：确保某一列/某一个字段的值不会重复
• 关键字：UNIQUE
• 注意点：
  • MySQL会给唯一约束的列上默认建一个唯一索引
  • 一个表中可以设置多个唯一性约束
  • 唯一性约束可以针对某一个字段，也可以针对某几个字段组合起来的值
  • 唯一性约束允许字段为空。如果两条数据，该字段都赋值null，不会受到唯一性约束的限制
  • 创建唯一性约束的时候，如果不给约束命名，那就默认唯一性约束的名字和字段名相同

1）如何在建表时，给字段添加唯一性约束

CREATE TABLE t_decade_test_unique(
id INT UNIQUE #这就是列级约束,
last_name VARVHAR(20),
email VARCHAR(25),
salary DECIMAL(10,2),
# 添加表级约束，一般约束的命名格式为：作用_表名_字段
CONSTRAINT uk_t_decade_test_unique_email UNIQUE(email)
);

2）如何使用ALTER TABLE来添加约束
方式一：alter table 表名 add UNIQUE(字段名称)

alter table t_decade_test_unique add UNIQUE(lastname);

# 如果要给约束取名，还是使用constraint
alter table t_decade_test_unique add constraint uk_t_decade_test_unique_lastname UNIQUE(lastname,email);

方式二：alter table 表名 modify 字段名 字段类型 UNIQUE

alter table t_decade_test_unique MODIFY last_name VARVHAR(20) UNIQUE;

3）什么是复合的唯一性约束
可以理解为，我们对多个字段进行组合
要求两条数据中，这几个字段的值不能完全相同，必须有一个或者几个字段的值不一样

CREATE TABLE t_unique_user(
id INT,
user_name VARCHAR(20),
user_pwd VARCHAR(15),
constraint uk_t_unique_user_nameAndPwd UNIQUE(user_name,user_pwd);
);

这样一来，这个表中的任意两条数据，name和pwd字段就不能完全相同

4）删除唯一性约束

• 创建唯一性约束的字段会自动创建一个唯一索引，所以我们只能通过删除唯一索引的方式来删除该约束
• 而唯一索引的名和唯一性约束的名称是一样的
• 如果是单列约束，那默认就和字段名称相同，如果是复合约束，那默认就和()中排在第一位的字段名相同。当然我们也可以使用constraint来自定义约束名

alter table t_unique_user
DROP index 索引名;

我们也可以通过show index from 表名;查看表的索引

3、primary key：主键约束

• 作用：用来标识表中的一行记录，也就是说，可以通过这个来确定某条数据是独一无二的
• 关键字：primary key
• 特点：主键约束相当于唯一性约束+非空约束
• 注意点：
  • 一个表中只能有一个主键约束，它可以在列级别创建，也可以在表级别创建
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么任何一列都不允许为空，而且组合的值不允许重复
  • 主键约束的名总是PRIMARY，它不能像前面的唯一性约束一样自定义名称
  • 创建主键约束后，系统会自动在对应的列或者组合字段上创建主键索引（根据主键进行查询的效率更高），如果删除了主键约束，对应的主键索引也会被删除
  • 不要修改主键字段的值，因为他是数据记录的唯一标识，如果修改了主键的值，就有可能破坏数据的完整性

1）创建表时，如何添加主键约束
列级约束

CREATE TABLE t_test_primary_key(
id INT PRIMARY KEY,
last_name VARCHAR(20),
salary DECIMAL(10,2),
email VARCHAR(15)
);

或者表级约束

CREATE TABLE t_test_primary_key(
id INT,
last_name VARCHAR(20),
salary DECIMAL(10,2),
email VARCHAR(15),
constraint prk_t_test_primary_key_id PRIMARY KEY(id) #这里的名称是无效的
);

或者复合主键

CREATE TABLE t_test_table(
id INT,
user_name VARCHAR(20),
user_pwd VARCHAR(15),
PRIMARY KEY(user_name,user_pwd)
);

2）使用ALTER TABLE创建主键约束

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

3）删除主键约束

ALTER TABLE table_name
DROP PRIMARY KEY;

4、auto_increment：自增列

• 作用：让某个字段的值自增
• 特点：
  • 一个表只能有一个自增字段
  • 当需要产生唯一标识符或者顺序值的时候，可以设置自增长
  • 自增长约束的列必须是键列（必须要有主键约束或者唯一性约束），而且自增长约束的列必须是整数类型，在没有给第一条数据的该字段显式赋值情况下，默认从1开始
  • 如果我们在插入某条数据时，自增列对应的value填的是0或者null，实际上入表的值会是该字段当前最大值加1，并不会展示为0或者null

1）在建表时声明自增列

CREATE TABLE t_auto_increment(
id INT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20)
);

2）使用alter table声明自增列

ALTER TABLE t_auto_increment
MODIFY id INT AUTO_INCREMENT;

3）删除自增

ALTER TABLE t_auto_increment
MODIFY id INT;

4）MySQL5.7与MySQL8.0中计数器的差别
假设现在有一个场景，我们连续执行三次insert语句，id字段value都是0
使用select语句后，我们可以发现三条数据的id依次为1，2，3

现在我们同时在两个版本的MySQL中删除id为3的数据
然后重复执行insert id为0这个操作
然后可以发现，两个版本的MySQL中，该表中的第三条数据并不是从3开始了，而是从4开始

然后，我们删除id为4的这条数据，再对两个版本的MySQL服务都进行重启操作，继续重新insert id=0这个操作，我们可以发现

• MySQL5.7中，该表第三条数据的id重新从3开始
• MySQL8.0中，该表第三条数据的id从5开始

MySQL5.7中，自增主键的计数器是在内存中维护的，数据库重启后，计数器会被初始化
MySQL8.0中，自增主键的计数器会被持久化到重做日志中，每次计数器发生改变都会记录到该日志中，当数据库重启，InnoDB会根据重做日志来初始化计数器的内存值

5、FOREIGN KEY：外键约束

• 作用：限制某个表的某个字段的引用完整性，比如说员工表中某个员工的部门id，必须要在部门表中存在
• 关键字：FOREIGN KEY
• 了解什么是主表/从表或者说什么是父表/子表：
  • 主表（父表）：被引用的表，比如上面的部门表
  • 从表（子表）：引用别人的表，比如上面的员工表
• 特点：
  • 从表的外键引用/参考的必须是主表的主键列或者是唯一约束的那个字段，这是因为被依赖/被参考的值必须是唯一的
  • 创建外键约束时，如果没有自定义名称，那么会自动生成一个外键名，不会像之前介绍的约束一样和列名保持一致了
  • 如果在创建表的时候就指定外键约束，那么必须先创建主表，再创建从表
  • 删表时，先删从表（或者先删外键约束），再删主表
  • 当主表的数据被从表参照时，主表的数据将不被允许删除，如果要删除数据，需要先去从表中删除依赖该记录的数据，然后再删除主表数据
  • 在从表中指定外键约束，一个表可以建立多个外键约束
  • 从表的外键列和主表中被引用的列不一定要名称相同，但是数据类型必须保持一致
  • 创建外键约束时，系统会默认在所在的列上建立普通索引，索引名=列名（列名≠外键约束名）
  • 删除外键约束后，必须手动删除对应的索引

1）在create table时指定外键约束

CREATE TABLE t_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE t_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT,
# 使用表级约束的方式指定外键约束
constraint fk_emp_department_id FOREIGN KEY (department_id) REFERENCES t_dept (dept_id)
);

2）演示外键效果
在创建了t_dept和t_emp表之后
如果两张表都是空表，当我们直接执行insert into t_emp values(1,"decade",10);
SQL执行会报错，因为我们从表t_emp依赖的主表t_dept中并没有id为10的记录

所以我们要先执行
insert into t_dept values(10,"技术部");
再执行insert into t_emp values(1,"decade",10);

执行上述插入语句之后
假设我们执行delete from t_dept where id = 10;，语句还是会报错，因为这条数据被从表依赖
同样的，当我们执行update t_dept set id = 20 where dept_name = '技术部';时，语句也会报错

3）建表后如何使用alter table添加外键约束

CREATE TABLE t_dept2(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE t_emp2(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT
);

ALTER TABLE t_emp2
ADD CONSTRAINT fk_emp2_department_id FOREIGN KEY (department_id) REFERENCES t_dept2 (dept_id);

4）外键约束的约束等级

• Cascade方式：当父表的数据发生update/delete时，同步update/delete子表中引用此记录的数据
• Set null方式：当父表的数据发生update/delete时，将子表中引用此记录的外键列设置为null，注意，子表的外键列不允许有非空约束
• No action方式：如果子表中有引用父表记录的数据，那么不允许对父表对应候选键进行update/delete操作
• Restrict方式：同No action方式
• Set default方式：父表有变更时，将子表的外键列设置成一个默认值，但InnoDB不能识别

对于外键约束，最好使用ON UPDATE CASCADE ON DELETE RESTRICT的方式
即更新会同步，但是不允许删除，使用方式如下

CREATE TABLE t_dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE t_emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
department_id INT,
# 使用表级约束的方式指定外键约束
constraint fk_emp_department_id FOREIGN KEY (department_id) REFERENCES t_dept (dept_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

5）删除外键约束

# 第一步：先查看表中的约束名，删除约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = '表名称';

ALTER TABLE 从表名
DROP FOREIGN KEY 外键约束名称;

# 第二步：查看索引名和删除索引
SHOW INDEX FROM 表名称;

ALTER TABLE 从表名 DROP INDEX 索引名;

6）开发场景
阿里开发规范要求，不得使用外键与级联（可以理解为主表数据更新触发从表相应记录更新），外键与级联适合单机低并发场景，不适合分布式、高并发场景，级联可能导致数据库更新风暴，外键影响数据库插入速度，所以一切外键概念都应该在应用层，即Java代码层面进行解决（经典白学~）

6、check：检查约束

• 作用：检查某个字段的值是否复合要求
• 关键字：CHECK
• 说明：MySQL5.7不支持（可以使用，但对数据验证没有任何作用，就算你添加了不符合要求的数据，也不会触发告警），MySQL8.0支持

1）建表时添加

CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2) CHECK(salary>5000)
);

如果你执行insert into t_emp values(1, "彭于晏", 1500);，语句就会报错

7、default：默认值约束

• 作用：给某个字段设置默认值，如果在插入数据时没有给该字段显式赋值，那么就赋值为默认值
• 关键字：DEFAULT

1）在创建表时添加约束

CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2) DEFAULT 3000
);

如果你执行insert into t_emp values(1, "彭于晏");，那么此条数据对应salary那一列的值就是3000

2）使用ALTER TABLE添加约束

CREATE TABLE t_emp(
id iNT PRIMARY KEY AUTO_INCREMENT
last_name VARCHAR(20),
salary DECIMAL(10,2)
);

ALTER TABLE t_emp
MODIFY salary DECIMAL(10,2) DEFAULT 3000;

3）删除约束

ALTER TABLE t_emp
MODIFY salary DECIMAL(10,2);

三、写在最后

很长时间没有更新博客了，最近也不知道在瞎忙什么，后面会慢慢恢复之前的学习状态，人还是要不断学习，才不会被时代抛弃，希望自己这次坚持的久一点，hh~

如有错误，欢迎指正！！！