对于数据库来说，关系型数据库对于保证数据完整性和一致性方面表现是最好的！

数据的完整性

完整性分类	具体描述	如何保证
实体完整性	每个实体都是独一无二的	主键 / 唯一索引（唯一约束）
参照完整性（引用完整性）	子表中不能有父表中没有的数据	外键
域完整性	数据都是有效的	数据类型和长度、非空约束、默认值约束、检查约束

数据的一致性：

数据的一致性通过事务实现 —> 一系列的对数据进行的不可分割的操作，要么全做，要么全部做

事务的ACID特性：

事物的特性	描述
Atomicity（原子性）	不可分割
Consistency（一致性）	事务前后数据状态保持一致
Isolation（隔离性）	多个并发的事务不知道彼此的中间状态
Duration（持久性）	事务完成后，对数据的修改要持久化

并发事务访问数据可能产生五大类的问题：

• 第一类丢失更新
• 第二类丢失更新
• 脏读（读脏数据）
• 不可重复读
• 幻读

如何解决这五类问题

• 单版本并发控制 - 锁 - 悲观锁 - 性能太差
• 多版本并发控制（MVCC）- 乐观锁 - 性能更好 - 需要冗余字段来支持

直接操作锁非常的麻烦，我们可以通过设置事务隔离级别的方式，让数据库自动选择合适的锁机制来保护数据。

事物的隔离级别	特点
read uncommitted	可以读到脏数据
read committed	Oracle默认的隔离级别 -不能避免不可重复读现象，但不会读到脏数据
repeatable read	避免不可重复读现象（ MySQL默认的事物隔离级别） 另一个事物更新数据，刚才查到的是什么，现在还是什么（查询事物没有提交和回滚之前都可以重复读）
serializable	此种方法不会出现并发数据的问题，但是效率地下，不建议使用

MySQL如何查看和修改事务隔离级别（transaction —> tx）

功能	方法
查看事物隔离级别	select @@tx_isolation
修改事物隔离级别	set session transaction isolation level read committed

在Linux环境下验证不同事物隔离级别的特性所需要使用到的命令

事物过程	相关命令
开事物	begin; / start transaction；
提交事物	commit；
回滚事物	rollback；（未提交之前，取消前面的操作，如更新或删除操作）

linux下面验证的场景举例：
数据库连接：mysql -u root -p

第一个实验场景：读脏数据

事物A：
begin；
update tb_emp set sal=2800 where eno = 1359;
事物B：
set session transaction isolation level read committed;
begin;
select * from tb_emp;       --->读脏数据
commit;
事物A:
rollback;

第二个实验场景：不可重复读

事物B：
set session transaction isolation level read committed;
begin;
select * from tb_emp where sal<3000;
事物A：
begin；
update tb_emp set sal=3800 where eno=1359;
commit;
事物B：
select * from tb_emp where sal<3000; --->没有1359对应的记录了，不可重复读
commit；

第三个实验场景：可以重复读

事物B：
set session transaction isolation level repeatable read;
begin;
select * from tb_emp where sal<3000;
事物A：
begin；
update tb_emp set sal=3800 where eno=1359;
commit;
事物B：
select * from tb_emp where sal<3000; --->还可以读到最开始使用select使用此命令读到的内容
commit；

数据完整性的概念

数据完整性是为了保证插入到数据库中的数据是正确的,防止用户可能的错误输入。

数据完整性分为实体完整性、域完整性、参照完整性。

实体(行)完整性

实体完整性中的实体指的是表中的行,因为一行记录对应一个实体。

实体完整性规定表的一行在表中是唯一的实体,不能出现重复。

实体完整性通过表的主键来实现。

主键关键字: primary key

主键特点: 不能为null，并且唯一。

主键分类:

​ 逻辑主键:例如ID,不代表实际的业务意义,只是用来唯一标识一条记录(推荐)

​ 业务主键:例如username，参与实际的业务逻辑。

主键使用方式:

​ 方式一:

Create table t1(

​	  id int primary key,

​	  name varchar(100)

);

插入数据：

insert into t1 values(1,’zs’);

insert into t1 values(2,’ls’);

主键自动增长:

关键字: auto_increment

下面是主键自动增长的例子

​		create table t4(

​			id int primary key auto_increment,

​			name varchar(100)

);

插入数据：

insert into t4(name) values(‘zs’);

insert into t4 values(null,’ls’);

域(列)完整性

域(列)完整性指数据库表的列（即字段）必须符合某种特定的数据类型或约束

​ 非空约束：not null

​ 唯一约束： unique

create table t5(

​			username varchar(100) not   null  unique,

​			gender varchar(100) not   null,

​			phonenum varchar(100)  unique

​	);

