ORACLE约束的属性

65 阅读 0 评论 43 点赞

我是靠谱客的博主坚定热狗，最近开发中收集的这篇文章主要介绍ORACLE约束的属性，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

今天处理了一个由于约束插入数据失败的问题，处理时感到有些吃力，三天不练手生啊。在这里回忆一下。
Oracle数据库Constraint约束的四对属性：Deferrable/not deferrable, Deferred/immediate, enable/disable, validate/novalidate。

准备一下环境：
SQL> create table empx as select * from emp;

SQL> create table deptx as select * from dept;

SQL> alter table empx add constraint pk_empx primary key(empno);

SQL> alter table deptx add constraint pk_deptx primary key(deptno);

SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);

SQL> select * from deptx;


DEPTNO DNAME
LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING
NEW YORK
20 RESEARCH
DALLAS
30 SALES
CHICAGO
40 OPERATIONS
BOSTON

SQL> select * from empx;


EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM
DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH
CLERK
7902 17-DEC-80
800
20
7499 ALLEN
SALESMAN
7698 20-FEB-81
1600
300
30
7521 WARD
SALESMAN
7698 22-FEB-81
1250
500
30
7566 JONES
MANAGER
7839 02-APR-81
2975
20
7654 MARTIN
SALESMAN
7698 28-SEP-81
1250
1400
30
7698 BLAKE
MANAGER
7839 01-MAY-81
2850
30
7782 CLARK
MANAGER
7839 09-JUN-81
2450
10
7788 SCOTT
ANALYST
7566 19-APR-87
3000
20
7839 KING
PRESIDENT
17-NOV-81
5000
10
7844 TURNER
SALESMAN
7698 08-SEP-81
1500
0
30
7876 ADAMS
CLERK
7788 23-MAY-87
1100
20
7900 JAMES
CLERK
7698 03-DEC-81
950
30
7902 FORD
ANALYST
7566 03-DEC-81
3000
20
7934 MILLER
CLERK
7782 23-JAN-82
1300
10
14 rows selected.

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED

SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found

这里通过emp和dept表创建了两张表empx和deptx,并在empx表上创建了外键约束fk_deptx。

1.Deferrable,not deferrable(default value)

1)这对属性是定义是否可以延时验证,deferrable是指作验证时机,如果在commit的时check为deferrable,否则为immediate .只有在设置Deferrable才可以设置另一属性Deferred,immediate.

2)这对属性是在创建的constraint的时候定义的,不能被修改.

notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index

SQL> alter table empx drop constraint fk_deptx;

SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable;

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
------------------------ ---------------- ------------------------ ---------------- ---------------- ------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
DEFERRABLE
IMMEDIATE
VALIDATED

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
DEFERRABLE
IMMEDIATE
VALIDATED

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- --------------------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
DEFERRABLE
DEFERRED
VALIDATED

SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found

可以通过创建contraint时指定Deferred值，也可以通过会话级别的语句SET CONSTRAINT(s)来设置（如上面的set constraint FK_DEPTX deferred）。

当属性DEFERRABLE和DEFERRED的值分别为DEFERRABLE和DEFERRED时，在事务提交时才验证，如果验证没通过，则报错，事务回滚。

3. novalidate, validate(default value)

定义是否对表中已经存在的数据作检查。

删除约束并插入一条脏数据：
SQL> alter table empx drop constraint fk_deptx;

Table altered.

SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8000,'TOM','SALESMAN',7839,sysdate,2000,50);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno);
alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found

SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred;
alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) deferrable initially deferred
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found

语句中不带validate属性参数，默认创建的是validate的约束。由于有脏数据的存在，不管是创建延时还是非延时的约束都不成功。

SQL> alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) novalidate;

Table altered.

创建novalidate的约束成功。

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
NOT DEFERRABLE
IMMEDIATE
NOT VALIDATED

SQL> select * from empx;


EMPNO ENAME
JOB
MGR HIREDATE
SAL
COMM
DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH
CLERK
7902 17-DEC-80
800
20
7499 ALLEN
SALESMAN
7698 20-FEB-81
1600
300
30
7521 WARD
SALESMAN
7698 22-FEB-81
1250
500
30
7566 JONES
MANAGER
7839 02-APR-81
2975
20
7654 MARTIN
SALESMAN
7698 28-SEP-81
1250
1400
30
7698 BLAKE
MANAGER
7839 01-MAY-81
2850
30
7782 CLARK
MANAGER
7839 09-JUN-81
2450
10
7788 SCOTT
ANALYST
7566 19-APR-87
3000
20
7839 KING
PRESIDENT
17-NOV-81
5000
10
7844 TURNER
SALESMAN
7698 08-SEP-81
1500
0
30
7876 ADAMS
CLERK
7788 23-MAY-87
1100
20
7900 JAMES
CLERK
7698 03-DEC-81
950
30
7902 FORD
ANALYST
7566 03-DEC-81
3000
20
7934 MILLER
CLERK
7782 23-JAN-82
1300
10
8000 TOM
SALESMAN
7839 15-JUL-12
2000
50

有一条脏数据存在，看看能不能再增加一条：

SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50);
insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTX) violated - parent key not found

不能增加违反约束的数据，novalidate的约束只是对原来的数据库不做验证，但对新插入的数据还是要进行验证的。

4. disable, enalbe(default value)

启用和禁用constraint.如果在新建pk和uk时定义了disable,将不建立相应的索引.

SQL> alter table empx disable constraint fk_deptx;

Table altered.

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
DISABLED
NOT DEFERRABLE
IMMEDIATE
NOT VALIDATED

SQL> insert into empx (empno,ename,job,mgr,hiredate,sal,deptno) values (8001,'TOM','SALESMAN',7839,sysdate,2000,50);

1 row created.

SQL> commit;

Commit complete.

禁用了约束后，脏数据也能插入成功。

SQL> alter table empx enable constraint fk_deptx;
alter table empx enable constraint fk_deptx
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_DEPTX) - parent keys not found

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
DISABLED
NOT DEFERRABLE
IMMEDIATE
NOT VALIDATED

SQL> alter table empx enable novalidate constraint fk_deptx;

Table altered.

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='EMPX';

CONSTRAINT_NAME
COND
STATUS
DEFERRABLE
DEFERRED
VALIDATED
---------------- ---------------- ------------------------ ---------------- ---------------- ----------------
PK_EMPX
ENABLED
NOT DEFERRABLE
IMMEDIATE
VALIDATED
FK_DEPTX
ENABLED
NOT DEFERRABLE
IMMEDIATE
NOT VALIDATED

如果有脏数据，启用约束时要同时设置novalidate。

由上面的实验得知，默认情况下，Oracle constraint是不开启延迟约束和原有数据保留验证的。

下列情况下可以使用这些特性：
1）批量数据导入、物化视图刷新的时候，事务量很大，而且存在数据规律前后颠倒的情况
此时，如果开启着立即约束应用的开关，可能存在一些暂时性的约束不满足的情况，从而影响到整个系统的运行。开启约束延迟验证，就可以帮助我们解决这个问题；
2）在历史数据移植
历史数据存在不规则的情况，很多时候难以满足我们新系统的数据完整性要求。可以使用not validate的方式，对历史数据不进行约束控制，而只针对新数据开启验证。