Reporting Constraint Exceptions
If exceptions exist when a constraint is validated, an error is returned and the integrity constraint remains novalidated. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot validate the constraint until all exceptions to the constraint are either updated or deleted.
To determine which rows violate the integrity constraint, issue the
ALTER TABLEstatement with the
EXCEPTIONSoption in the
EXCEPTIONSoption places the rowid, table owner, table name, and constraint name of all exception rows into a specified table.
You must create an appropriate exceptions report table to accept information from the
EXCEPTIONSoption of the
ENABLEclause before enabling the constraint. You can create an exception table by executing the
UTLEXCPT.SQLscript or the
Both of these scripts create a table named
EXCEPTIONS. You can create additional exceptions tables with different names by modifying and resubmitting the script.
The following statement attempts to validate the
PRIMARY KEYof the
depttable, and if exceptions exist, information is inserted into a table named
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
If duplicate primary key values exist in the
depttable and the name of the
PRIMARY KEYconstraint on
sys_c00610, then the following query will display those exceptions:
SELECT * FROM EXCEPTIONS;