Mysql-04-数据完整性的简单概述

一.数据完整性的概念

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;