Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Constraint Checking Updates

Oracle 8i introduced a number of updates to the constraint checking mechanism to give more freedom whilst processing data:

Deferred Constraints

During large transactions involving multiple dependancies it is often difficult to process data efficiently due to the restrictions imposed by the constraints. An example of this would be the update of a primary key (PK) which is referenced by foreign keys (FK). The primary key columns cannot be updated as this would orphan the dependant tables, and the dependant tables cannot be updated prior to the parent table as this would also make them orphans. Traditionally this problem was solved by disabling the foreign key constraints or deleting the original records and recreating them. Since neither of these solutions is particularly satisfactory Oracle 8i includes support for deferred constraints. A deferred constraint is only checked at the point the transaction is commited.

By default constraints are created as NON DEFERRABLE but this can be overidden using the DEFERRABLE keyword. If a constraint is created with the DEFERRABLE keyword it can act in one of two ways (INITIALLY IMMEDIATE, INITIALLY DEFERRED). The default, INITIALLY IMMEDIATE, keyword causes constraint validation to happen immediate unless deferred is specifically requested. The INITIALLY DEFERRED keyword causes constraint validation to defer until commit, unless immediate is secifically requested. The following code creates two tables with a deferred constraint.

CREATE TABLE tab1 (id  NUMBER(10), tab2_id NUMBER(10));
CREATE TABLE tab2 (id  NUMBER(10));

ALTER TABLE tab2 ADD PRIMARY KEY (id);

ALTER TABLE tab1 ADD CONSTRAINT fk_tab1_tab2
  FOREIGN KEY (tab2_id)
  REFERENCES tab2 (id)
  DEFERRABLE
  INITIALLY IMMEDIATE;

ALTER SESSION SET CONSTRAINTS = DEFERRED;
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

The ALTER SESSION... statements show how the state of the constraint can be changed. These ALTER SESSION... statements will not work for constraints that are created as NOT DEFERRABLE.

Constraint States

Table constraints can be enabled and disabled using the CREATE TABLE or ALTER TABLE statement. In addition the VALIDATE or NOVALIDATE keywords can be used to alter the action of the state.

ALTER TABLE tab1 ADD CONSTRAINT fk_tab1_tab2
  FOREIGN KEY (tab2_id)
  REFERENCES tab2 (id)
  ENABLE NOVALIDATE;

ALTER TABLE tab1 MODIFY CONSTRAINTS fk_tab1_tab2 ENABLE VALIDATE;

Issues

Hope this helps. Regards Tim...

Back to the Top.