数据库7大约束

蜗牛学苑_武汉

于 2024-06-20 16:36:17 发布

阅读量402

点赞数 3

分类专栏： Java开发 python测试开发文章标签：数据库

本文链接：https://blog.csdn.net/zhuge_long/article/details/139836831

版权

Java开发同时被 2 个专栏收录

41 篇文章 0 订阅

订阅专栏

python测试开发

15 篇文章 0 订阅

订阅专栏

4 约束

数据类型

什么是数据：数字、小说、图片、音乐、视频

针对不同的数据展现形式，我们对数据进行了分类，数据类型。

数字类型：存放数字的，int
浮点型类型：存放数字，float，小数
字符串类型：存放一切字符varchar
二进制类型：存放特殊数据
日期类型：只能存放日期类型

4.1 定义

约束是在符合数据类型的情况下，对数据的限制。

主键约束：empno（int）：1，2，3，4，1，2，3，4

4.2 分类

有七个，但是oracle严格意义上有七个，mysql严格意义上只有六个，但是我们通过一种手段让其实现七个。

非空约束：对一个列有要求，要求其不能为空值。
唯一约束：对一个列有要求，要求其值不能跟其他已经存在的值相同（注册邮箱，注册用户，手机号）
主键约束：当一列需要有非空约束，也需要有唯一约束的时候，我们直接使用主键约束就会同时满足非空和唯一（一个表的第一列）
默认值约束：对一个表的列设置一个值，如果在给表添加数据的时候，这个列你没有给值，就用设置的这个默认值
检查约束：对一个表的类的取值有规定：只能为什么或者什么（性别）
自增长：对一个表的列的值，自动在列历史最大值的基础上增加1（一般跟主键一起使用）
外键约束：一个表的取值，需要参考另外一个表的列的取值

4.3 非空约束

4.3.1 创建非空约束

-- 添加非空约束
-- 1)创建表的时候直接添加非空约束
-- create table tbname(...列 数据类型 not null,...);

-- 案例：创建一个班级表myclass（cid，cname，loc）,其中cid添加非空约束
create table myclass(cid int not null,cname varchar(20),loc varchar(20))

-- 验证非空约束是否生效
select * from myclass;
insert into myclass(cid, cname, loc) values(1001, '测试班', '教室二');
insert into myclass(cname, cid, loc) values('测试班', 1001, '教室二');
insert into myclass values(1001, '测试班', '教室二');

insert into myclass(cname, loc) values('开发班', '教室三'); -- [Err] 1364 - Field 'cid' doesn't have a default value
insert into myclass(cid, cname, loc) values(null, '开发班', '教室三'); -- [Err] 1048 - Column 'cid' cannot be null

-- 练习：创建学生表mystuden(sid,sname,cid),其中sid添加非空约束


-- 2)创建表的时候没有添加，创建完表，又想添加
-- ALTER table tbname change old_列 new_列 数据类型 not null;


-- 准备工作：
drop table if EXISTS myclass;  -- 如果存在这个表就删除这个表

-- 案例：创建一个班级表myclass（cid，cname，loc）,没有非空约束
create table myclass(cid int,cname varchar(20),loc varchar(20));

-- 给表中的cid列添加非空约束
ALTER table myclass change cid cid int not null;  -- alter修改表

-- 验证非空约束
insert into myclass values(1001, '测试班', '教室二'); -- 添加一条正常数据，证明表是ok
insert into myclass(cname, loc) values('开发班', '教室三'); -- [Err] 1364 - Field 'cid' doesn't have a default value
insert into myclass(cid, cname, loc) values(null, '开发班', '教室三') -- [Err] 1048 - Column 'cid' cannot be null

4.3.2 删除非空约束

-- 删除非空约束
-- alter table tbname change old_列 new_列 数据类型  null;

-- 案例：删除myclass中的cid上的非空约束
alter table myclass change cid cid int  null;


-- 验证约束是否删除成功
insert into myclass(cname, loc) values('开发班', '教室三'); -- 应该成功
insert into myclass(cid, cname, loc) values(null, '测试班', '教室4')  -- 应该成功
select * from myclass;

