Oracle 19C入门到精通之数据完整性和约束性

数据库不仅仅存储数据，它还必须保证所有存储数据的正确性，因为只有正确的数据才能提供有价值的信息。如果数据不准确或不一致，那么该数据的完整性就可能受到破坏，从而给数据库本身的可靠性带来问题。为了维护数据库中数据的完整性，在创建表时常常需要定义一些约束。约束可以限制列的取值范围，强制设定列的取值来自合理的范围等。在Oracle系统中，约束的类型包括非空约束、主键约束、唯一性约束、外键约束、检查约束和默认约束。

对约束的定义既可以在CREATE TABLE语句中进行，也可以在ALTER TABLE语句中进行。在实际应用中，通常是先定义表的字段，然后根据实际需要通过ALTER TABLE语句为表添加约束。

1. 非空约束

非空约束就是限制必须为某个列提供值。空(NULL)值是不存在值，它既不是数字0，也不是空字符串，而是不存在的、未知的情况。

创建Books表，要求BookNo（图书编号）、ISBN和PublisherNo（出版社编号）不能为空值，代码如下：

create table Books

(

BookNo number(4) not null,         --图书编号，不为空

BookName varchar2(20),             --图书名称

Author varchar2(10),               --作者

SalePrice number(9,2),             --定价

PublisherNo varchar2(4) not null,  --出版社编号，不为空

PublishDate date,                  --出版日期

ISBN varchar2(20) not null         --ISBN，不为空

);

在创建完表之后，也可以使用ALTER TABLE…MODIFY语句为已经创建的表删除或重新定义NOT NULL约束。

--为Books表中BookName（图书名称）字段设置NOT NULL约束

alter table books modify bookname not null;

如果使用ALTER TABLE…MODIFY语句为表添加NOT NULL约束，并且表中该列数据已经存在NULL值，则向该列添加NOT NULL约束将失败。这是因为列应用非空约束时，Oracle会试图检查表中所有的行，以验证所有行在对应的列是否存在NULL值。

使用ALTER TABLE…MODIFY语句还可以删除表的非空约束，实际上也可以理解为修改某个列的值可以为空。

--删除Books表中关于BookName列的非空约束

alter table books modify bookname null;

2. 主键约束

主键约束用于唯一地标识表中的每一行记录。在一个表中，最多只能有一个主键约束，主键约束既可以由一个列组成，也可以由两个或两个以上的列组成（这种称为联合主键）。对于表中的每一行数据，主键约束列都是不同的，主键约束也具有非空约束的特性。

如果主键约束由一列组成，则该主键约束被称为行级约束；如果主键约束由两个或两个以上的列组成，则该主键约束被称为表级约束。若要设置某个或某些列为主键约束，通常使用CONSTRAINT…PRIMARY KEY语句来完成。

创建表Books_1，并为该表定义行级主键约束BOOK_PK（主键列为BookNo），代码如下：

create table Books_1

(

BookNo number(4) not null,              --图书编号

BookName varchar2(20),                  --图书名称

Author varchar2(10),                    --作者

SalePrice number(9,2),                  --定价

PublisherNo varchar2(4) not null,       --出版社编号

PublishDate date,                       --出版日期

ISBN varchar2(20) not null,             --ISBN

constraint BOOK_PK primary key (BookNo) --创建主键和主键约束

);

如果构成主键约束的列有多个（即创建表级约束），则多个列之间使用英文输入法下的逗号(,)分隔。

如果在创建表时未定义主键约束，用户可以使用ALTER TABLE…ADD CONSTRAINT…PRIMARY KEY语句为该表添加主键约束；

使用ALTER TABLE…ADD CONSTRAINT…PRIMARY KEY语句为Books表添加主键约束，代码如下：

alter table Books add constraint Books_PK primary key(BookNo);

