Thread: Constraints

What is the difference between deferrable and not deferrable constraints?

>>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

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.