-- 练习：针对mystudent表（sid，sname, cid）,先创建表，然后添加sid的非空约束，然后删除约束并给出响应的验证

4.4 唯一性约束

4.4.1 创建唯一约束

# 2.唯一约束
# 作用：确保一张表中的一个列的取值没有重复的，确保唯一性
# 场景：一般用户名、手机号
# 1)添加唯一约束
-- 创建表的同时添加唯一约束
-- create table tbname (...,列名 数据类型  unique,...);  如果添加的时候没有给约束设置名字默认约束名为列名

-- 准备工作：
drop table if EXISTS myclass;

-- 案例：创建表myclass(cid，cname,loc)，其中cid添加唯一约束
create table myclass(
		cid int unique, 
		cname varchar(20), 
		loc varchar(20)
);

-- 验证唯一约束是否成功
insert into myclass values(1001,'测试班','教室二');
insert into myclass values(1001,'开发班','教室3'); -- 不应该成功：[Err] 1062 - Duplicate entry '1001' for key 'cid'  重复给cid键输入1001
select * from myclass;


-- 2）创建表的时候没有添加唯一约束，创建表之后，再添加唯一约束
-- alter table tbname add [CONSTRAINT uk_列名] unique(列);   # 中括号内的参数可以写也可以不写

-- 准备工作：
drop table if EXISTS myclass;
-- 案例：创建表myclass(cid，cname,loc)，创建完后，给cid列添加唯一约束
-- 创建表（没有唯一约束的）：
create table myclass(
		cid int, 
		cname varchar(20), 
		loc varchar(20)
);
-- 添加cid的唯一约束：
alter table myclass add unique(cid); -- 没有使用[CONSTRAINT uk_列名]，唯一约束的名字就默认是列名，如果使用了，就使用自定义的唯一约束名字

-- 验证唯一约束是否成功
insert into myclass values(1001,'测试班','教室二');
insert into myclass values(1001,'开发班','教室3'); -- 不应该成功：[Err] 1062 - Duplicate entry '1001' for key 'cid'  重复给cid键输入1001
select * from myclass;

-- ****************************** 给约束添加名字 ************************
-- 验证添加语法
-- 创建表的时候没有添加唯一约束，创建表之后，再添加唯一约束
-- alter table tbname add [CONSTRAINT uk_列名] unique(列);   # 中括号内的参数可以写也可以不写

-- 准备工作：
drop table if EXISTS myclass;
-- 创建表（没有唯一约束的）：
create table myclass(
		cid int, 
		cname varchar(20), 
		loc varchar(20)
);
-- 添加约束：alter table tbname add [CONSTRAINT 约束名字] unique(列);   # 中括号内的参数可以写也可以不写
alter table myclass add CONSTRAINT hahahahah unique(cid);

-- 验证唯一约束是否添加成功：
insert into myclass values(1001,'测试班','教室二');  
insert into myclass values(1001,'开发班','教室3'); -- 不成功：[Err] 1062 - Duplicate entry '1001' for key 'hahahahah'
select * from myclass;

-- 针对mystuent(sid,sname,cid)，练习唯一约束

4.4.2 删除唯一约束

-- 删除唯一约束
-- alter table tbname drop index 唯一约束的名字
alter table myclass drop index cid;


-- 验证：重新执行插入语句1001，可以出入成功
insert into myclass values(1001,'开发班','教室3');  -- 可以执行成功

4.5 主键约束

4.5.1创建主键约束

# 3.主键约束
# 作用：确保表中的某列的值是唯一且不为空
# 场景：一张表的第一列（习惯）所有跟编号相关都是主键

-- 1)创建表的时候添加主键约束--中间添加
-- create table tbname (列1 数据类型 PRIMARY key, 列2 数据类型..);

-- 案例：针对myclass（cid,cname,loc）创建表的同时，添加cid为主键
-- 准备工作：
drop table if EXISTS myclass;
-- 创建表：
create table myclass(
	cid int primary key,
	cname VARCHAR(20),
	loc VARCHAR(20)
);