在上述代码中，由于为PRIMARY KEY约束指定名称，因此必须使用CONSTRAINT关键字。如果要使用系统自动为其分配的名称（即不指定主键约束的名称），则可以省略CONSTRAINT关键字，并且在指定列的后面直接使用PRIMARY KEY标记即可。

创建表Books_2，并在BookNo列上定义一个由系统自动分配名称的主键约束，代码如下：

create table Books_2

(

BookNo number(4) primary key,  --图书编号，设置为由系统自动分配名称的主键约束

BookName varchar2(20),             --图书名称

Author varchar2(10),               --作者

SalePrice number(9,2),             --定价

PublisherNo varchar2(4) not null,  --出版社编号

PublishDate date,                  --出版日期

ISBN varchar2(20) not null         --ISBN

);

同样，也可以使用ALTER TABLE…ADD PRIMARY KEY语句添加由系统自动分配名称的主键约束，使用ALTER TABLE…ADD PRIMARY KEY语句为Books表中的BookNo列添加由系统自动分配名称的主键约束，代码如下：

alter table Books add primary key(BookNo);

删除PRIMARY KEY主键约束通常使用ALTER TABLE…DROP语句来完成。

--删除Books_1表中的主键约束BOOK_PK

alter table Books_1 drop constraint BOOK_PK;

3. 唯一性约束

唯一性约束强调所在的列不允许有相同的值。但是，它的定义要比主键约束弱，即它所在的列允许空值（但主键约束列是不允许为空值的）。唯一性约束的主要作用是在保证除主键列外，其他列值的唯一性。

在一个表中，根据实际情况可能有多个列的数据都不允许存在相同值。例如，各种“会员表”的QQ、E-mail等列的值是不允许重复的（但用户可以不提供，这样就必须允许为空值）。但是，由于在一个表中最多只能有一个主键约束存在，所以使用唯一性约束解决这种多个列都不允许重复数据存在的问题；通常使用CONSTRAINT…UNIQUE标记该列为唯一约束。

创建一个会员表Members，并要求为该表的QQ列定义唯一性约束，代码如下：

create table Members

(

MemNo number(4) not null,                 --会员编号

MemName varchar2(20) not null,            --会员名称

Phone varchar2(20),                       --联系电话

Email varchar2(30),                       --电子邮件地址

QQ varchar2(20) Constraint QQ_UK unique,  --QQ号，并设置为UNIQUE约束

ProvCode varchar2(2) not null,            --省份代码

OccuCode varchar2(2) not null,            --职业代码

InDate date default sysdate，             --入会日期

Constraint Mem_PK primary key (MemNo)     --主键约束列为MemNo

);

如果UNIQUE约束的列有值，则不允许重复，但是可以插入多个NULL值，即该列的空值可以重复。

除了可以在创建表时定义UNIQUE约束，还可以使用ALTER TABLE…ADD CONSTRAINT…UNIQUE语句为现有的表添加UNIQUE约束。

--为members表的email列添加唯一约束

alter table members add constraint Email_UK unique (email);

如果要为现有表中的多个列同时添加UNIQUE约束，则在括号内使用逗号分隔多个列。

使用ALTER TABLE…DROP CONSTRAINT语句可以删除UNIQUE约束。

--删除members表中Email_UK这个唯一约束

alter table members drop constraint Email_UK;

4. 外键约束

外键约束比较复杂，一般的外键约束会使用两个表进行关联（当然也存在同一个表自连接的情况）。外键是指“当前表”（即外键表）引用“另一个表”（即被引用表）的某个列或某几个列，而“另一个表”中被引用的列必须具有主键约束或者唯一性约束。在“另一个表”中，被引用列中不存在的数据不能出现在“当前表”对应的列中。一般情况下，当删除被引用表中的数据时，该数据也不能出现在外键表的外键列中。如果外键列中存储了被引用表中将要被删除的数据，那么对被引用表的删除操作将失败。

创建一个Class班级表，并向Class表插入数据，为Students表添加一个与Class表之间的外键约束，代码如下：

--创建Class表

create table Class

