章节14:完整性约束之非外键约束
【1】表的完整性约束
1.为什么需要完整性约束
为防止不符合规范的数据存入数据库,在用户对数据进行插入、修改、删除等操作时,MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件,以保证数据库中数据的准确性和一致性,这种机制就是完整性约束。
例如,在之前的添加数据中,我们添加了学号相同的几条数据,这显然是不符合实际情况的,因为学号应当是唯一的。然而,我们的添加操作成功了,为什么呢?这就是因为,我们缺少了完整性约束。
2.MySQL中完整性约束目前主要有7种
MySQL中主要支持以下7种完整性约束,如图所示。 其中Check约束是MySQL8中开始提供的支持。
3.列级约束与表级约束
约束从作用上可以分为两类:列级约束和表级约束(往下看,下文会解释)
【2】非外键约束
前6种约束比较简单,本文先介绍除外键约束外的约束,统称为非外键约束
1.实例演示非外键约束(以列级约束写法为例)
例:建立一张用来存储学生信息的表
字段包括学号、姓名、性别、年龄、入学日期、班级、email等信息
本例约束(要求)如下:
- 以学号作为主键(非空且唯一),主键的作用:可以通过主键查到唯一的一条记录【主键约束primary key】
- 如果主键是整数类型,那么需要自增【自增约束auto_increment】
- 姓名不能为空【非空约束not null】
- email唯一(不一定非空)【唯一约束unique】
- 性别默认值是男【默认值约束default】
- 性别只能是男女【检查约束check】
- 年龄只能在18-50之间【检查约束check】
代码演示:
-- 创建数据库表:
create table t_student(
sno int(6) primary key auto_increment,##学号
-- 1.primary key 主键约束
-- 2.auto_increment 自增约束,每次插入新数据会自动增加,确保不会重复
sname varchar(5) not null, ##姓名
-- 3.not null 非空约束
sex char(1) default '男' check(sex='男' || sex='女'),
-- 4.default 默认值约束
age int(3) check(age>=18 and age<=50),
-- 5.check 检查约束(性别只能是男或女,年龄只能在18-50之间)
enterdate date,
classname varchar(10),
email varchar(15) unique
-- 6.unique 唯一约束
补充说明,关于 自增约束:
每次插入一条数据,主键就自增一次,即使这次插入失败报错
所以可能会出现主键不连续的情况(这仍然不违反主键唯一且非空)
2.那么表级约束是什么意思呢
- 列级约束:包含在列(字段)定义中,直接跟在该字段的其他定义之后,用空格分隔;
- 表级约束:可以约束表中任意一个或多个字段。与列定义相互独立,不包含在列定义中;与定义用‘,’分隔;必须指出要约束的列的名称;(需要特别说明的是,只有4种约束能写在表级约束里:主键、外键、唯一、检查)
表级约束代码演示:
-- 删除上表:
drop table t_student;
-- 创建数据库表:
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 是约束的名字
-- 1.给约束取名字的好处是,报错的时候会直接说明是哪个约束未满足,能够更快找到错误的地方
-- 2.也可以不给约束取名,这个时候可以省去constraint,例如下面这一句
check (sex = '男' || sex = '女'),
constraint ck_stu_age check (age >= 18 and age <= 50),
constraint uq_stu_email unique (email)
);
3.已经创建表之后怎么添加/删除约束
代码演示:
-- 删除表:
drop table t_student;
-- 创建数据库表:
create table t_student(
sno int(6),
sname varchar(5),
sex char(1),
age int(3),
enterdate date,
classname varchar(10),
email varchar(15)
);
-- 在创建表以后添加/删除约束:
-- 添加/删除 主键约束、唯一约束、检查约束语法相似
-- 添加主键约束
alter table t_student add constraint pk_stu primary key (sno) ;
-- 删除主键约束,表级约束添加语法为drop constraint(+约束名)+具体约束
alter table t_student drop constraint pk_stu primary key (sno) ;
-- 添加唯一约束
alter table t_student add constraint uq_stu_email unique (email);
-- 删除唯一约束
alter table t_student drop constraint uq_stu_email unique (email);
-- 添加检查约束
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
-- 删除检查约束
alter table t_student drop constraint ck_stu_sex check (sex = '男' || sex = '女');
-- 添加非空约束
alter table t_student modify email varchar(15) not null;
-- 删除非空约束(后面加null的意思是可以为空,而不是必须是空)
alter table t_student modify email varchar(15) null;
-- 添加默认值约束
alter table t_student alter column sex default '男';
-- 删除默认值约束
alter table t_student alter column sex drop default;
-- 添加自增约束(必须先为其定义主键约束、外键约束或唯一约束)
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 modify sno int(6);
【3】非外键约束总结
1.主键约束
主键约束(PRIMARY KEY,缩写PK),是数据库中最重要的一种约束,其作用是约束表中的某个字段可以唯一标识一条记录。因此,使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等,设置为主键的字段取值不能重复(唯一),也不能为空(非空),否则无法唯一标识一条记录。 (设定了主键约束之后,就已经设定了唯一+非空,不用再额外写unique和not null)
主键可以是单个字段,也可以是多个字段组合(例如: PRIMARY KEY(id, name)就是以id与name作为联合主键)。对于单字段主键的添加可使用表级约束,也可以使用列级约束;而对于多字段主键的添加只能使用表级约束。
2.唯一约束
唯一约束(UNIQUE,缩写UK)比较简单,它规定了一张表中指定的某个字段的值不能重复,即这一字段的每个值都是唯一的。如果想要某个字段的值不重复,那么就可以为该字段添加为唯一约束。
3.检查约束
检查约束(CHECK)用来限制某个字段的取值范围(MySQL8开始支持检查约束)。
4.非空约束
非空约束(NOT NULL,缩写NK)规定了一张表中指定的某个字段的值不能为空(NULL)。设置了非空约束的字段,在插入的数据为NULL时,数据库会提示错误,导致数据无法插入。
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(即使插入数据时报错,也会加1,下次插入时,就会跳过刚刚本来分配给错误数据的主键,即造成不连续)。