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

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

在会话级设置:
SQL> set constraint FK_DEPTX deferred;

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

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


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

2.Deferred,immediate(default value)

deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后,可以通过set constraints在会话级进行deferred属性的设置,来确定约束应用时点。
定义约束是使用initially关键字来确定约束的deferred属性取值。

SQL> alter table empx drop constraint fk_deptx;

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

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	    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的方式,对历史数据不进行约束控制,而只针对新数据开启验证。


  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值