Oracle Management Constraints (v) Oracle uses deferred constraint checking

By default, if you type data that violates a constraint rule when you perform a DML operation, the error message is immediately prompted. In some cases, you might want to check for constraints at the end of a transaction, such as cascading updates to foreign keys, loading data from a reference table, and so on. It is important to note that if you use deferred constraint checking, you must specify the deferrable option when defining constraints. Examples are as follows:

11:53:27 sql> ALTER TABLE DEPT1

11:53:38 2 Add Constraint pk_dept1 primary key (DEPTNO);

Table altered.

11:53:58 sql> ALTER TABLE EMP1

11:54:00 2 Add Constraint fk_emp1 foreign key (DEPTNO) references dept1 (DEPTNO) deferrable;

Table altered.

--deferrable can be deferred (default is immediate, non deferr delay)

11:54:08 sql> set constraint fk_emp1 deferred;

Constraint set.

---------the constraint fk_emp1 into the deferred state

11:55:04 sql> SELECT * from EMP1;

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

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

Rows selected.

11:55:41 sql> SELECT * from DEPT1;

DEPTNO dname LOC

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

Ten ACCOUNTING NEW YORK

DALLAS

SALES CHICAGO

OPERATIONS BOSTON

11:55:44 sql> Update emp1 set deptno=50 where empno=7900;

1 row updated.

11:56:12 sql> commit;

Commit

*

ERROR at line 1:

Ora-02091:transaction rolled back

Ora-02291:integrity constraint, SCOTT. FK_EMP1) violated-parent key not found

11:56:28 sql> SELECT * from EMP1;

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

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/