一. 约束概述
1.1 概念
约束是作用于表中字段上的规则,用于限制存储在表中的数据
1.2 目的
保证数据库中数据的正确、有效性和完整性
1.3 分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
注:约束是作用于表中字段上的,可以在创建/修改表的时候添加约束
二. 约束演示
案例:
根据需求,完成表结构的创建
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
---|---|---|---|---|
id | ID唯一标识 | int | 主键,并且自动增长 | PRIMARY KEY(主键), AUTO_INCREMENT(自动增长) |
name | 姓名 | varchar(10) | 不为空,并且唯一 | NOT NULL(非空), UNIQUE(唯一) |
age | 年龄 | int | 大于0,并且小于等于120 | CHECK(检查) |
status | 状态 | char(1) | 如果没有指定该值,默认为1 | DEFAULT(默认) |
gender | 性别 | char(1) | 无 |
演示
-- ------------------------------------------------- 约束演示 ---------------------------------------------------
-- 创建表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'
注:添加数据失败时,请求已经发出,所以id主键也是自动增长的
三. 外键约束
3.1 概念
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
3.2 语法
添加外键
CREATE TABLE 表名(
字段名 数据类型
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
三. 删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null值)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)。 |
语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE [行为关键字] ON DELETE [行为关键字];
演示
创建表:文章来源:https://www.toymoban.com/news/detail-701687.html
-- 创建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);
-- 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
注:图形化界面只需要右键想要添加外键或行为的表,关联到父表进行相关操作即可,在此不做演示文章来源地址https://www.toymoban.com/news/detail-701687.html
到了这里,关于MySQL基础3-约束的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!