一.数据完整性的概念
1.什么是数据完整性
数据完整性是指存储在数据库中的数据能正确反映实际情况,规定输入的数据不能是无效值、错误值或者乱码等。
2.数据完整性的类型
① 实体完整性:标识符或者主键的完整性,使其唯一。
② 域的完整性:限制类型、格式和取值范围等。
③ 引用完整性:保持原表和引用表数据的一致性。
④ 自定义完整性:用户自定义的业务规则。
3.数据完整性的实现方式
实体完整性:主键约束、唯一值约束
域完整性:检查约束、默认值约束
引用完整性:外键约束
用户自定义完整性:以上约束的自由组合
4.什么是约束及Mysql常用约束
数据完整性约束也就是创建表(creat table)时的约束条件,是用来限制属性或者表中数据的,也可以通过修改语句(alter table)修改约束条件,或者删除表中属性的约束。
5.约束的分类
约束可分为行级约束和表即约束,两者本质上是一样的,只是约束的效果和范围不同。行级约束只对表中的一个属性列起作用,而表级约束可以对表中的多个属性列起作用。
非空、默认、自增列约束一般为行级约束:
主键(联合主键)、外键、唯一约束时标记约束:
二.完整性约束
1.非空约束:关键字:not null
创建非空约束的语法格式:
create table <table_name>(
name1 int not null,
name2 varchar(20) not null
);
#创建一个表,使编号和名称不能为空
eg:
create table staff( -- 员工表
staff_id int not null, -- 工号(不能为空)
staff_name char(30), -- 员工姓名
staff_gender char(4), -- 员工性别
staff_age int -- 员工年龄
);
insert into staff values(1,"张三","男",21); -- 正常运行
insert into staff (staff_name) values("李四"); -- Error 必需赋值工号
--对于原表可以修改(添加或去除)其中属性的约束
alter table staff modify column staff_name char(30) not null;--添加
alter table staff modify column staff_name char(30); --去除
alter table staff modify column staff_id int; --去除
--如果属性的取值已经存在空值,则不允许为这个属性添加非空约束:如果属性已经存在非空约束,则不允许为空值
2.唯一约束:指定某列或者某几列数据的取值不能重复。
(这里应为两部分,还有一个复合唯一约束,而实际上也是以复合唯一约束为主)
现在我们先来简单看看非符合唯一约束的简单概述。
关键字:unique
①创建表时添加唯一约束
create table teacher_tb(
t_no int unique,
t_name varchar(20)
);
②指定列添加唯一约束:
alter table <表名> add 约束类型(属性名);
#eg:可以通过constraint给约束名取别名
alter table <表名> add constraint <约束名> unique (属性名);
③删除唯一约束:
alter table <表名> drop index (属性名/约束名);
eg:
--创建一个数据表,使表中某个属性的取值不能重复
create table staff( -- 员工表
staff_id int unique, -- 工号(唯一)
staff_name char(30), -- 员工姓名
staff_gender char(4), -- 员工性别
staff_age int -- 员工年龄
);
--以修改表结构的方式,为某个属性添加唯一约束
alter table staff add unique(staff_id);
insert into staff values(1,"李四","男",22);
--运行此段会Error,因为在上文中插入工号为1的张三
insert into staff values(2,"李四","男",22);
--此段与上文不冲突,可成功运行
--以修改表结构的方式,去掉某个属性唯一约束
alter table staff drop index staff_id;
复合唯一约束(在唯一约束之上做些许更改)
创建复合唯一约束(表级约束)
可以对多个列进行唯一约束,约束两个及以上的字段(属性)不能同时一致。
①在创建表时添加复合唯一约束的方法
constraint <约束名> unique ( [属性1],[属性2]·····[属性n] );
②在创建表后添加复合唯一约束的方法
alter table <表名> add constraint <约束名> unique ( [属性1],[属性2]·····[属性n] ):
#这里也就是约束属性1、属性2到属性n中可以允许其中存在取相同值的属性,但也不能同时相同,至少要有一个不同。
#数据库默认第一个非空且唯一的属性为主键
--创建一个数据表,使表中某几个属性的取值不能同时重复
create table staff( -- 员工表
staff_id int, -- 工号(唯一)
staff_name char(30), -- 员工姓名
staff_gender char(4), -- 员工性别
staff_age int, -- 员工年龄
constraint uni_idname unique(staff_id,staff_name) --添加复合唯一约束
);
--关键字constraint用于给约束取别名(也可以不取,一般都要取)
--以修改表结构的方式,为某个属性添加复合唯一约束
alter table staff add constraint uni_idname unique(staff_id,staff_name);
--以修改表结构的方式,去掉某个属性唯一约束
alter table staff drop index uni_idname; --通过别名来删除约束
--如果属性的取值已经存在重复值,那么就不允许为这个属性添加唯一约束
三.主键约束
1.主键约束
主键约束就是非空+唯一约束,且每个表只能有一个主键约束(表级),在多列联合的主键约束时,联合主键的值不能同时重复。
关键字:primary key(表级)
①创建表时设置单列主键
create table <表名>(
属性名 数据类型 primary key, -- 设置主键约束
属性名 数据类型 not null
)
②设置多个属性为联合主键
alter table <表名> add [constraint <约束名>] primary key ( [属性1],[属性2]·····[属性n] ):
③删除主键约束
alter table < 表名> drop primary key; #直接删除主键
--创建一个数据表,使表中某属性成为主键
create table staff( -- 员工表
staff_id int primary key, -- 工号(唯一)
staff_name char(30), -- 员工姓名
staff_gender char(4), -- 员工性别
staff_age int -- 员工年龄
-- 或者primary key(staff_id) 该方式一般是添加联合主键时用到
);
--以修改表结构的方式,为某个属性添加主键约束
alter table staff add primary key(staff_id);
--如果属性(staff_id)的取值是空值或者重复,那么此段程序会报错
--以修改表结构的方式,去掉某个属性唯一约束
delete from staff where staff_id = 1 and staff_name = "李四"
--以修改表结构的方式,为某个属性添加联合主键约束
alter table staff add primary key(staff_id,staff_name);
2.自增列
自增列就是在插入数据时,如果不规定属性的值,其值就会根据上一个属性的值自动加1,如果上一个属性值为空或没有上一个属性,自增列会默认初始值为1
关键字:auto_increment(行级约束)
①创建表时添加自增列
create table <表名>(
id int primary key auto_increment,
);
②添加自增列
alter table <表名> modify column <属性名> <数据类型> auto_increment;
③删除自增列
alter table <表名> modify column <属性名> <数据类型>;
create table staff( -- 员工表
staff_id int primary key auto_increment, -- 工号(唯一)
staff_name char(30), -- 员工姓名
staff_gender char(4), -- 员工性别
staff_age int -- 员工年龄
)auto_increment = 100; -- 可以自主规定自增列的初始值为100(从100开始)
--以修改表结构的方式,为某个属性添加自增列约束
alter table staff modify column staff_id int auto_increment;
--以修改表结构的方式,去掉某个属性自增列约束
alter table staff modify column staff_id int not null;--此语句会删除这个属性的全部约束,如果需要保留某个约束则需在后面写上它的关键字,即如果不写"not null"那么在删除时会一起被删掉
------------------------------------------------------------分割线------------------------------------------------------------
接下来就不举例子了,与上面是大同小异的
3.默认值约束
给带有默认值约束的属性添加数据时,如果没有给定属性值,会自动添加一个默认值。
关键字:default
①在创建表时设置默认值约束
create table <表名>(
name varchar(20) not null
sex varchar(2) default '男'; --姓名默认为'男'
);
②添加/删除默认值约束
alter table <表名> modify column <属性名> <属性类型> [default <默认值>];
4.检查约束
顾名思义就是检查列中属性的取值范围,判断字段的值是否为指定的值,如果不是就不允许数据的插入或修改。
关键字:check
age int check (age>=0 and age<=150),--age约束在0到150之间
sex varchar(2) check(sex='男' or sex=’女‘)--sex约束为男或者女
--以修改表结构的方式为某个属性添加检查约束
alter table staff add constraint check_gender check(staff_gender="男" or staff="女");
--删除
alter table <表名> drop constraint <检查约束名>;
四.外键约束
外键约束是指一个表中的属性值参考另一个表中的主属性的值,也就是外键值参考主键值,由于外键约束时表级,所以在创建参考表之前要先创建被参考表
①创建表时建立外键约束
constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考表属性名>)
②添加外键约束
alter table <表名> add constraint <约束名> foreign key (<属性名>) references 被参考表(<被参考表属性名>);
③删除
alter table <表名> drop constraint <约束名>; --根据约束名删除约束
--外键约束用于描述实体集之间的联系,表示参考完整性,外键值参考其他表的主键
--在创建外键所在的参考表之前,需要先创建被参考表
eg:
--部门表(父表)
create table department_tb(
d_id int primary key auto_increment, --部门编号
d_name char(20) not null, --部门名称
d_number int default 0 --部门员工人数
)auto_increment = 100;
--插入任意部门表数据
insert into department_tb(d_name) values("行政部"),("人事部"),("财务部"),("生产部"),
("技术部"),("销售部");
--员工表
create table staff_tb(
staff_id int primary key auto_increment, --工号
staff_name char(30) not null, -- 姓名
staff_did int, --部门编号(外键:表示员工属于哪个部门)
staff_gender char(4) check(staff_gender = '男' or staff_gender = '女'), --性别
staff_age int check(staff_age >=18 and staff_age <= 60), --年龄
constraint for_staff_did foreign key(staff_did) references department_tb(d_id) --外键约束
)auto_increment = 1000;