Oracle增加完整性约束,oracle中的完整性约束

一、约束的查询和相关操作

Oracle对数据库完整性的约束：

三种方法维护数据完整性：ORACLE完整性约束，数据库触发器，应用程序代码。

应尽量使用ORACLE完整性约束，可靠性和效率高，容易修改，使用灵活，记录在数据字典。

ORACLE五种约束：

非空 not null,定义 的列不能为空。只能在列级定义

唯一，unique，表中每一行所定义 的列或列值不能相同

主键primary key 不能包括空值，主键唯一标识表中每一行，一列或几列组合为主键

外键foreign key 指明一列或几列的组合为外键以维护从表chilD table和主表 parent table

之间的引用完整性referential integrity

条件约束check，表中每一行要满足约束条件。约束加上表上，创建表时可以定义

1.查询约束

查询表中是否有约束并显示约束名：显示表列所对应的约束的信息

BYS@bys1>select

a.constraint_name, a.column_name from user_cons_columns a,

user_constraints b where a.table_name = upper('&AA');

Enter value for aa: emp

old 1: select a.constraint_name, a.column_name from

user_cons_columns a, user_constraints b where a.table_name =

upper('&AA')

new 1: select a.constraint_name, a.column_name from

user_cons_columns a, user_constraints b where a.table_name =

upper('emp')

CONSTRAINT_NAME COLUMN_NAME

------------------------------ ---------------

PK_EMPNO EMPNO

查表中是否有主键约束

BYS@bys1>select

a.constraint_name, a.column_name from user_cons_columns a,

user_constraints b where a.constraint_name = b.constraint_name and

b.constraint_type = 'P' and a.table_name

=upper('&table_name');

Enter value for table_name: emp

old 1: select a.constraint_name, a.column_name from

user_cons_columns a, user_constraints b where a.constraint_name =

b.constraint_name and b.constraint_type = 'P' and a.table_name

=upper('&table_name')

new 1: select a.constraint_name, a.column_name from

user_cons_columns a, user_constraints b where a.constraint_name =

b.constraint_name and b.constraint_type = 'P' and a.table_name

=upper('emp')

CONSTRAINT_NAME COLUMN_NAME

------------------------------ ---------------

PK_EMPNO EMPNO

查当前用户下的所有约束的信息

BYS@bys1>col owner for

a10

BYS@bys1>col table_name for

a10

BYS@bys1>select * from

user_cons_columns;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION

---------- ------------------------------ ----------

--------------- ----------

BYS PK_OBJ_ID TEST2 OBJECT_ID 1

BYS SYS_C0011203 TEST1 OBJECT_ID 1

BYS PK_EMPNO EMP EMPNO 1

查询当前用户的相关约束的状态信息，可以查dba_constraints或USER_constraints

BYS@bys1>select

constraint_name,table_name,constraint_type,status,deferrable,deferred,validated

from dba_constraints where owner='BYS';

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED

VALIDATED

------------------------------ ---------- - -------- --------------

--------- -------------

SYS_C0011203 TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE

VALIDATED

PK_EMPNO EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_OBJ_ID TEST2 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SCOTT@bys1>select

constraint_name,table_name,constraint_type,status,deferrable,deferred,validated

from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS DEFERRABLE DEFERRED

VALIDATED

------------------------------ ---------- - -------- --------------

--------- -------------

FK_DEPTNO EMP R ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_DEPT DEPT P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

PK_EMP EMP P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

SYS_C0011265 TEST P ENABLED NOT DEFERRABLE IMMEDIATE

VALIDATED

PK_A TEST1 P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

2.增加、删除、修改约束

增加与修改：

增删主键及外键

alter table dept add constraint pk_dept primary key(deptno);

alter table dept2 add primary key(dname);

不指定约束名，则由系统自动命令约束名。

alter table emp add constraint fk_deptno foreign key(deptno)

references dept(deptno) on delete cascade;

alter table emp add constraint fk_deptno foreign key(deptno)

references dept(deptno);

注：on delete cascade和on delete set null的作用是用来处理级联删除问题的，

如果你需要删除的数据被其他数据所参照(主、外键)，那么你应该决定到底希望oracle怎么处理那些参照这些即将要删除数据的数据的.

你可以有三种方式：

禁止删除，这也是oracle默认的。

将那些参照本值的数据的对应列赋空，使用on delete set

null关键字。即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被修改为NULL。

将那些参照本值的数据一并删除，使用on delete

cascade关键字，即删除本表的列中的值，对本列有外键引用的表的相应的列有相应值行会被删除

增加CHECK 约束:

alter table dept add constraint valid_deptno check

(deptno<5000); DEPTNO列的值需要少于5000

SQL>alter table customer add constraint abc check (address in

(’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’));

增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；

增加惟一约束

alter table customer add constraint aaa unique(cardId);

增加非空约束--注意增加非空约束时用的是关键字modify，其它四种约束都是ADD

SQL>alter table dept modify dname not null;

删除

对非空约束的删除：因为非空约束不能指定约束名，应该先通过查询表和列所对应的的约束信息，找出约束名，再删除。如下：