-- 验证(既然主键的特性非空且唯一)：
insert into myclass(cid, cname, loc) VALUES(1001,'测试班','教室2');  -- cid=1001，第一次插入可以成功
insert into myclass(cid, cname, loc) VALUES(1001,'开发班','教室3');  -- cid=1001，第二次插入失败：[Err] 1062 - Duplicate entry '1001' for key 'PRIMARY
insert into myclass(cid, cname, loc) VALUES(null,'开发班','教室3');  -- cid=null，失败：[Err] 1048 - Column 'cid' cannot be null


-- 2)创建表的同时添加主键约束--最后添加
-- create table tbname (列1 数据类型 , 列2 数据类型.., PRIMARY key(列1));

-- 案例：针对myclass（cid,cname,loc）创建表的同时，添加cid为主键
-- 准备工作：
drop table if EXISTS myclass;
-- 创建表：
create table myclass(
	cid int,
	cname VARCHAR(20),
	loc VARCHAR(20),
	primary key(cid)
);

-- 验证(既然主键的特性非空且唯一)：
insert into myclass(cid, cname, loc) VALUES(1001,'测试班','教室2');  -- cid=1001，第一次插入可以成功
insert into myclass(cid, cname, loc) VALUES(1001,'开发班','教室3');  -- cid=1001，第二次插入失败：[Err] 1062 - Duplicate entry '1001' for key 'PRIMARY
insert into myclass(cid, cname, loc) VALUES(null,'开发班','教室3');  -- cid=null，失败：[Err] 1048 - Column 'cid' cannot be null


-- 3)创建表没有添加主键约束，创建完了才添加主键约束
-- alter table tbname add [CONSTRAINT 主键的名字] PRIMARY key(列);   # pk_列名

-- 案例：针对myclass（cid,cname,loc）创建表的同时，添加cid为主键
-- 准备工作：
drop table if EXISTS myclass;
-- 创建表(不添加约束)：
create table myclass(
	cid int,
	cname VARCHAR(20),
	loc VARCHAR(20)
);

-- 添加约束
alter table myclass add constraint pk_cid PRIMARY key(cid);

-- 验证：
insert into myclass(cid, cname, loc) VALUES(1001,'测试班','教室2');  -- cid=1001，第一次插入可以成功
insert into myclass(cid, cname, loc) VALUES(1001,'开发班','教室3');  -- cid=1001，第二次插入失败：[Err] 1062 - Duplicate entry '1001' for key 'PRIMARY
insert into myclass(cid, cname, loc) VALUES(null,'开发班','教室3');  -- cid=null，失败：[Err] 1048 - Column 'cid' cannot be null

4.5.2 删除主键约束

-- 4)删除主键约束
-- alter table tbname drop PRIMARY key;

-- 案例：删除myclass的主键约束
alter table myclass drop primary key;

-- 验证：
select * from myclass;  -- 此时有一条数据
insert into myclass(cid, cname, loc) VALUES(1001,'开发班','教室3'); -- 成功
insert into myclass(cid, cname, loc) VALUES(null,'开发班','教室3'); -- 失败，因为添加主键的时候，该列会同时满足唯一和非空，但是删除的时候只会删除唯一
alter table myclass change cid cid int null; -- 上面插入失败原因是因为非空约束，此时如果要插入成功需要去除非空约束

4.6 默认值约束

4.6.1 创建默认值约束

# 4.默认值约束
# 作用：给一个表中的某列设置一个初始值
# 场景：昵称（未知）、性别（男）一般都有默认值

-- 创建默认值约束
-- 1）创建表的时候添加默认值约束
-- create table tbname (...,列 数据类型 default 默认值,...);

-- 案例：针对myclass添加loc的默认值为：金融港六层
-- 数据准备
drop table if EXISTS myclass;
-- 创建表
create table myclass(
	cid int,
	cname VARCHAR(20),
	loc VARCHAR(20) default '金融港六层'
);

