Oracle Deferred Constraints

By | September 30, 2015

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.

  • ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.
  • ENABLE NOVALIDATE means the constraint is checked for new or modified rows, but existing data may violate the constraint.
  • DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.
  • DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.
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

  • Exception handling has to be coded carefully as statements will not trigger exceptions directly. Often exceptions will only be picked up by the outermost exception handler which encloses the commit statement.
  • Converting a NOVALIDATE constraint to VALIDATE may take a long time depending on the amount of data to be validated, although conversion in the other direction is not an issue.
  • Enabling a unique or primary key constraint when no index is present causes the creation of a unique index. Likewise, disabling a unique or primary key will drop a unique index that it used to inforce it.

Hope this helps. Regards Tim…

One thought on “Oracle Deferred Constraints

  1. Tr Y SIngh

    Hi
    Gone through Bulk collect. Its really nvery gud for Oracel Technical guys.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *