8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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:
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;
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
Table constraints can be enabled and disabled using the
CREATE TABLE or
ALTER TABLE statement. In addition the
NOVALIDATE keywords can be used to alter the action of the state.
ENABLE VALIDATEis the same as
ENABLE. The constraint is checked and is guaranteed to hold for all rows.
ENABLE NOVALIDATEmeans the constraint is checked for new or modified rows, but existing data may violate the constraint.
DISABLE NOVALIDATEis the same as
DISABLE. The constraint is not checked so data may violate the constraint.
DISABLE VALIDATEmeans 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;
- 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
VALIDATEmay 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...