-- 验证（如果不给loc设置值，则默认使用金融港六层）：
insert into myclass (cid,cname,loc) values(1001,'测试班','教室2'); -- 插入成功，loc='教室2'
select * from myclass;
insert into myclass(cid, cname) values(1002,'开发班'); -- 插入成功，但是loc的值没有给，所以会使用默认值：金融港六层


-- 2）创建完表，再添加默认值约束
-- alter table tbname change old_列 new_列 数据类型 default 默认值;

-- 案例：针对myclass先创建表，然后添加loc的默认值为：金融港六层
-- 数据准备
drop table if EXISTS myclass;
-- 创建表
create table myclass(
	cid int,
	cname VARCHAR(20),
	loc VARCHAR(20)
);

-- 添加约束：
alter table myclass change loc loc VARCHAR(20) default '金融港六层';

-- 验证：
insert into myclass (cid,cname,loc) values(1001,'测试班','教室2'); -- 插入成功，loc='教室2'
select * from myclass;
insert into myclass(cid, cname) values(1002,'开发班'); -- 插入成功，但是loc的值没有给，所以会使用默认值：金融港六层

4.6.2 删除默认值约束

-- 3)删除默认值约束
-- alter table tbname change old_列 new_列 数据类型 default null;

-- 案例：删除myclass表loc的默认值约束
alter table myclass change loc loc varchar(20) default null; -- 默认值约束：null


-- 练习：在mystudent上添加默认值约束和删除默认值约束并验证

4.7 检查约束

4.7.1 创建检查约束

# ******检查约束 check()--mysql不支持
# 作用：控制表中的某个列的取值范围
# 场景：性别

-- 创建检查约束
-- 1）创建表的同时添加检查约束
-- create table tbname(...,列 set(a,b,c),....);

-- 案例：针对mystudent(sid,sname,ssex,cid)，其中ssex有检查约束
-- 数据准备：
-- 创建表：
create table mystudent(
	sid int,
	sname varchar(20),
	ssex set('男','女'),  -- set：集合   (1,2,3,4,'yyyy','ppppp')  
	cid int
);

-- 验证：
insert into mystudent(sid,sname,ssex,cid) values(101,'丁淳','男',1001); -- 插入成功，因为ssex='男'符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(102,'小红','女',1001); -- 插入成功，因为ssex='女'符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(103,'小中','中',1001); -- 插入失败，因为ssex='中'不符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(103,'小中',1,1001); -- 不可取这种方式，虽然1,2,3的值可以实现一定的效果
select * from mystudent;


-- 2）创建完表，添加检查约束
-- alter table tbname change old_列 new_列 set('男','女');

-- 案例：针对mystudent(sid,sname,ssex,cid)，创建完表之后，再给ssex添加检查约束
-- 数据准备：
drop table if EXISTS mystudent;
-- 创建表：
create table mystudent(
	sid int,
	sname varchar(20),
	ssex  varchar(20), 
	cid int
);

-- 添加约束
alter TABLE mystudent change ssex ssex set('男','女');

-- 验证：
insert into mystudent(sid,sname,ssex,cid) values(101,'丁淳','男',1001); -- 插入成功，因为ssex='男'符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(102,'小红','女',1001); -- 插入成功，因为ssex='女'符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(103,'小中','中',1001); -- 插入失败，因为ssex='中'不符合检查约束
insert into mystudent(sid,sname,ssex,cid) values(103,'小中',1,1001); -- 不可取这种方式，虽然1,2,3的值可以实现一定的效果
select * from mystudent;

4.7.2 删除检查约束

-- 3)删除检查约束
-- alter talbe tbname change old_列 new_列 数据类型;

-- 案例：删除mystudent中的ssex上的检查约束
ALTER table mystudent change ssex ssex varchar(20);

-- 验证：
insert into mystudent(sid,sname,ssex,cid) values(104,'小中','天大地大',1001); -- 插入成功
select * from mystudent;

4.9 自增长

4.9.1 创建自增长约束

