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.