365 - 表的完整性约束

         为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

        MySQL中主要支持以下几种种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

一、非外键约束

1、代码演示非外键约束

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
--  1048 - Column 'sname' cannot be null 不能为null
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复
-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束
insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@126.com');
-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');
-- 查看数据：
select * from t_student;

实操时，出现性别和年龄的检查约束失败 ？？？ 

原因：

        Check约束是MySQL8（即5.8）中提供的支持，实操时用的是MySQL5.7。

实操代码：

## 表的完整性约束
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
 
约束： 
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student2(
        sno int(6) primary key auto_increment, -- 主键，自增
        sname varchar(5) not null, -- 非空
        -- sex char(1) default '男' check(sex='男' || sex='女'), -- 默认“男”，只能是男女
				sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),-- 限定年龄区间 
        enterdate date, 
        classname varchar(10),
        email varchar(15) unique -- 唯一
);

-- 验证：

-- 添加数据
insert into t_student2 values(1,'张三','男',20,'2020-9-1','java1班','zs@126.com');
-- ??? 为什么性别限制不了？？？
insert into t_student2 values(2,'张三','哈',60,'2020-9-1','java1班','ls33@126.com');
-- ??? 为什么年龄限制不了？？？
insert into t_student2 values(null,'张三','女',70,'2020-9-1','java1班','ls2@126.com');

insert into t_student2 (sname,enterdate) values ('小明','2021-9-1');
-- 主键没设置值、或设置 null 和 default —— 也可以实现自增
insert into t_student2 values(null,'小红','男',20,'2020-9-1','java1班','xh@126.com');
insert into t_student2 values(default,'小刚','男',20,'2020-9-1','java1班','xg@126.com');

-- 查看数据
select * from t_student2;

-- 删除表
drop table t_student2;

结果：？？？  —— 原因见上

2、约束从作用上可以分为两类

(1)   表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；   

        多个定义(约束)之间用‘，’分隔；必须指出要约束的列的名称；

(2)   列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

（ 列级约束—— 见上面）

表级约束 —— 示例：
注意：自增约束、非空约束、默认值约束 只能通过 列级约束 进行添加。

-- 创建数据库表：
create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
        constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
        constraint ck_stu_sex check (sex = '男' || sex = '女'), -- 这样的检查约束可以分辨出具体是哪个检查约束
        constraint ck_stu_age check (age >= 18 and age <= 50),
        constraint uq_stu_email unique (email)
);
-- 添加数据：
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- > 3819 - Check constraint 'ck_stu_sex' is violated.
-- > 3819 - Check constraint 'ck_stu_age' is violated.
-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'
insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');
-- 查看数据：
select * from t_student;

3、在创建表以后添加约束

示例：

-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);
-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉
-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);
-- 查看表结构：—— 可以看到约束条件
desc t_student;

验证约束添加成功： —— 查看表结构

 也可以： 选定表—>右键选择 “设计表”

4、总结

1）主键约束 

        主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

        主键可以是单个字段，也可以是多个字段组合。对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

2）非空约束 

        非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段，非空约束的添加只能使用列级约束（非空约束无表级约束）。

为已存在表中的字段添加非空约束 ：

alter table t_student modify sex char(1) not null;  

使用ALTER TABLE语句删除非空约束 ：

alter table t_student modify sex vchar(1) null;  

3）唯一约束

        唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

        无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束。

4）检查约束

        检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。 

5）默认值约束 

        默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

     默认值约束的设置与非空约束一样，也只能使用列级约束。

6）字段值自动增加约束

        自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

为已存在表中的字段添加自增约束 

alter  table t_student3  modify sno int(6)  auto_increment;

使用ALTER TABLE语句删除自增约束 

alter table t_studen3  modify  sno int(6);

二、外键约束

1、什么是外键约束？

        外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
        外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

        举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

主表（父表）：班级表  -  班级编号 - 主键
从表（子表）：学生表 - 班级编号 - 外键

2、sql语言示例

-- 先建父表：班级表
create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(10) not null,
	room char(4)
);

-- 查看表格数据
select * from t_class;

