Maintaining Integrity Constraints

After integrity constraints have been created, it often becomes necessary to change their state, or to otherwise manipulate them. This section covers those functions.

Enabling Constraints

A disabled constraint can be enabled in either of two ways: ENABLE NOVALIDATE or ENABLE VALIDATE.

ENABLE NOVALIDATE

For primary keys and unique constraints that already have existing indexes built on them, enabling a constraint NOVALIDATE is faster than enabling it VALIDATE because existing data is not checked for constraint violation. If this option is used, no locks are required on the table, and the option is therefore highly appropriate for OLTP environments where a lot of DML activity is taking place on the table.

The following ALTER statement enables the constraint named constraint_name with the NOVALIDATE option:

 ALTER TABLE tablename ENABLE NOVALIDATE CONSTRAINT constraint_name |PRIMARY KEY |UNIQUE (column, column...) [USING INDEX index clause] 

USING INDEX clauses are applicable only for PRIMARY KEY or UNIQUE CONSTRAINTS that have been created as deferrable, and either the constraints were created disabled or the constraints were disabled and the index dropped.

If the index needs to be created using the ENABLE CONSTRAINT method, NOVALIDATE does not offer any significant benefit over VALIDATE because Oracle locks the table to build the index.

ENABLE VALIDATE

ENABLE VALIDATE checks existing data in the table for constraint validation when the constraint is reenabled. When a constraint is enabled, VALIDATE is the default. When a constraint that is disabled is ENABLED VALIDATE, it has the following effect:

• The table is locked and changes to the table and to data within the table are prevented until validation has completed.

• Oracle creates an index if one does not already exist. A unique index is created while enabling a primary key or a unique constraint that is nondeferrable, and a nonunique index is created of deferrable primary keys or unique constraints.

If ENABLE VALIDATE is executed when a constraint is enforced, it does not require any locks be acquired on the table during validation because the enforcement guarantees that no violations will be introduced during the validation. This has several advantages:

• All constraints are enabled concurrently.

• Each constraint is internally parallelized, allowing for performance gains.

• Concurrent activity on the table is permitted.

VALIDATE is the default when enabling constraints and therefore does not need to be specified when enabling a disabled constraint.

If data in a table violates the constraint, the statement is rolled back, and the constraint remains in a disabled state.

It is important to remember that if ENABLE VALIDATE finds data that violates the constraint, the statement is rolled back and the constraint remains disabled. This may be included in a trick question.

Using the EXCEPTIONS Table

You create the EXCEPTIONS table by running the utlexcpt.sql script in the %ORACLE_HOME%\rdbms\admin directory on Windows or the $ORACLE_HOME /rdbms/admin directory on Unix. You can then execute the ALTER TABLE statement with the EXCEPTIONS option, and rows that violate constraints will have information included in the EXCEPTIONS table that will allow you to easily find the offending row or rows and rectify the data that has caused the exceptions to the constraints. After you have found and fixed the errant data, you can re-execute the ALTER TABLE statement to enable constraints.

The EXCEPTIONS table has four columns, ROW_ID, OWNER, TABLE_NAME, and CONSTRAINT. With this information, you can access the row directly with the ROWID (recall this uniquely identifies a row within the database) in the OWNER.TABLE table and determine what CONSTRAINT was violated so that you can more easily fix the problems.

The following ALTER statement enables the addresses_lastname_pk constraints and directs all exceptions into the exceptions table created by the utlexcpt.sql script:

 ALTER TABLE addresses ENABLE VALIDATE CONSTRAINT addresses_lastname_pk EXCEPTIONS INTO SYSTEM.EXCEPTIONS; 

If the EXCEPTIONS table does not belong to the user running the ALTER TABLE statement, it needs to be qualified with the owner. You can centralize the table in the SYSTEM schema and grant SELECT, INSERT, UPDATE, and DELETE on the table to everyone. To do this, create a PUBLIC SYNONYM, thereby eliminating the need to dereference the table name with the owning schema name. Not only will this allow users to more easily code programs to use the EXCEPTIONS table, it will also allow you to more elegantly maintain the system.

After you, or the user, have dealt with all exceptions, you can truncate the EXCEPTIONS table, and the constraint can be reenabled.

Again, it is simple to find information on constraints in the database by going to the data dictionary. The following section provides information on accessing that information.