What is the difference between deferrable and not deferrable constraints?
Printable View
What is the difference between deferrable and not deferrable constraints?
[B]>>What is the difference between deferrable and not deferrable constraints?[/B]
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
[/CODE]
Deferrable constraints allows to go for transactions but not commit them .It checks for data integrity only at the time of commit not during normal transactions.