-
I post this question earlier but no one seems to answser, so I post again.
DEFERRABLE | NOT DEFERRABLE options mean that constraints can be set to DEFERRED | NOT DEFERRED by SET CONSTRAINT(S) ?
So, initial constraint state is set by INITIALLY DEFERRED | INITIALLY IMMEDIATE , and if a constraint is set DEFFERABLE INITIALLY IMMEDIATE, it means that constraint is set INITIALLY IMMEDIATE, but can be changed to DEFFERABLE by SETTING CONSTRAINT(S) command ?
Need your help on this.
Thank you so much.
Woo
-
I suggest you read carefully the following easy example due to Metalink. I think you will understand the issues after you go through the details of the example:
The following example can be run from SQL*Plus:
------------------------------Begin Script--------------------------------------
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME CHAR(14),
LOC CHAR(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO) INITIALLY DEFERRED,
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
commit;
---------------------------------End Script-------------------------------------
Without the deferred constraint, the following will happen when you delete
"deptno number 20" from the table "dept":
SQL> delete dept where deptno=20;
1 row deleted.
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated
- child record found
At this point the statement is rolled back and "dept number 20" is undeleted.
When using the deferred constraint, deleting this row will not generate an
error until a "commit" is performed. For example:
SQL> delete dept where deptno=20;
1 row deleted.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated
- child record found
This will then give you a chance to delete the "child" rows before issuing the
"commit" command. For example:
SQL> delete dept where deptno=20;
1 row deleted.
SQL> delete emp where deptno=20;
5 rows deleted.
SQL> commit;
Commit complete.
Another situation where the deferred constraint checking feature could be useful
is if you want to change the primary key value in the parent table i.e., "DEPT".
SQL> update dept set deptno=25 where deptno=20;
1 row updated.
SQL> update emp set deptno=25 where deptno=20;
5 rows updated.
SQL> commit;
Commit complete.
A constraint can also be created "INITIALLY IMMEDIATE DEFERRABLE". This means
that the constraint will be checked at "initially" instead of at "commit" time,
unless you manually set the constraint to "deferrable". For example:
------------------------------Begin Script--------------------------------------
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME CHAR(14),
LOC CHAR(13),
CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO) INITIALLY IMMEDIATE DEFERRABLE,
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
commit;
---------------------------------End Script-------------------------------------
SQL> delete dept where deptno=20;
delete dept where deptno=20
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_FOREIGN_KEY) violated
- child record found
SQL> set constraint emp_foreign_key deferred;
Constraint set.
SQL> delete dept where deptno=20;
1 row deleted.
There are new columns in the "user_constraints/dba_constraints/all_constraints"
views that will give you information on whether the constraint is deferrable.
For example:
SQL> select constraint_name,deferrable,deferred from user_constraints
where constraint_name like 'EMP%';
CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
EMP_PRIMARY_KEY NOT DEFERRABLE IMMEDIATE
EMP_SELF_KEY NOT DEFERRABLE IMMEDIATE
EMP_FOREIGN_KEY DEFERRABLE DEFERRED