-- 添加班级数据
insert into t_class values(null,'java001','r803');
insert into t_class values(null,'java002','r259');
insert into t_class values(null,'大数据001','r651');
-- 上面依次添加 比较麻烦
-- 可以一次性添加多条数据
insert into t_class values(null,'java001','r803'),(null,'java002','r259'),(null,'大数据001','r651');

-- 查看表结构：—— 可以看到约束条件
desc t_class;
-- 删除表：
drop table t_class;

-- 创建子表,学生表：
create table t_student4(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
				-- classname varchar(10), -- 这个就不用了
				-- -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
);
select * from t_student4;

desc t_student4;
drop table t_student4;

-- 添加学生信息
insert into t_student4 values(null ,'张三',1),(null ,'李四',1),(null ,'王五',2);


-- 出现问题：
-- 1.添加一个学生对应的班级编码为4：
insert into t_student4 values (null,'丽丽',4);-- 但是根本就没有班级编码为4的班级
-- 2.删除班级2：
delete from t_class where cno = 2; -- 但是对应班级下的学生根本删除不掉
-- 出现问题的原因：
-- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义

-- 解决办法，添加外键约束：
-- 注意：外键约束只有表级约束，没有列级约束： —— 创建表的时候就添加外键约束（并且只有表级约束）
create table t_student4(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
);

-- 或者，这样：
create table t_student4(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)
);
-- 在创建表以后添加外键约束：
alter table t_student4 add constraint fk_stu_classno foreign key (classno) references t_class (cno)

-- 上面的两个问题都解决了：
-- 添加学生信息：
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student4 values (null,'张三',1),(null,'李四',1),(null,'王五',2);

-- 删除班级1：
-- 2.删除班级2：
insert into t_student4 values (null,'张三',3),(null,'李四',3),(null,'王五',3);
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno = 3; -- 有学生在3班，故不能轻易的删除

3、外键策略

示例：

-- 学生表删除：
drop table t_student;
-- 班级表删除：
drop table t_class;
-- 注意：先删除从表，再删除主表。（视频中这个位置笔误，笔记现在已经更正）
-- 先创建父表：班级表：
create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
)
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
-- 添加学生表，添加外键约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
-- 可以一次性添加多条记录：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);
-- 查看班级表和学生表：
select * from t_class;
select * from t_student;
-- 删除班级2：如果直接删除的话肯定不行因为有外键约束：
-- 加入外键策略：
-- 策略1：no action 不允许操作
-- 通过操作sql来完成：
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2：
delete from t_class where cno = 2;
-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 先删除在重新添加：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

-- 试试更新：—— 直接更新不行，必须先执行上面两步
update t_class set cno = 5 where cno = 3;
-- 试试删除：
delete from t_class where cno = 5;
-- 策略3：set null  置空操作：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
-- 试试更新：
update t_class set cno = 8 where cno = 1;
-- 注意：
-- 1. 策略2 级联操作  和  策略3 的  删除操作  可以混着使用：
-- 如：后面用（混着写）：on update cascade on delete set null -- 即更新的时候为级联，删除的时候为置空
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;
-- 2.应用场合：
-- （1）朋友圈删除，点赞。留言都删除  --  级联操作
-- （2）解散班级，对应的学生 置为班级为null就可以了，-- set null

三、DDL和DML的补充

1、SQL示例

-- 创建表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
insert into t_student values (null,'张三','男',21,'2023-9-1','java01班','zs@126.com');
insert into t_student values (null,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (null,'露露','男',21,'2023-9-1','java01班','ll@126.com');
-- 查看学生表：
select * from t_student;
-- 添加一张表：快速添加：结构和数据跟t_student 都是一致的
create table t_student2
as
select * from t_student;
select * from t_student2;
-- 快速添加，结构跟t_student一致，数据没有：
create table t_student3
as
select * from t_student where 1=2;
select * from t_student3;
-- 快速添加：只要部分列，部分数据：
create table t_student4
as
select sno,sname,age from t_student where sno = 2;
select * from t_student4;
-- 删除数据操作 :清空数据 （不是删除表，删除表用drop）
delete from t_student;
-- 或：
truncate table t_student;

2、delete和truncate的区别

        从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：
(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。
(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。
(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。
(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。