8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | 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.
ENABLE VALIDATE
is the same asENABLE
. 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 asDISABLE
. 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 CONSTRAINT 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 toVALIDATE
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...