|
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 ConstraintsA disabled constraint can be enabled in either of two ways: ENABLE NOVALIDATE or ENABLE VALIDATE. ENABLE NOVALIDATEFor 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]
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 VALIDATEENABLE 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:
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:
If data in a table violates the constraint, the statement is rolled back, and the constraint remains in a disabled state.
Using the EXCEPTIONS TableYou 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;
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. |
|