Home » Articles » 8i » Right here

Constraint Checking Updates

Oracle 8i presented quite a few updates to the constraint checking mechanism to offer extra independence while processing knowledge:

• Deferred Constraints
• Constraint States
• Issues

Deferred Constraints

Right through massive transactions involving more than one dependancies it’s incessantly tough to procedure knowledge successfully because of the limitations imposed by means of the restrictions. An instance of this will be the replace of a number one key (PK) which is referenced by means of international keys (FK). The main key columns can’t be up to date as this is able to orphan the dependant tables, and the dependant tables can’t be up to date previous to the dad or mum desk as this is able to additionally put together them orphans. Historically this condition was once solved by means of disabling the international key constraints or deleting the unedited information and recreating them. Since neither of those answers is especially enough Oracle 8i comprises backup for deferred constraints. A deferred constraint is handiest checked on the level the transaction is commited.

Via default constraints are created as NON DEFERRABLE however this may also be overidden the usage of the DEFERRABLE key phrase. If a constraint is created with the DEFERRABLE key phrase it might probably office in considered one of two techniques (INITIALLY IMMEDIATE, INITIALLY DEFERRED). The default, INITIALLY IMMEDIATE, key phrase reasons constraint validation to occur rapid except deferred is particularly asked. The INITIALLY DEFERRED key phrase reasons constraint validation to defer till devote, except rapid is secifically asked. Refer to code creates two tables with a deferred constraint.

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

ALTER TABLE tab2 ADD PRIMARY KEY (identity);

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

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

The ALTER SESSION... statements display how the climate of the constraint may also be modified. Those ALTER SESSION... statements won’t paintings for constraints which can be created as NOT DEFERRABLE.

Constraint States

Desk constraints may also be enabled and disabled the usage of the CREATE TABLE or ALTER TABLE observation. As well as the VALIDATE or NOVALIDATE key phrases may also be impaired to vary the motion of the climate.

• ENABLE VALIDATE is equal to ENABLE. The constraint is checked and is assured to reserve for all rows.
• ENABLE NOVALIDATE approach the constraint is checked for fresh or changed rows, however present knowledge might violate the constraint.
• DISABLE NOVALIDATE is equal to DISABLE. The constraint isn’t checked so knowledge might violate the constraint.
• DISABLE VALIDATE approach the constraint isn’t checked however disallows any amendment of the constrained columns.

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

ALTER TABLE tab1 MODIFY CONSTRAINT fk_tab1_tab2 ENABLE VALIDATE;

Problems

• Exception dealing with must be coded in moderation as statements won’t cause exceptions without delay. Frequently exceptions will handiest be picked up by means of the outermost exception handler which encloses the devote observation.
• Changing a NOVALIDATE constraint to VALIDATE might rush a protracted presen relying at the quantity of information to be validated, even supposing conversion within the alternative route isn’t a topic.
• Enabling a singular or number one key constraint when disagree index is provide reasons the foundation of a singular index. Likewise, disabling a singular or number one key will let go a singular index that it impaired to inforce it.

Hope this is helping. Regards Tim…

Back to the Top.