1. username 唯一 ，如果有重复就会出现错误：

 [Err] 1062 - Duplicate entry 'ls' for key 'username' 

2. 如果第二次插入代码insert into student3(username,gender,phone) VALUES('ls','男','112');

   就会出现错误：[Err] 1062 -关键字“用户名”的重复条目“ls”

3. gender 不能为 NULL，如果不输入就会出现错误：

Field 'gender' doesn't have a default value  

4. 执行下面这条代码insert into student3(username,gender,phone) values('ll','男','113');
   就会出现错误Duplicate entry 'll' for key 'username'

• 关键字“用户名”的重复条目“ll”

参照完整性**

参照完整性指的就是多表之间的设计,主要使用外键约束。

多表设计: 一对多、多对多、一对一设计

一对多

下面举例说明：
1.客户和订单的关系就是一对多，一个客户可以有多张订单,一张订单属于一个客户;

​ 创建客户表:

create table  customers(

​	id int,

​	name varchar(100),

​	address varchar(255),

​	primary  key(id)
);

创建订单表:

create table orders(

​	order_num int primary key,

​	price float(8,2),

​	status int,

​	customer_id int,

constainct  customer_id_fk foreign  key(customer_id) references customers(id)

);

外键约束: constraint customer_id_fk foreign key(customer_id) references customers(id);

注: constraint: 约束的意思。foreign key: 外键。references: 参照

创建一个名叫customer_id_fk的外键约束,其中外键指的是customer_id,并且参照的是 customers表中的id列。

图形界面操作的过程是：

子表的删除、更新策略

子表的删除更新策略一共有四种：

1. CASCADE 级联策略。

使用此种策略时主表的记录被删除或者主键字段被修改时会同步删除或修改子表

2. NO ACTION 无动作策略。

使用此种策略时要删除主表必须先删除子表，要删除主表的记录必须先删除子表关联的记录，不能更新主表主键字段的值。

3. RSTRICT 主表约束策略。

此种策略对主表的约束跟 NO ACTION 一样

4. SET NO 置空策略。

使用此种策略时，如果主表被删除或者主键被更改，则将子表中的外键设置为NULL。需要注意的是，如果子表的外键是主键或者是设置为NOT NULL的，则主表的删除和主键的更改跟 NO ACTION 一样。

多对多

老师和学生是多对多关系, 一个老师对应多个学生，一个学生被多个老师教

注意：

1. 需要创建第三张表，并且公共的字段，可以放入到第三张表格中。
2. 设置外键对应其他表格中的主键

创建老师表:

​	Create table teachers(

​		id int,

​		name varchar(100)

​		salary float(8,2),

​		primary key(id)

);

创建学生表:

Create table students(

​	id int,

​	name varchar(100),

​	grade varchar(100),

​	primary key(id)

);	

第三张表格：

Create table teacher_student(

​	t_id int,

​	s_id int,

​	primary key(t_id,s_id)

CONSTRAINT teacher_id_fk FOREIGN KEY(t_id) REFERENCES teachers(id),

CONSTRAINT student_id_fk FOREIGN KEY(s_id) REFERENCES students(id)

);

一对一

按照外键关联

​

1.在IdCard表中的外键添加唯一约束

按照主键关联，对主键添加外键约束

实际开发中直接设计一张表格就可以了。

注意：

1. 一般情况，设计一张表格即可；
2. 设计成两张表格：
   第二张表格中创建一个字段为外键（设置成唯一 unique）对应 第一张表格中的主键；

直接使用第二张表格中的主键为外键，对应第一张表格中的主键。

这篇文章讲的是数据库中的数据完整性约束，如有错误或不当之处，还望各位大神批评指正。

什么是数据完整性约束？

数据完整性：存储在数据库中的所有数据值均正确的状态。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性分为四类：
1. 实体完整性（实体完整性是对关系中的记录唯一性，也就是主键的约束。准确地说，实体完整性是指关系中的主属性值不能为Null且不能有相同值。定义表中的所有行能唯一的标识,一般用主键,唯一索引 unique关键字,及identity属性比如说我们的身份证号码,可以唯一标识一个人.。）
2. 域完整性（域完整性是对数据表中字段属性的约束，通常指数据的有效性,它包括字段的值域、字段的类型及字段的有效规则等约束，它是由确定关系结构时所定义的字段的属性决定的。限制数据类型,缺省值,规则,约束,是否可以为空,域完整性可以确保不会输入无效的值.。）
3. 参照完整性（参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。准确地说，参照完整性是指关系中的外键必须是另一个关系的主键有效值，或者是NULL。参考完整性维护表间数据的有效性,完整性,通常通过建立外部键联系另一表的主键实现,还可以用触发器来维护参考完整性）
4. 用户定义的完整性。

