>>What is the difference between deferrable and not deferrable constraints?
By default constraints are not deferrable. They will notify immedietly on violation of constraint.
By setting a constraint as deferrable oracle checks voilation only at commit point i.e. at the end of the transaction.
Code:
SQL> CREATE TABLE TEST_CONS ( NUM NUMBER(10) CONSTRAINT NUM_PK PRIMARY KEY DEFERRABLE);
Table created.
SQL> SET CONSTRAINT NUM_PK DEFERRED;
Constraint set.
SQL> INSERT INTO TEST_CONS VALUES(1);
1 row created.
SQL> INSERT INTO TEST_CONS VALUES(1);
1 row created.
SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (CMSJAN.NUM_PK) violated