6.6 How do I…Enable, disable, and enforce constraints?Problem
In order to manipulate data in some batch processing functions within our organization, I need to disable constraints. Without disabling the constraints, programs cannot perform any tasks that violate referential integrity, even temporarily. After the batch processes have completed, I need to enable the constraints. I know that with Oracle8, a constraint can be enforced before enabling it. How do I enable, disable, and enforce constraints?
Technique
You must know the name of the constraint in order to enable, disable, or enforce it. As explained in the previous How-To, names of constraints on tables owned by the current user account can be queried as the CONSTRAINT_NAME column from the USER_CONSTRAINTS data dictionary view. The STATUS column of the view lists the current state of these constraints. When a new constraint is created, its state defaults to ENABLED. A constraint can be disabled right upon creation by having a DISABLE keyword after the constraint definition as in:
CREATE TABLE DEPT06 (
DEPT_NO NUMBER(4) PRIMARY KEY DISABLE,
.......);
or while adding a new constraint with an ALTER TABLE statement:
ALTER TABLE DEPT06
ADD PRIMARY KEY DEPT_NO DISABLE;
An ALTER TABLE statement can be used to change the state of a constraint anytime after creation of the constraint.
Steps
1. Run SQL*Plus and connect as the WAITE user account. If you have not run the CHP6_5.SQL script before, run it to create the EMP06 and DEPT06 tables. Now run the CHP6_16.SQL file, which contains an ALTER TABLE statement to disable the foreign key constraint in the EMP06 table. The name of the constraint was obtained from the query in the previous How-To. Figure 6.16 shows the results of running the statement in SQL*Plus.
The DISABLE CONSTRAINT clause instructs Oracle to disable the constraint specified in line 3. If the constraint is already disabled, the statement will not return an error. After a constraint is disabled, the database does not check to ensure that an operation follows the rules of the constraint. If an operation is performed that violates a disabled constraint, the data must be corrected before the constraint can be enabled, or an ORA-02298 error will occur while enabling the constraint. To perform a cascade disable, that is, disable a primary key and any foreign keys dependent on it, use the CASCADE keyword as DISABLE PRIMARY KEY CASCADE.
2. Run the CHP6_17.SQL file in SQL*Plus. This file is similar to the one used in step 1 of the previous How-To to view constraint information from the USER_CONSTRAINTS view.
SQL> SET ECHO ON
SQL> COLUMN CONSTRAINT_NAME FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A10
SQL> COLUMN R_CONSTRAINT_NAME FORMAT A17
SQL> COLUMN SEARCH_CONDITION FORMAT A30
SQL> SELECT
2 CONSTRAINT_NAME,
3 TABLE_NAME,
4 CONSTRAINT_TYPE,
5 R_CONSTRAINT_NAME,
6 STATUS
7 FROM
8 USER_CONSTRAINTS
9 ORDER BY 1, 2;
Figure 6.17 shows the results of the query. The STATUS column contains the state of the constraint: ENABLED, DISABLED, or ENFORCED. The constraint FKEY_DEPT is shown as DISABLED because it was disabled in Step 1. When a constraint is disabled, it cannot be used to create new constraints. For example, a foreign key constraint requires a primary key constraint on the parent table. If the primary key constraint on the parent table has been disabled, the foreign key cannot be created.
3. Run the command in CHP6_18.SQL file, which contains an ALTER TABLE statement to enforce the foreign key constraint in the EMP06 table, which was disabled in Step 1. Figure 6.18 shows the results of running the statement in SQL*Plus.
The constraint is enforced after running the statement. Both enabled or disabled constraints can be sent into an enforced state of trance. An ALTER TABLE statement that attempts to enforce an integrity constraint will not fail if there are rows in the table that violate any integrity constraint defined with the table. But after the constraint is enforced, it wont allow anymore integrity violating rows to be inserted or updated that would have been possible if the constraint was in a disabled state. Enforcing the constraint changes the STATUS column of the USER_CONSTRAINTS view to ENFORCED, which can be viewed by running the CHP6_17.SQL file in SQL*Plus.
Oracle8 introduces this new constraint state of enforced. It is a partially enabled constraint with an intermediate enable novalidate state as it does not check existing table data for validity, whereas an enabled constraint is in an enable validate state.
4. Run the command in CHP6_19.SQL file, which contains an ALTER TABLE statement to enable the foreign key constraint in the EMP06 table, which was enforced in step 2. Figure 6.19 shows the results of running the statement in SQL*Plus.
The constraint is enabled after running the statement. A constraint that is either in a disabled or an enforced state can be enabled using the above command. Enabling the constraint changes the STATUS column of the USER_CONSTRAINTS view to ENABLED. This can be verified by running the CHP6_17.SQL file in SQL*Plus. An ALTER TABLE statement that attempts to enable an integrity constraint may fail if there are rows in the table that violate any integrity constraint defined with the table. Consequently, the constraint is not enabled and the statement is rolled back.
How It Works
Step 1 executes an ALTER TABLE statement with a DISABLE CONSTRAINT clause to disable a constraint. Step 2 to view the constraint status by querying the USER _CONSTRAINTS view after it has been disabled. Step 3 demonstrates how to enforce a constraint with the ENFORCE keyword in the ALTER TABLE statement. Step 4 enables the constraint by using an ALTER TABLE statement with the ENABLE CONSTRAINT clause.
Comments
Constraints should be used whenever possible to ensure data integrity. Enabled constraints can cause problems in batch processes or during system maintenance, as not a single row violating a constraint can be inserted into the corresponding table. In data warehouse configurations, temporarily disabling integrity constraints might be necessary to upload valid OLTP data. Disabling a constraint makes it possible to insert rows violating constraints. These rows are known as constraint exceptions. The data in the tables affected by the constraint must be valid before the constraint is enabled. Oracle will not allow the constraint to be enabled if the data in the table violates the constraint. The rows that violate the constraint must be either deleted or updated before enabling the constraint. All rows violating constraints can be examined from an exceptions table, which is examined in How-To 6.9.
In Oracle8, a disabled constraint can be enforced using the ENFORCE keyword with the ALTER TABLE statement. A table with enforced constraints can contain invalid data, but it disallows adding new invalid data to it. Validation is not required when enforcing a constraint, which makes it much faster than enabling a constraint. Enforced constraints can then be enabled at leisure to validate data in the tables. Thus, ENFORCED is an interim state of a constraint, before it gets reincarnated when the constraint is enabled using the ENABLE keyword.
Prior to Oracle8, disabling a unique or primary key constraint caused the corresponding unique index to be dropped. When enabling a disabled constraint, an index had to be re-created. This resulted into a long time to get the primary key constraint enabled for large tables. With Oracle8, the index does not need to be rebuilt when the constraint is re-enabled. Also, unique and primary keys can use non-unique indexes as mentioned earlier in How-To 6.1. The non-unique index has to be created manually, and the constraint must be created in a disabled state. An existing index is used when the constraint is enabled.
Validation takes place when enabling a disabled constraint. To ensure that no new data is entered while data is being validated, Oracle puts exclusive locks on the table. All this might have a negative impact on the availability and performance of a system while enabling disabled constraints. Enforced constraints are handy in such situations, as no locks are required during validation. Consequently, concurrent operations can be allowed on tables with enforced constraints, and enforced constraints can be enabled concurrently unlike disabled constraints that have to enabled serially. As the table does not need to be exclusively locked, users can read and modify data while an enforced constraint is enabled.
In order to enable, disable, or enforce a constraint, the user account must either be the owner of the table, or have the ALTER TABLE or ALTER ANY TABLE privilege.