数据库中的数据完整性约束_数据库实体完整性约束

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

什么是数据完整性约束？

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

数据完整性分为四类：
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)
)
1
2
3
4
5

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

唯一约束（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)
)
1
2
3
4
5
6
7
8

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

注：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)
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

若主键重复则：

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

外键约束（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)
)
1
2
3
4
5
6
7
8
9
10
11

此时，若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)
)
1
2
3
4
5
6
7
8
9
10

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

添加删除约束

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

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

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

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

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

alter table std1
drop constraint std1_id_nn ;
1
2

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

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

有效化无效化约束

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

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

alter table std1
disable constraint std1_score_ck ;
1
2

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

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

alter table std1
enable constraint std1_score_ck ;
1
2

此时约束重新生效。

级联删除与级联制空祥例

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)
)
1
2
3
4
5
6
7
8
9

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
)
1
2
3
4
5
6
7
8
9
10
11

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
     )
1
2
3
4
5
6
7
8
9
10
11

std6中数据如下：

级联操作

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

std5表：

std6表：

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