管理约束（五）--使用延期约束检查

默认情况下，当执行DML操作时，如果键入了违反约束规则的数据，则会立即提示错误信息。某些情况下，可能希望在事务结束时进行约束检查，如级联更新外部键、给自参照表装载数据等。需要注意，如果使用延期约束检查，那么在定义约束时必须指定 DEFERRABLE 选项。实例如下：

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 可延迟（默认是immediate ，非 deferr 延迟）

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

Constraint set.

---------将约束fk_emp1 进入延迟状态

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

12 rows selected.

11:55:41 SQL> select * from dept1;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 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;