约束是表级的强制规定，有以下五中：not null，unique，primary key，foreign key，check 。

创建约束

非空约束（not null）

not null为列级约束，只能作用在列上
- 新建表std1，字段：id（非空），name（非空），score（可为空）

create table std1 (
     id number(6) constraint std1_id_nn not null ,
     name varchar(200) not null,
     score number(10,2)
)

这事如果在操作时定义为非空的字段为空，则会报错：

唯一约束（unique）

• 新建表std2，字段：id（唯一），name（非空），score（可为空）

create table std2 (
     id number(6) constraint std2_id_uk unique ,
     name varchar(200) constraint std2_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std2_email_uk unique(email)
)

这时，若有唯一约束的字段出现了重复值，则会报错：

注：unique允许多个空值

主键约束（primary key）

• 新建表std3，字段：id（主键），name（非空），score（可为空）

create table std3 (
     id number(6) constraint std3_id_pk primary key ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std3_email_uk unique(email)
)

--或者

create table std3 (
     id number(6) ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std3_email_uk unique(email) ,
     constraint std3_id_pk primary key(id)
)

此时若往主键字段插入空值则：

若主键重复则：

注：一个表只能有一个主键，主键非空且唯一

外键约束（foreign key）

• 新建表std4，字段：id（主键），name（非空），score（可为空），friend_id（外键关联std3.id）

create table std4 (
     id number(6) ,
     name varchar(200) constraint std4_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std4_email_uk unique(email) ,
     constraint std4_id_pk primary key(id) ,
     constraint std4_friend_id_fk foreign key(friend_id) references std3(id)
)

此时，若std4表中插入std3中不存在的id，则报错：

检查约束（check）

• 新建表std7，字段：id（主键），name（非空），score（值范围为0-100）

create table std7 (
     id number(6) ,
     name varchar(200) constraint std7_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std7_email_uk unique(email) ,
     constraint std7_id_pk primary key(id) ,
     constraint std7_score_ck check (score >= 0 and score <= 100)
)

此时如果添加数据不在check所约束的范围内则:

添加删除约束

可以往已知表中添加新的约束，使用ALTER TABLE - ADD(MODIFY)语句可以添加约束，

not null约束为列级约束必须使用modify

• 往表std1的score字段加入非空约束

alter table std1
modify(score number(10,2) not null)

• 删除表std1中id非空的约束

alter table std1
drop constraint std1_id_nn ;

• 给表std1的score加入检查约束，要求范围为1-100

alter table std1
add(constraint std1_score_ck check(score>=0 and score<=100)) ;

有效化无效化约束

可以将已有表中的约束有效化或无效化（disabled）。

• 将表std1中score字段的检查约束std1_score_ck 无效化

alter table std1
disable constraint std1_score_ck ;

此时检查约束将不再有效，可以添加不在check范围的数据。

• 将表std1中score字段的检查约束std1_score_ck 有效化

alter table std1
enable constraint std1_score_ck ;

此时约束重新生效。

级联删除与级联制空祥例

1. 级联删除（ON DELETE CASCADE）：当父表中的列被删除时，字表中相对应的列也被删除。
2. 级联制空（ON DELETE SET NULL）：当父表中的列被删除时，字表中相对应的列被制空。

表结构及初始数据

std3表结构：

create table std3 (
     id number(6) ,
     name varchar(200) constraint std3_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
--表级约束
     constraint std3_email_uk unique(email) ,
     constraint std3_id_pk primary key(id)
)

std3中数据如下：

std5表结构（外键级联删除）：

create table std5 (
     id number(6) ,
     name varchar(200) constraint std4_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std5_email_uk unique(email) ,
     constraint std5_id_pk primary key(id) ,
     constraint std5_friend_id_fk foreign key(friend_id) references std3(id) on delete cascade
)

std5中数据如下：

std6表结构（外键级联删除制空）：

create table std6 (
     id number(6) ,
     name varchar(200) constraint std6_name_nn not null,
     score number(10,2) ,
     email varchar(20) ,
     friend_id number(6) ,
--表级约束
     constraint std6_email_uk unique(email) ,
     constraint std6_id_pk primary key(id) ,
     constraint std6_friend_id_fk foreign key(friend_id) references std3(id) on delete set null
     )

std6中数据如下：

级联操作

• 此时删除std3中‘300001’号数据以后：

std5表：

std6表：

注：级联删除会删除相关联子表中的数据，而级联制空会将子表中相关联的数据制空而不会删除