# **********自增长
# 作用：一般跟主键一起使用，保证某个列的值自动递增
# 场景：淘宝订单（订单编号）

-- 创建自增长约束
-- 1）建表时添加自增长约束
-- create table tbname(...,列 数据类型 auto_increment primary key,....);

-- 案例：给myclass表中cid添加自增和主键

-- 准备数据：
drop table if EXISTS myclass;
-- 创建表
create table myclass(
	cid int primary key auto_increment,
  cname varchar(20),
  loc varchar(20)
);

-- 验证：
insert into myclass(cname,loc) values('test','武汉'); -- 插入成功，一张表中的第一条数据，其中cid没有给值，从1开始
-- 构造数据：
insert into myclass(cname,loc) values('test2','武汉');
insert into myclass(cname,loc) values('test3','武汉');
insert into myclass(cname,loc) values('test4','武汉');
insert into myclass(cname,loc) values('test5','武汉');
insert into myclass(cname,loc) values('test6','武汉');
insert into myclass(cname,loc) values('test7','武汉');
insert into myclass(cname,loc) values('test8','武汉');
insert into myclass(cname,loc) values('test9','武汉');
insert into myclass(cname,loc) values('test10','武汉');
insert into myclass(cname,loc) values('test11','武汉'); -- cid自动增长11
-- 删除10和11
delete from myclass where cid in(10,11);
-- 重新插入一条数据，cid的值应该为12
insert into myclass(cname,loc) values('test12','武汉'); -- cid自动为12
select * from myclass;


-- 2）建完表后添加自增长约束
-- ALTER table tbname change olc_列 new_列 数据类型 auto_increment;

-- 案例：创建myclass表，然后再给cid添加自增

-- 准备数据：
drop table if EXISTS myclass;
-- 创建表
create table myclass(
	cid int primary key,
  cname varchar(20),
  loc varchar(20)
);

-- 添加cid的自增长约束
alter table myclass change cid cid int auto_increment;

-- 验证：
insert into myclass(cname,loc) values('test','武汉'); -- 插入成功，一张表中的第一条数据，其中cid没有给值，从1开始
-- 构造数据：
insert into myclass(cname,loc) values('test2','武汉');
insert into myclass(cname,loc) values('test3','武汉');
insert into myclass(cname,loc) values('test4','武汉');
insert into myclass(cname,loc) values('test5','武汉');
insert into myclass(cname,loc) values('test6','武汉');
insert into myclass(cname,loc) values('test7','武汉');
insert into myclass(cname,loc) values('test8','武汉');
insert into myclass(cname,loc) values('test9','武汉');
insert into myclass(cname,loc) values('test10','武汉');
insert into myclass(cname,loc) values('test11','武汉'); -- cid自动增长11
-- 删除10和11
delete from myclass where cid in(10,11);
-- 重新插入一条数据，cid的值应该为12
insert into myclass(cname,loc) values('test12','武汉'); -- cid自动为12
select * from myclass;


-- 再次插入一条数据，该数据cid给值：99
insert into myclass(cid,cname,loc) values(99,'test12','武汉'); -- cid插入的值为99，所以最后cid是99
-- 再次插入一条数据，不给cid的值，那么cid的值为：100
insert into myclass(cname,loc) values('test100','武汉'); 
select * from myclass;

4.9.2 删除自增长约束

-- 3)删除自增长约束
-- ALTER table tbname change olc_列 new_列 数据类型 ;

-- 案例：删除mycalss的自增长约束
alter table myclass change cid cid int;

-- 验证：
insert into myclass(cname,loc) values('test101','武汉');  -- 插入失败，因为cid没有给值，cid的值默认是null，主键不允许：[Err] 1364 - Field 'cid' doesn't have a default value


-- 练习：在mystudent表中验证自增张的添加和删除

4.10 外键约束

A表中的某个列的取值需要参考B表中的主键

子表/从表中的某个列（外键）的取值需要参考主表中的主键

现在开发们已经不建议添加过多的外键（软件的业务复杂度）

4.10.1创建外键约束

