DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: DEFERRABLE | NOT DEFERRABLE => cofusing me :-(

  1. #1
    Join Date
    Oct 2001
    Posts
    22
    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

    Do not worry people not knowing you,
    but seek the way that truely make yourself known to them
    -Confucius
    Need an Oracle running on Mac OS !!
    aleaves@hitel.net

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width