Deferred Constraint
- A deferred constraint is one that is enforced when a transaction is committed.
- To make a constraint
deferred we need to declare it as deferrable. A deferrable constraint is
specified by using DEFERRABLE clause.
- Once you've added a
constraint, you cannot change it to DEFERRABLE. You must drop and recreate the
constraint.
- When you add a
DEFERRABLE constraint, you can mark it as INITIALLY IMMEDIATE or INITIALLY
DEFERRED.
- INITIALLY IMMEDIATE
means that the constraint is checked whenever you add, update, or delete rows
from a table.
- INITIALLY DEFERRED
means that the constraint is only checked when a transaction is committed.
A. DEFERRED Example
SQL>CREATE TABLE a1(
id INT
CONSTRAINT cns_a1_id_chk check(id>5) DEFERRABLE,
name VARCHAR2(35)
);
SQL>INSERT INTO a1 values(6, 'a'); //valid
SQL>INSERT INTO a1 values(1, 'a'); //invalid
SQL>commit;
SQL>SELECT * FROM a1;
ID NAME
6 a
SQL>SET CONSTRAINT cns_a1_id_chk DEFERRED;
SQL>INSERT INTO a1 values(7, 'a'); //valid
SQL>INSERT INTO a1 values(2, 'a'); //valid (although it is violating
the constraint,
because
the constraint is defined as INITIALLY
DEFERRABLE
which is nothing but same as
DEFERRED)
SQL>SELECT * FROM a1;
ID NAME
6 a
7 a
1 a
SQL>commit;
Note: when commit
query will execute it will check all the temporary inserted data is valid or not
and if it will found any record as invalid one then it will rollback all (till
last commit).
So as in our
above example we had inserted two records i.e.
SQL>INSERT INTO a1 values(7, 'a'); //valid
SQL>INSERT INTO a1 values(1, 'a'); //invalid
As 2nd record contains invalid data so it
will rollback both the records. As a result of this now there is only one record
is found in the table;
SQL>SELECT * FROM a1;
ID NAME
6 a
B. INITIALLY DEFERRED Example
SQL>CREATE TABLE a2(
id INT
CONSTRAINT cns_a2_id_chk check(id>5) DEFERRABLE INITIALLY DEFERRED,
name VARCHAR2(35)
);
SQL>INSERT INTO a2 values(6, 'a'); //valid
SQL>commit;
SQL>SELECT * FROM a2;
ID NAME
6 a
SQL>INSERT INTO a2 values(7, 'a'); //valid
SQL>INSERT INTO a2 values(2, 'a'); //valid (although it is violating
the constraint,
because
the constraint is defined as INITIALLY
DEFERRABLE
which is nothing but same as
DEFERRED)
SQL>SELECT * FROM a2;
ID NAME
6 a
7 a
1 a
SQL>commit;
Note: when commit
query will execute it will check all the temporary inserted data is valid or not
and if it will found any record as invalid one then it will rollback all (till
last commit).
So as in our
above example we had inserted two records i.e.
SQL>INSERT INTO a2 values(7, 'a'); //valid
SQL>INSERT INTO a2 values(1, 'a'); //invalid
As 2nd record contains invalid data so it
will rollback both the records. As a result of this now there is only one record
is found in the table;
SQL>SELECT * FROM a2;
ID NAME
6 a
C. INITIALLY IMMEDIATE Example
SQL>CREATE TABLE a3(
id INT
CONSTRAINT cns_a3_id_chk check(id>5) DEFERRABLE INITIALLY IMMEDIATE,
name VARCHAR2(35)
);
SQL>INSERT INTO a3 values(6, 'a'); //valid
SQL>INSERT INTO a3 values(1, 'a'); //invalid
SQL>commit;
SQL>SELECT * FROM a3;
ID NAME
6 a
SQL>SET CONSTRAINT cns_a3_id_chk DEFERRED;
SQL>INSERT INTO a3 values(7, 'a'); //valid
SQL>INSERT INTO a3 values(2, 'a'); //valid (although it is violating
the constraint,
because
the constraint is defined as INITIALLY
DEFERRABLE
which is nothing but same as
DEFERRED)
SQL>SELECT * FROM a3;
ID NAME
6 a
7 a
1 a
SQL>commit;
Note: when commit
query will execute it will check all the temporary inserted data is valid or not
and if it will found any record as invalid one then it will rollback all (till
last commit).
So as in our
above example we had inserted two records i.e.
SQL>INSERT INTO a3 values(7, 'a'); //valid
SQL>INSERT INTO a3 values(1, 'a'); //invalid
As 2nd record contains invalid data so it
will rollback both the records. As a result of this now there is only one record
is found in the table;
SQL>SELECT * FROM a3;
ID NAME
6 a
Note: For more information on Oracle database constraints
please refer Oracle Database SQL Reference guide