# **** 外键约束
# 作用：约束一张表中的某个列的值的取值范围需要参考另外一张表的主键，防止冗余的垃圾数据
# 场景：淘宝登陆，用户名（user表 user_id），下订单（订单表order_id 主键     user_id）

-- 添加外键
-- 1）创建表的时候添加外键
-- create table 子表 (列1 数据类型，列2 数据类型，...,constraint 外键的约束名 foreign key (外键的列) REFERENCES 主表(主键)


-- 案例：给mystudent中的cid列添加外键约束（参考值为myclass表中的主键cid）

-- 数据准备：
drop table if EXISTS myclass ;
drop table if EXISTS mystudent ;
create table myclass(
	cid int PRIMARY key,
	cname VARCHAR(20),
	loc varchar(20)
);

-- 创建表+外键约束：
create table mystudent(
	sid int,
	sname VARCHAR(20),
	aaa int,
	constraint fk_aaa foreign key(aaa) REFERENCES myclass(cid)
);

-- 验证：
-- 给主表myclass添加数据
insert into myclass values(31,'test31','wuhan');
insert into myclass values(32,'test32','wuhan');
insert into myclass values(33,'test33','wuhan');
select * from myclass;
-- 给子表mystudent添加数据
insert into mystudent(sid,sname,aaa) values(101,'小明',31);  -- 插入成功
insert into mystudent(sid,sname,aaa) values(102,'小红',null);  -- 插入成功：外键并没有说不允许为空
insert into mystudent(sid,sname,aaa) values(103,'小黄',55);  -- 插入失败：[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`部门数据库`.`mystudent`, CONSTRAINT `fk_aaa` FOREIGN KEY (`aaa`) REFERENCES `myclass` (`cid`))


-- 2）创建完表，再添加外键
-- ALTER TABLE 从表 ADD CONSTRAINT fk_1 FOREIGN KEY (外键列) REFERENCES 主表(主键列)

-- 案例：创建mystudent表后，再给mystudent中的cid列添加外键约束（参考值为myclass表中的主键cid）

-- 数据准备：
drop table if EXISTS mystudent ;
drop table if EXISTS myclass ;
create table myclass(
	cid int PRIMARY key,
	cname VARCHAR(20),
	loc varchar(20)
);

create table mystudent(
  sid int,
	sname VARCHAR(20),
	cid int
);

-- 添加外键约束：
alter table mystudent add  foreign key(cid) references myclass(cid);  -- 没有给外键起名字

-- 验证：
-- 给主表myclass添加数据
insert into myclass values(31,'test31','wuhan');
insert into myclass values(32,'test32','wuhan');
insert into myclass values(33,'test33','wuhan');
select * from myclass;
-- 给子表mystudent添加数据
insert into mystudent(sid,sname,cid) values(101,'小明',31);  -- 插入成功
insert into mystudent(sid,sname,cid) values(102,'小红',null);  -- 插入成功：外键并没有说不允许为空
insert into mystudent(sid,sname,cid) values(103,'小黄',55);  -- 插入失败：[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`部门数据库`.`mystudent`, CONSTRAINT `fk_aaa` FOREIGN KEY (`aaa`) REFERENCES `myclass` (`cid`))

4.10.2 删除外键

-- 3)删除外键
-- alter table tbname drop foreign key fkname;  -- 外键的名字

-- 查看外键名
show create table mystudent; -- 结果如下
-- CREATE TABLE `mystudent` (
--   `sid` int(11) DEFAULT NULL,
--   `sname` varchar(20) DEFAULT NULL,
--   `cid` int(11) DEFAULT NULL,
--   KEY `yyyyy` (`cid`),
--   CONSTRAINT `yyyyy` FOREIGN KEY (`cid`) REFERENCES `myclass` (`cid`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 案例：删除mystudent表中的外键
-- 删除外键：
alter table mystudent drop foreign key yyyyy;

-- 验证：
insert into mystudent(sid,sname,cid) values(103,'小黄',55);   -- 插入成功
select * from mystudent;