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;
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
.
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 CONSTRAINT fk_tab1_tab2 ENABLE VALIDATE;
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.Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/constraint-checking-updates