Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DEFERRABLE Constraint State Meaning ??

user503699Sep 26 2011 — edited Sep 26 2011
Hello,

I checked the documentation about the meaning of DEFERRABLE constraint state here and in particular following section
DEFERRABLE Clause 
The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.
What I understood from above was if the constraint is created as DEFERRABLE, then it is possible to change its status to DEFERRABLE(i.e. check the validity at the end of transaction) / IMMEDIATE(i.e. check the validity at the end of statement). However, my following test shows that just having constraint defined as DEFERRABLE is enough to postpone the constraint validation to the end of transaction. Is the documentation incorrect or am I missing something obvious here?
SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - Production
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> DROP SEQUENCE P_SEQ ;

Sequence dropped.

SQL> DROP TABLE C PURGE ;
DROP TABLE C PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> DROP TABLE P CASCADE CONSTRAINTS PURGE ;

Table dropped.

SQL> CREATE TABLE P (PID NUMBER, CONSTRAINT P_PK PRIMARY KEY (PID)) ;

Table created.

SQL> CREATE SEQUENCE P_SEQ minvalue 1 start with 1 increment by 1 cache 10 ;

Sequence created.

SQL> CREATE TABLE C (CID NUMBER, PID NUMBER, CONSTRAINT C_PK PRIMARY KEY (CID), constraint c_fk foreign key (pid) references P(PID) deferrable initially immediate) ;

Table created.

SQL> select CONSTRAINT_NAME, TABLE_NAME, R_CONSTRAINT_NAME, status, DEFERRABLE, DEFERRED from USER_CONSTRAINTS ;

CONSTRAINT_NAME                TABLE_NAME                     R_CONSTRAINT_NAME              STATUS   DEFERRABLE     DEFERRED
------------------------------ ------------------------------ ------------------------------ -------- -------------- ---------
C_FK                           C                              P_PK                           ENABLED  DEFERRABLE     IMMEDIATE
P_PK                           P                                                             ENABLED  NOT DEFERRABLE IMMEDIATE
C_PK                           C                                                             ENABLED  NOT DEFERRABLE IMMEDIATE

SQL> CREATE OR REPLACE TRIGGER C_BI_TRG
  2  BEFORE INSERT ON C
  3  FOR EACH ROW
  4  BEGIN
  5    IF :NEW.PID IS NULL THEN
  6      INSERT INTO P VALUES (P_SEQ.NEXTVAL) RETURNING PID INTO :NEW.PID ;
  7    END IF;
  8  END;
  9  /

Trigger created.

SQL> SELECT * FROM P ;

no rows selected

SQL> SELECT * FROM C ;

no rows selected

SQL> INSERT INTO C VALUES (1, NULL) ;

1 row created.

SQL> SELECT * FROM P ;

       PID
----------
	 1

SQL> SELECT * FROM C ;

       CID	  PID
---------- ----------
	 1	    1

SQL> commit ;

Commit complete.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2011
Added on Sep 26 2011
4 comments
8,761 views