Home » SQL & PL/SQL » SQL & PL/SQL » plz help me in knowing clearly of what is deferrable intially deferrable
plz help me in knowing clearly of what is deferrable intially deferrable [message #1337] Mon, 22 April 2002 13:55 Go to next message
pavani
Messages: 32
Registered: April 2002
Member
hi
i'm going through sql and pl/sql and i came across deferability---it's saying that inorder to constraint deferability to work u must define u'r constraint to be deferrable using deferrable initially deferred---plz help me in knowing this.and where we can use this and what's the result we get in using this.plz make me clesr with an example..
thanks for helping..

Report message to a moderator

Re: plz help me in knowing clearly of what is deferrable intially deferrable [message #1345 is a reply to message #1337] Tue, 23 April 2002 06:03 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This example shows the use of Deferred Constraints.

When a constraint is deferred, the database will check that the constraint is
satisfied only at commit time. This is useful in the case where an update to
a foreign key will violate the constraint. This will allow you to change the
foreign key and then cascade the changes to the parent table before committing
the change.

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 "deferred". 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

Report message to a moderator

Previous Topic: Oracle SQL Loader
Next Topic: ORA-00020 maximum number of connections reached(80)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 07:13:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.