本文正在参加「技术专题19期 漫谈数据库技术」活动
关系型数据库系统和文件系统的一个不同点就是关系型数据库本身能够保证存储数据的完整性，不需要应用程序控制，几乎所有关系型数据库都采用约束机制保持数据完整性，那么今天来聊聊关系型数据库的约束机制。
聊完整性，一定要结合业务来聊，要不然有点耍流氓，嘿嘿嘿，还是举个例子：
要搭建一个宿舍管理系统，那么有两个业务是主题是必须的：
学生
| 姓名 | 性别 | 年级 | 班级 |
|---|---|---|---|
| 张三 | 男 | 2 | 3 |
| 李四 | 女 | 2 | 3 |
宿舍
| 宿舍楼 | 门牌号 | 容量 |
|---|---|---|
| 1 | 203 | 8 |
| 1 | 204 | 8 |
那基于这样两个主体，我们来研究一下MySQL的约束机制：
完整性分类
一般来说，数据有三种形式的完整性，针对不同的完整性，有不同的约束机制：
实体完整性
德国哲学家莱布尼茨说过: 世上没有两片完全相同的树叶。现实生活当中，所有存在的事务都是由一个唯一的可以区分的条件的，哪怕是双胞胎也是一样的，那么在数据库当中实体的完整性就是要求数据由唯一的主码（主键）来标识数据，并且主键不可以为空，因为没有唯一标识的数据是不可以区分的，和我们的业务逻辑是冲突的，比如学生应该由id或者学号来区分，否则叫张伟的学员区分将是学校的一大难题。
MySQL提供了三种约束机制来保证数据的完整性：
1、primary key 设置主键，一般会设置一个自增的id
2、unique key 设置主键，不可以重复字段
3、触发器保证数据完整性，在数据增删改查和执行前后进行逻辑判断
域完整性
首先说这里的域指的是数据当中的字段，域完整性就是字段的完整性，字段的完整性要和业务进行结合去考虑，比如：不可以为空，不可以重复，数据类型，取值范围这些，比如：学员的性别只能出现，男或者女，年龄不可以是-1
为了保证域完整性可以采用的约束机制有：
1、MySQL本身提供的数据类型，可以保证数值的类型和大小完整性
2、Foreign Key 外键约束，保证值在一定的范围。
3、触发器保证域完整性，在数据增删改查和执行前后进行逻辑判断
4、default函数通过默认值保证域完整性
参照完整性
参照完整性值的是维持两个表之间的关系的完整性，比如，学员和宿舍两个业务实体之间是有关系的，学员必须有宿舍可以住，住的宿舍必须是唯一的，等等...
当然也有很多机制来保证参照完整性:
1、primary key 设置主键，一般会设置一个自增的id
2、unique key 设置主键，不可以重复字段
3、Foreign Key 外键约束，一对多关系映射。
4、default函数通过默认值保证参照完整性
5、not null 不可以为空保证参照完整性
注意
1、这里要注意的是，我们采用主键来进行完整性的约束，涉及到了另外的一个知识点，就是索引，这里要刻意的关注一下:尽管创建一个唯一索引就是创建了一个唯一约束，但是，约束是一个逻辑概念，是用来保证数据完整性的，而索引是一种数据结构，二者不在一个层面上。
约束实现方法
primary key
primary key是设置主键约束的方法，通常在创建表的时候创建，这里要注意：主键约束，既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键只能有一个。
单字段主键
create table student(id int primary key,s_name varchar(20));
-- 或者
create table student(id int,s_name varchar(20),primary key(id));
多字段联合主键
create table student(id int,s_name varchar(20),primary key(id,s_name));
当然对已经创建好的没有主键的表也可以进行修改添加：
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| s_name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table student add primary key(id);
....
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| s_name | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
通常数字主键会和自增长连用
unique key
unique key的使用和primary key的类似
create table student(id int primary key,s_name varchar(20) unique);
-- 或者
create table student(id int,s_name varchar(20),unique(s_name));
当然也可以在也有的表上进行修改
alter table student add CONSTRAINT unique_name unique(s_name);
这里要注意的是 add一定要和and区分写对，为啥这么说能，嗯，我有个朋友.....
enum
对于性别这样的字段，值是固定的几个，而不是用户随机插入的。所以，就可以用枚举来指定。
mysql> create table student(id int primary key,s_name varchar(20), gender enum("男","女"));
mysql> insert into student(id,s_name,gender) values(1,"张三","1");
这里注意enum确实约束了gender的取值范围，但是需要通过set来设置sql_model
set sql_mode ='STRICT_TRANS_TABLES';
check约束
enum是对字段进行离散的约束，比如对年龄这样的范围约束（1<150）就有点无力了，所以可以使用check约束
create table student(id int primary key,s_name varchar(20), age int ,check(1<age<100));
触发器和外键
触发器和外键本身是一个很复杂的知识点，并且需要案例支撑，这里我们不进行展开，只是简单的展示一下他们的创建语句，之后单独介绍。
创建触发器
触发器是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行，当然，存储器调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
创建触发器，当学生表发生插入，自动添加到日志表：
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| s_name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc log;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| l_time | date | YES | | NULL | |
| content | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create trigger s_log after insert on student for each row insert into log(l_time,content) values(now(),'学员表进行了数据插入');
Query OK, 0 rows affected (0.01 sec)
-- 测试
mysql> insert into student(id,s_name,age) values(3,"laobian",10);
Query OK, 1 row affected (0.01 sec)
mysql> select * from log;
+----+------------+----------------------+
| id | l_time | content |
+----+------------+----------------------+
| 1 | 2022-11-24 | 学员表进行了数据插入 |
+----+------------+----------------------+
2 rows in set (0.00 sec)
创建外键约束
外键需要有一对多的业务逻辑场景，比如一个宿舍可以有多个学生，但是一个学生可以有一个宿舍。
mysql> desc dormitory;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| d_number | varchar(6) | YES | | NULL | |
+----------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> create table student(id int primary key auto_increment, s_name varchar(24), d_id int, foreign key(d_id) references dormitory(id));
Query OK, 0 rows affected (0.06 sec)
mysql> desc student;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| s_name | varchar(24) | YES | | NULL | |
| d_id | int(11) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
--测试
mysql> insert student(s_name,d_id) values("李四",1)
-> ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`d_id`) REFERENCES `dormitory` (`id`))
这个时候插入学员的宿舍id的时候就必须保证在宿舍表当中有对应id的数据，否则是插入不进去的。
ok，Mysql约束机制先聊到这里，还是欢迎各位大佬多多指点。
本文正在参加「技术专题19期 漫谈数据库技术」活动