BYS@bys1>alter table

dept drop constraint SYS_C0011725;

修改约束--大可以删除了重建

alter table dept2 modify constraint pk_d2 initially

immediate;

删除指定名字的约束---可以是主键、外建或其它约束的名字

alter table emp drop constraint PK_EMPNO;

alter table scott.event drop constraint evtid_pk;

删除其它用户的约束--需要DBA权限

不用指定约束名，直接删除表中主键

altertable emp drop primary key cascade;

删除主键约束的时候，如果在些主键上的外键创建时未指定on delete

cascade参数，直接删除主键报错，要加上cascade参数。

Oracle约束constraint是我们经常使用的一种数据库规则对象。constraint在数据库中的作用就是从静态角度对数据完整性进行维护。我们经常使用的主键primary

key和外键foreign key，本质上就是约束的一种形式。

对Oracle的约束，我们有三个属性可以进行设置，分别为deferrable、deferred和validated。针对不同的需求设计场景，采用不同类型的属性，可以帮助我们实现不同的约束效果。下面我们分别来进行实验。

1、 环境准备

首先我们还是准备数据实验环境。

SQL> create table t (id number);

Table created

SQL> alter table T

2 add

constraint c_t_id1

3 check

(id>5);

Table altered

我们创建了数据表T，在列id上添加了约束c_t_id1。约束内容很简单，就是要求id值保证是大于5。约束c_t_id1使用的是默认选项，数据字典中对该约束的表示如下：

SQL> select constraint_name, constraint_type

ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED,

VALIDATED from dba_constraints where

table_name='T' and wner='SYS';

CONSTRAINT_NAME CTYPE

COND STATUS DEFERRABLE DEFERRED VALIDATED

-------------------- ----- ---------- --------

-------------- --------- -------------

C_T_ID1 C id>5 ENABLED NOT DEFERRABLE IMMEDIATE

VALIDATED

注意，此时约束的三个属性取值分别为：deferrable: not

deferrable；deferred：immediate；validated：validated；

我们观察一下此时数据表的插入现象：

SQL> insert into t values (1);

//插入非法的数据；

insert into t values (1)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

//立刻报错，将数据剔除！

SQL> insert into t values (6);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from t;

ID

----------

6

结论：在默认情况下，Oracle的约束是不允许延迟(not

deferrable)、立即应用和验证的(immediate、validated)。在数据变化的时候，立即进行约束验证。

2、 deferrable：约束应用可以延迟

deferrable默认值为not

deferrable，字面含义是不可延迟。那么我们如果设置可以延迟，效果是什么呢？

SQL> alter table T

2 drop

constraint C_T_ID1;

Table altered

SQL> alter table T

2 add

constraint C_T_ID1

3 check

(id>5)

4 deferrable;

Table altered

此时，数据字典中的情况是如下：

SQL> select

constraint_name, SEARCH_CONDITION cond, STATUS,

DEFERRABLE, DEFERRED, VALIDATED from

dba_constraints where table_name='T' and wner='SYS';

CONSTRAINT_NAME CTYPE STATUS DEFERRABLE DEFERRED VALIDATED

-------------------- ----- -------- -------------- --------- -------------

C_T_ID1 C ENABLED DEFERRABLE IMMEDIATE VALIDATED

与默认情况相比，deferrable属性变化为了deferrable。我们观察一下现象：

SQL> insert into t values (3);

insert into t values (3)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

在插入数据的时候，立即进行约束验证。和默认情况下没有差异。那么怎么处理呢？

//手工设置deferred属性为deferred

SQL> set constraint c_t_id1

deferred;

Constraints set

SQL> insert into t values (3);

//此时插入数据时候，并不进行验证操作了。

1 row inserted

SQL> insert into t values (7);

1 row inserted

SQL> commit;

commit

ORA-02091: 事务处理已回退

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

//直到进行commit的时候，才会应用约束；

那么，如何设置回原有的属性呢？

SQL> set constraint c_t_id1

immediate;

Constraints set

SQL> insert into t values (4);

//又恢复插入立刻检查约束的状态了？

insert into t values (4)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

结论：单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后，约束检查延后是不可以直接使用的，要配合deferred参数，如果该参数是immediate，那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred，约束就是在事务commit提交的时候应用，出现错误就连带回滚rollback整个事务。

3、 deferred：是否进行延迟应用

从上面的实验中，我们可以看出deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后，可以通过set

constraints进行deferred属性的设置，来确定约束应用时点。

本部分确定deferred定义的方式和与deferrable属性的关系。是可以在定义约束是使用initially关键字来确定约束的deferred属性取值。

SQL> alter table T

2 add

constraint C_T_ID1

3 check

(id>5)

4 deferrable

initially deferred;

Table altered

set

constraints语句只有在约束的deferrable属性设置为deferrable的时候才可以使用。

SQL> alter table T

2

drop

constraint C_T_ID1;

Table altered

SQL> alter table T

2 add

constraint C_T_ID1

3 check

