Deferred Constraints

A deferred constraint is enforced when a transaction is committed. Uou use the DEFERRABLE clause when you initially add the constraint. Once you've added a constraint,you cannot change it to DEFERRABLE. You have to drop and re-create the constraint.

A DEFERRABLE constraint can be marked as INITIALLY IMMEDIATE or INITIALLY DEFERRED. INITIALLY IMMEDIATE constraint is checked whenever you add, update, or delete rows from a table. INITIALLY DEFERRED constraint is checked when a transaction is committed.

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    SAL NUMBER(7, 2),
  5                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> ALTER TABLE emp
  2  ADD CONSTRAINT my_uq UNIQUE (ename)
  3  DEFERRABLE INITIALLY DEFERRED;

Table altered.

SQL>

1. Adding a Constraint with CHECK
2. Adding a NOT NULL Constraint
3. Adding a FOREIGN KEY Constraint
4. ON DELETE CASCADE
5. ON DELETE SET NULL
6. Adding a UNIQUE Constraint
7. CHECK constraint
8. Multiple Constraints
9. Dropping a Constraint
10. Disabling a Constraint
11. Enabling a Constraint
12. Deferred Constraints
13. Getting Information on Constraints:user_constraints and all_constraints