MySQL基础3-约束

一. 约束概述

1.1 概念

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

1.2 目的

保证数据库中数据的正确、有效性和完整性

1.3 分类

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

二. 约束演示

案例：

根据需求，完成表结构的创建

演示

-- ------------------------------------------------- 约束演示 ---------------------------------------------------

-- 创建表students
create table students(
	id int primary key auto_increment comment 'ID',
	name varchar(10) not null unique comment '姓名',
	age int check(age > 0 and age <= 120) comment '年龄’，
	status char(1) default '1' comment '状态',
	gender char(1) comment '性别'
) comment '学生信息表';

-- 添加信息
insert into students(name, age, status, gender) values('李雷', 16, '1', '男'), ('韩梅梅', 16, '0', '女');

-- 添加第三条信息，将姓名设为null
insert into students(name, age, status, gender) values(null, 17, '1', '男');
# 提示"Column 'name' cannot be null", 姓名不能为空，因为该字段约束为not null

-- 再次添加信息，将姓名设为李雷
insert into students(name, age, status, gender) values('李雷', 16, '1', '男');
# 提示"Duplicate entry '李雷' for key 'students.name'", 因为'name'字段约束还有unique，所以不允许出现重复

-- 添加年龄大于120的数据
insert into students(name, age, status, gender) values('张三丰', 121, '1', '男');
# 提示"Check constraint 'student_chk_1' is violated.", 因为该字段有'check'约束

-- 添加一条没有status的数据
insert into student(name, age, gender) values('张三丰', 30, '男');

# 通过查看表的信息，虽然没有设置status的值，但表上给出了默认值'1'，因为该字段设置的约束为defaul '1'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

注：添加数据失败时，请求已经发出，所以id主键也是自动增长的

三. 外键约束

3.1 概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

3.2 语法

添加外键

CREATE TABLE 表名(
     字段名 数据类型
     …
     [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

三. 删除/更新行为

语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE [行为关键字] ON DELETE [行为关键字];

演示

创建表：

-- 创建emp表
create table emp(
	id int primary key auto_increment comment 'ID',
	name varchar(10) not null comment '姓名',
	age int check(age > 0 and age < 100) comment '年龄',
	job varchar(20) comment '职位',
	salary int comment '薪资',
	EntryDate date comment '入职时间',
	ManageRid int comment '直属领导ID',
	dept_id int comment '部门ID'
) comment '员工信息表';

-- 创建dept表
create table dept(
	id int auto_increment primary key comment 'ID',
	name varchar(20) not null comment '部门名称'
) comment '部门表';

-- dept插入数据
insert into dept(name) values ('研发部'),
 ('市场部'), ('财务部'), ('销售部'), ('总经办');
# c创建表时主键设置自增，所以这里不添加id，emp添加数据同理
 
-- emp插入数据
insert into emp(name, age, job, salary, EntryDate, ManageRid, dept_id)
values('宋若雨', 50, '总裁', 20000, '2000-01-01', null, 5),
      ('钱瑞阳', 28, '项目经理', 12500, '2010-08-10', 1, 1),
      ('李嘉欣', 26, '程序员', 8500, '2020-11-15', 2, 1),
      ('蔡楚楚', 25, '程序员', 8500, '2020-05-20', 2, 1),
      ('周子涵', 28, '程序员', 9000, '2019-04-02', 2, 1),
      ('王瑞杰', 25, '程序员', 6500, '2022-08-20', 2, 1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- no action(当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新, 与restrict一致)
-- 更新/删除演示(确保创建的外键已经删除)
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update no action on delete no action;
-- 测试
delete from dept where id = 1;
# 此时提示删除失败

-- restrict(当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新, 与no action一致)
# 删除外键
alter table emp drop foreign key fk_emp_dept_id;
# 添加行为
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update restrict on delete restrict;
# 测试
update dept set id = 6 where id = 1;
# 同上，修改失败

-- cascade(当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录)
# 删除外键
alter table emp drop foreign key fk_emp_dept_id;
# 添加行为
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update cascade on delete cascade;
# 测试1：修改dept表中的id
update dept set id = 6 where id = 1;
# 查看是否修改成功
select * from dept;	# 此时可以看到id为1的研发部此时id为6
# 查看emp表中的dept_id是否更新
select * from emp;	# 表中原本dept_id为1的也更新成6
update dept set id = 1 where id = 6;	#还原
# 测试2：删除dept表中id = 1的数据
delete from dept where id = 1;
select * from dept;	# 此时dept表中id为1的数据已经删除
select * from emp;	# 此时emp表中dept_id为1的数据也全部被删除

# 因为emp表中只剩下一条数据，于是将其删掉，参照前面创建表的步骤再次创建emp表并添加数据，这里不做演示
# 将dept删掉的数据补上
insert into dept(id, name) values(1, '研发部');

-- set null（当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null, 这就要求该外键允许取null值)
-- 添加行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;
# 测试1：修改
update dept set id = 7 where id = 1;	# 此时可以看到emp表中dept_id为1的地方都更新为null
update dept set id = 1 where id = 7;	# 改回来
update emp set dept_id = case when dept_id is null
then 1 when dept_id = 5 then 5 end;	# emp表也改回来
# 测试2：删除
delete from dept where id = 1;	# emp表中dept_id为1的地方更新为null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

注：图形化界面只需要右键想要添加外键或行为的表，关联到父表进行相关操作即可，在此不做演示