MySQL完整性约束

MySQL完整性约束 （外键约束和非外键约束）

• 表级约束（在创建时添加到，后面）

create table t_student(
	sno int(6) auto_increment,-- 代表显示长度 不过多了也没事
	sname varchar(10) not null,-- 是个字符
	sex char(1) default '男', -- 
	age int(3),
	enterdate date,
	classname varchar(10),
	email varchar(20),
	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)
);

• 列级约束

create table t_student(
	sno int(6) primary key auto_increment,-- 代表显示长度 不过多了也没事
	sname varchar(10) not null,-- 10个字符 不能为空
	sex char(1) default '男' check(sex='男' || sex='女'), -- 默认值 检查约束 
	age int(3) check(age>=18 and age<=50),
	enterdate date,
	classname varchar(10),
	email varchar(20) unique -- 唯一的约束
);

在建表后添加约束：

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_age check (age>=18 and age<=50);
alter table t_student add constraint ck_stu_sex check (sex="男"||sex="女");
alter table t_student add constraint uq_stu_email unique (email);

查看表结构：

desc t_student;

常见错误：

• 单独自增约束必定需要主键 否则会报错

create table t_student(
	sno int(6) auto_increment,-- 代表显示长度 不过多了也没事
	sname varchar(10),-- 是个字符
	sex char(1), -- 
	age int(3),
	enterdate date,
	classname varchar(10),
	email varchar(20)
);

错误：
Incorrect table definition; there can be only one auto column and it must be defined as a key
解决方法：去掉自增或者加上主键

外键约束

-- 外键约束
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,"python001",'r406');
insert into t_class values(null,"python002",'r408');
insert into t_class values(null,"c/c++001",'r108');

-- 一次性添加多条数据：
insert into t_class values (null,"python001",'r406'),(null,"python002",'r408'),(null,"c/c++001",'r108');

-- 查询班级表
select * from t_class;

-- 删除原先学生表
drop table t_student;

-- 创建学生表
create table t_student(
	sno int(6) primary key auto_increment,-- 代表显示长度 不过多了也没事
	sname varchar(10) not null,-- 10个字符 不能为空
	classno int(4) -- 取值参考t_class中的cno字段
);
insert into t_student values (null,"张三",1),(null,"李四",1),(null,"王五",2);
select * from t_student;


-- 出现问题
-- 1.添加一个学生班级为4   但是根本没有四班
insert into t_student values (null,"丽丽",4);

-- 2.想删除班级2：但是学生信息里面还没删除
delete from t_class where cno = 2;

-- 解决办法：添加外键约束
-- 注意 外键约束只有表级约束 没有列级约束
-- 1.在表内增加外键约束
create table t_student(
	sno int(6) primary key auto_increment,
	sname varchar(10) not null,-- 10个字符 不能为空
	classno int(4), -- 取值参考t_class中的cno字段
	constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
-- 2.在建表后增加外键爱约束
create table t_student(
	sno int(6) primary key auto_increment,
	sname varchar(5) not null,
	classno int(4)
)
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)

-- 添加学生
insert into t_student values (null,"张三",1),(null,"李四",1),(null,"王五",2);
-- 报错：
--  1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestyt`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
-- 因为没有这个班级2 所以不能添加 问题解决
insert into t_student values (null,"张三",1),(null,"李四",1),(null,"王五",3);
-- 删除班级1
delete from t_class where cno =1;
-- 报错不能删除 因为这个班级已经有人了
select * from t_student;
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestyt`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
-- 问题解决

外键策略：

-- 删除班级2：如果直接删除肯定时不行的 因为有外键约束
-- 加入外键策略

-- 策略1. no action 不允许删
-- 通过操作sql来完成
-- 先把班级二的班级改为其他班
update t_student set classno = null where classno =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 =1; -- class 和student都删了


-- 策略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 =9  where cno = 6; 
-- 然后班级为6的全部都置空了

-- 注意 策略二的级联操作和策略三删除操作可以混着使用：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update set null on delete set null;

-- 应用场合
（1） 朋友圈删除，点赞留言都删除 -- 级联操作
（2）解散班级，对应学生置空班级

t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno) on update set null on delete set null;

– 应用场合
（1） 朋友圈删除，点赞留言都删除 – 级联操作
（2）解散班级，对应学生置空班级