(

ClassNo varchar2(4) ,                     --班级编号

ClassName varchar2(20) not null,          --班级名称

Constraint Class_PK primary key (ClassNo) --主键约束列为ClassNo

);

--向Class表中插入数据

insert into class (classno,classname) values (1,'一班');

insert into class (classno,classname) values (2,'二班');

insert into class (classno,classname) values (3,'三班');

--为students表添加外键约束

alter table students

add constraint st_cl_fk

foreign key(classno)

references class(classno);  --创建外键约束，外键列为classno

如果外键表的外键列与被引用表的被引用列列名相同，则为外键表定义外键列时可以省略REFERENCES关键字后面的列名称，为students表添加外键约束代码也可写成如下：

alter table students

add constraint st_cl_fk

foreign key(classno)

references class;  --创建外键约束，外键列为classno

下面验证一下外键约束的有效性，向students表插入一条记录，设置classno列的值为class表中不存在的值，代码如下：

insert into students (stuno,stuname,sex,age,departno,classno,regdate) values (001,'小明','男',18,'1',5,sysdate);

可以看到class表中classno的值只有1、2、3，向students表中插入classno值为5的数据时外键约束生效，外键表students中的外键值必须存在于被引用表class中，否则该数值会因“违反完整约束条件”而无法被插入。

在定义外键约束时，还可以通过关键字ON指定引用行为的类型。当尝试删除被引用表中的一条记录时，通过引用行为可以确定如何处理外键表中的外键列，引用行为的类型包括以下3种：

在定义外键约束时，如果使用了关键字NO ACTION，那么当删除被应用表中被引用列的数据时将违反外键约束，该操作将被禁止执行，这也是外键的“默认引用类型”。

在定义外键约束时，如果使用了关键字SET NULL，那么当被引用表中被引用列的数据被删除时，外键表中外键列被设置为NULL，要使这个关键字起作用，外键列必须支持NULL值。

在定义外键约束时，如果使用了关键字CASCADE，那么当被引用表中被引用列的数据被删除时，外键表中对应的数据也将被删除，这种删除方式通常被称作“级联删除”，它在实际应用程序开发中得到比较广泛的应用。

下面通过一个实例来演示如何使用关键字CASCADE创建外键约束以及如何实现数据的级联删除操作。

创建新表class_temp（该表的结构复制自class表）和students_temp（该表的结构复制自students表），然后在class_temp表和students_temp表之间建立外键约束，并指定外键约束的引用类型为ON DELETE CASCADE，最后删除class_temp表和students_temp表都存在的外键值，具体步骤如下：

创建被引用表class_temp表和外键表student_temp表，并设置class_temp表的主键，代码如下：

--创建class_temp表

create table class_temp as select * from class;

--创建students_temp表

create table students_temp as select * from students;

--设置class_temp表的主键

alter talbe class_temp add primary key(classno);

在students_temp表和class_temp表之间创建外键约束，并指定外键约束的引用类型为NO DELETE CASCADE，代码如下：

alter table students_temp

add constraint temp_classno_fk

foreign key(classno)

references class_temp on delete cascade;

查看外键表students_temp中的数据，代码及结果如下：

select * from students_temp;

删除外键class_temp中classno为3的数据，代码如下：

delete class_temp where classno=3;

再次查询students_temp表中的数据，结果如下：

通过上图结果可以看出，由于指定了外键约束的引用类型为ON DELETE CASCADE，因此在删除被引用表class_temp中classno为3的记录时，系统也级联删除了students_temp表中所有classno为3的记录。

使用ALTER TABLE…DROP CONSTRAINT语句可以删除外键约束。

删除students_temp表和class_temp表之间的外键约束temp_classno_fk，代码如下：

alter table students_temp drop constraint temp_classno_fk;

5. 禁用和激活约束

创建约束之后，如果没有经过特殊处理，约束会一直起作用。但也可以根据实际需要，临时禁用某个约束。当某个约束被禁用后，该约束就不再起作用了，但它还存在于数据库中。