(id>5)

4 ;

Table altered

SQL> set constraint c_t_id1

deferred;

set constraint c_t_id1 deferred

ORA-02447: 无法延迟不可延迟的约束条件

4、 disable禁用约束和validate验证约束

disable与validate的关系很紧密，相互制约影响。我们观察下面的实验：

SQL> alter table t disable constraint

c_t_id1;

Table altered

SQL> select

constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE,

DEFERRED, VALIDATED from dba_constraints where

table_name='T' and wner='SYS';

CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED

---------------- ---------- --------

-------------- --------- -------------

C_T_ID1 id>5 DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

通过disable

constraint语句，可以对一个约束进行禁用操作。禁用disable下的约束，validated属性是not

validate，也就不起作用的。

SQL> select * from t;

ID

----------

6

2

4

86

数据表约束禁用后，数据完整性被破坏。此时，如果我们直接进行约束启用。

SQL> alter table t enable constraint

c_t_id1;

alter table t enable constraint

c_t_id1

ORA-02293: 无法验证 (SYS.C_T_ID1) -

违反检查约束条件

SQL> select

constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE,

DEFERRED, VALIDATED from dba_constraints where

table_name='T' and wner='SYS';

CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED

---------------- ---------- --------

-------------- --------- -------------

C_T_ID1 id>5 DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

在约束被启用的时候，会自动进行检验。如果数据不满足条件，Oracle不会开启该约束引用。

enable和validate可以配合使用enable

novalidate子句使用。

SQL> alter table t enable novalidate

constraint c_t_id1;

Table altered

SQL> select

constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE,

DEFERRED, VALIDATED from dba_constraints where

table_name='T' and wner='SYS';

CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED

---------------- ---------- --------

-------------- --------- -------------

C_T_ID1 id>5 ENABLED NOT DEFERRABLE IMMEDIATE NOT

VALIDATED

此时，只是针对现有T中数据不进行验证，对新增加变化的数据，同样会进行验证。

SQL> insert into t values (45);

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t values (3);

insert into t values (3)

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

5、结论

默认情况下，Oracle

constraint是不开启延迟约束和原有数据保留验证的。那么在什么样的场景下，我们可以考虑使用这些特性呢？

批量数据导入、物化视图刷新的时候，事务量很大，而且存在数据规律前后颠倒的情况。此时，如果开启着立即约束应用的开关，可能存在一些暂时性的约束不满足的情况，从而影响到整个系统的运行。开启约束延迟验证，就可以帮助我们解决这个问题；

历史数据移植。历史数据存在不规则的情况，很多时候难以满足我们新系统的数据完整性要求。可以使用not

validate的方式，对历史数据不进行约束控制，而只针对新数据开启管理；

三、oracle级联操作

oralce 里能直接设置是否级联删除，

如果需要级联更新，需要用触发器来实现，同时需要设置deferrable

not deferrable | deferrable [ initially { immediate | deferred }

]

deferrable：这个选项控制该约束推迟到事务的结尾。如果前面加not,就是相反的操作了。

initially immediate：立即检查约束，在每条语句(结尾)检查约束，这是缺省值。相反，还有一个initially

deferred。

disable：禁止的意思。

这些都是在执行SQL时的一些开关选项。

四、oracle级联更新实现

Oracle外键只有级联删除没有级联更新，但是有时候系统中可能需要这样的功能，所以这里记录下实现过程。

Oracle中的约束有两种方式，一种是延迟约束，一种是非延迟约束。所谓的非延迟约束就是说更改记录的时候会立刻进行约束条件的查看，是否因为违反了某些约束条件而不能执行修改，而延迟约束则表示不会再刚进行修改的时候进行约束查看，只有提交的时候才会检查。Oracle的级联更新就是使用这个特性来实现的。

1、首先建立外键的时候设置为延迟约束。具体的语句如下：

ALTER TABLE TEST_2 ADD CONSTRAINT TEST_2_TEST_1_FK1 FOREIGN KEY (FKID) REFERENCES TEST_1(ID) ON DELETE CASCADE DEFERRABLE;

就是为TEST_2表增加外键，引用到TEST_1上，并且设置为级联删除和延迟约束(DEFERREABLE)。如果是使用了Oracle的Sql

Developer工具，可以在约束条件一栏查看是否设置成功。

2、然后设置触发器

在TEST_1表上设置触发器，当表的主键改变了就级联的更新表TEST_2中对应的字段。

CREATE OR REPLACE

TRIGGER TEST_UPDATE

AFTER UPDATE ON TEST_1

FOR EACH ROW

BEGIN

IF :OLD.ID<>:NEW.ID THEN

UPDATE TEST_2 SET FKID=:NEW.ID WHERE FKID=:OLD.ID;

END IF;

END;

经过1和2两步就可以实现外键的级联更新了。基本思想就是在更新主表主键的时候进行了延迟约束，不会立刻检查，而触发器在更新之后就会级联更新附表的外键，然后提交，这是会进行约束检查，由于外键已经级联更新过了，所以这里不会有问题。