ORACLE约束的属性(三)
,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;
www.2cto.com
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';
[plain]
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;
[plain]
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