为什么要禁用约束呢？这是因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件。当执行一些特殊操作时，例如使用SQL* Loader从外部数据源向表中导入大量数据，并且事先知道这些数据是满足约束条件的，此时为提高运行效率，就可以禁用这些约束。

禁用约束操作不但可以对现有的约束执行，而且可以在定义约束时执行。

5.1. 在定义约束时禁用

在使用CREATE TABLE或ALTER TABLE语句定义约束时（默认情况下约束是激活的），如果使用关键字DISABLE，则约束是被禁用的。

创建一个学生信息表(Student)，并为年龄列(Age)定义一个DISABLE状态的CHECK约束（要求年龄值为8～30），代码如下：

create table Student

(

StuCode varchar2(4) not null,

StuName varchar2(10) not null,

Age int constraint Age_CK check (age > 8 and age 

Province varchar2(20),

SchoolName varchar2(50)

);

5.2. 禁用已经存在的约束

对于已存在的约束，可以使用ALTER TABLE…DISABLE CONSTRAINT语句禁止该约束。

--禁用students_temp表中的约束temp_classno_fk

alter table students_temp disable constraint temp_classno_fk;

在禁用主键约束时，Oracle会默认删除约束对应的唯一索引，而在重新激活约束时，Oracle将会重新建立唯一索引。如果希望在删除约束时保留对应的唯一索引，可以在禁用约束时使用关键字KEEP INDEX（通常放在约束名称的后面）。

在禁用唯一性约束或主键约束时，如果有外键约束正在引用该列，则无法禁用唯一性约束或主键约束。这时可以先禁用外键约束，然后禁用唯一性约束或主键约束；或者在禁用唯一性约束或主键约束时使用CASCADE关键字，这样可以级联禁用这些列的外键约束。

禁用约束只是一种暂时现象，在特殊需求处理完毕之后，还应该及时激活约束。如果希望激活被禁用的约束，可以在ALTER TABLE语句中使用ENABLE CONSTRAINT子句。激活约束的语法格式如下：

ALTER TABLE table_name

ENABLE [NOVALIDATE | VALIDATE] CONSTRAINT con_name;

table_name：表示要激活约束的表的名称。

NOVALIDATE：该关键字表示在激活约束时不验证表中已经存在的数据是否满足约束，如果没有使用该关键字，或者使用VALIDATE关键字，则在激活约束时系统将验证表中的数据是否满足约束的定义。

禁用Books_1表中的主键BOOK_PK，然后重新激活该约束，具体步骤如下：

使用ALTER TABLE语句禁用BOOK_PK主键约束，代码如下：

alter table books_1 disable constraint BOOK_PK;

在Books_1表中插入两行数据，并且这两行数据的bookno列的值相同（如9999），代码如下：

insert into books_1(bookno,publisherno,isbn) values(9999,'东方','123456');

insert into books_1(bookno,publisherno,isbn) values(9999,'东方','777777');

由于在禁用BOOK_PK主键之后，不受主键约束条件的限制，因此可以给bookno列添加重复值。

使用ALTER TABLE语句激活BOOK_PK主键约束，代码如下：

alter table books_1 enable constraint BOOK_PK;

由于bookno列的现有值中存在重复的情况，这与主键约束的作用存在冲突，因此激活约束的操作一定是失败的。对于这种情况的解决方法，通常是更正表中不满足约束条件的数据。

6. 删除约束

可以使用带DROP CONSTRAINT子句的ALTER TABLE语句删除约束。删除约束与禁用约束不同，禁用的约束是可以被激活的，但是删除的约束在表中就完全消失了。使用ALTER TABLE语句删除约束的语法格式如下：

ALTER TABLE table_name

DROP CONSTRAINT con_name;

table_name：表示要删除约束的表名称。

con_name：表示要删除的约束名称。

--删除student表中所创建的check约束Age_CK

alter table Student drop constraint Age_CK;