默认情况下,当执行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;
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:56:39 SQL>
——插入记录只在提交(commit)时检查
转载于:https://blog.51cto.com/19880614/1173360