6.9 How do I…Determine integrity violations?Problem
In order to upload data into the database in batch operations, I have to disable constraints. After the load operation completes, I cannot enable constraints, due to constraint violating rows being inserted during the load. How do I identify exactly which rows violate integrity constraints?
Technique
If a row of a table does not satisfy any constraint defined with the table, this row is called as an exception to the constraint. Even if there is a single row in exception, the constraint cannot be enabled. An error is returned and the constraint remains disabled. In order to enable the constraint, exceptions to the constraint must be either deleted or updated to comply with the constraint.
Oracle can automatically report exceptions through an exception table. The EXCEPTIONS option in the ENABLE clause of a CREATE TABLE or ALTER TABLE statement can be used to specify an exception table to identify rows violating a constraint. A query on the specified exception table returns the ROWID, table owner, table name, and the constraint name for all exception rows.
Steps
1. As a prerequisite, run CHP6_20.SQL if you have not done this before. This will create the EMP06 table and insert some sample data in it. Run the statements in the CHP6_30.SQL file. This file contains an ALTER TABLE statement to disable the primary key on the EMP06 table. This is followed by an INSERT statement to insert a row with a duplicate primary key. Figure 6.30 shows the results of the operation in SQL*Plus.
In demonstrating this How-to, a row with a duplicate key needs to be inserted. This can be done only after disabling the constraint with the ALTER TABLE statement (refer to How-To 6.6). This is succeeded by inserting a row with a primary key equal to that of a record already existing in the table. No error is reported as the primary key is disabled. Now, there are multiple records with the EMP_NO value of 1 in the EMP06 table.
2. Run the statements in the CHP6_31.SQL file. The first statement is a CREATE TABLE statement to create an exceptions table. An ALTER TABLE statement is then used to specify this exceptions table to report exception rows encountered when enabling the constraint. Figure 6.31 illustrates the method of using an exceptions table to trap integrity violations.
In the first command, line 1 contains the CREATE TABLE keywords to create a table named WAITE_EXCEPTIONS. From line 2 through 5, columns of the table are created in line with expectations of any exception table that must typically have four columns—ROWID, table owner, table name, and constraint name, in that order—and must have the same datatypes and lengths as used in the WAITE_EXCEPTIONS table.
In the second command, the ALTER TABLE statement has an EXCEPTIONS option in the ENABLE clause, to specify the WAITE_EXCEPTIONS table to log information about constraint exceptions in the EMP06 table. The statement ends up with an error as expected, as a record with a duplicate key was intentionally inserted in Step 1. While attempting to enable the primary key, information about exception rows is placed by Oracle in the WAITE_EXCEPTIONS table.
3. Run the CHP6_32.SQL to query the WAITE_EXCEPTIONS table to report constraint exceptions. Another variation of the query lists actual rows from the EMP06 table using ROWIDs from the WAITE_EXCEPTIONS table. Figure 6.32 shows the output from the queries as displayed in SQL*Plus.
4. Run the CHP6_33.SQL file in SQL*Plus, as shown in Figure 6.33, to correct the exceptions reported for the EMP06 table.
The offending row is deleted from the EMP06 table followed by deleting the corresponding entries from the WAITE_EXCEPTIONS exception report table. A COMMIT transaction statement is issued to ensure changes to tables by the DELETE statements are made permanent. The primary key of the EMP06 is now enabled successfully using an ALTER TABLE statement.
How It Works
Step 1 executes an ALTER TABLE statement to disable a primary key before inserting an invalid row into a table. Step 2 creates an exception table by using the CREATE TABLE statement and issues an ALTER TABLE statement to specify the exception table in the EXCEPTIONS option of ENABLE clause. Step 3 queries the integrity violating rows from the exception table. Step 4 fixes integrity exceptions before successfully enabling the primary key using an ALTER TABLE statement.
Comments
An exception is a row in a table that violates an integrity constraint. If an exception table is specified and the constraint is being enabled, a row is inserted automatically in the exception table for each exception. If there are any exceptions, the constraint remains disabled. Exceptions from multiple constraints can be channeled to the same exception table. Exception rows can be either deleted or updated to correct integrity violations, and corresponding rows in the exception table have to be deleted explicitly. As a special case for index-only tables where rows are identified by the primary key and not the ROWID, the BUILD_EXCEPTIONS_TABLE procedure in the DBMS_IOT package is used to create the exceptions table.