Exceptions for a specific integrity constraint can be identified while attempting to enable the constraint. This procedure is discussed in the section "Exception Reporting" ![[*]](jump.gif)
.
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, . . . ); ALTER TABLE emp ADD PRIMARY KEY (empno);
An ALTER TABLE statement that defines and attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint definition is not stored and not enabled. Refer to the section "Exception Reporting" for more information about rows that violate integrity constraints.
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY DISABLE, . . . ); ALTER TABLE emp ADD PRIMARY KEY (empno) DISABLE;
An ALTER TABLE statement that defines and disables an integrity constraints never fails. The definition of the constraint is always allowed because its rule is not enforced.
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE PRIMARY KEY, ENABLE UNIQUE (dname, loc);
An ALTER TABLE statement that attempts to enable an integrity constraint fails when the rows of the table violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled. Refer to the section "Exception Reporting" for more information about rows that violate integrity constraints.
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey; ALTER TABLE dept DISABLE PRIMARY KEY, DISABLE UNIQUE (dname, loc);
and the Oracle7 Server Reference manual.
. UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator; see the Oracle7 Server Administrator's Guide for more information.
Because there is a danger that some constraints might not be re-enabled after a direct path load, you should check the status of the constraint after completing the load to ensure that it was enabled properly.
You should consider enabling these constraints manually after a load (and not specify the automatic enable feature). This allows you to manually create the required indexes in parallel to save time before enabling the constraint. See the Oracle7 Server Tuning manual for more information about creating indexes in parallel.
If exceptions exist when you enable a constraint, an error is returned and the integrity constraint remains disabled. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot enable the constraint until all exceptions to the constraint are either updated or deleted.
To determine which rows violate the integrity constraint, include the EXCEPTIONS option in the ENABLE clause of a CREATE TABLE or ALTER TABLE statement. The EXCEPTIONS option places the ROWID, table owner, table name, and constraint name of all exception rows into a specified table. For example, the following statement attempts to enable the primary key of the DEPT table; if exceptions exist, information is inserted into a table named EXCEPTIONS:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;
Create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause. Create an exception table by submitting the script UTLEXCPT.SQL. The script creates a tabled named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and resubmitting the script.
If duplicate primary key values exist in the DEPT table and the name of the PRIMARY KEY constraint on DEPT is SYS_C00301, the following rows might be placed in the table EXCEPTIONS by the previous statement:
SELECT * FROM exceptions; ROWID OWNER TABLE_NAME CONSTRAINT ------------------ ------ ------------ ----------- 000003A5.000C.0001 SCOTT DEPT SYS_C00301 000003A5.000D.0001 SCOTT DEPT SYS_C00301
A more informative query would be to join the rows in an exception report table and the master table to list the actual rows that violate a specific constraint. For example:
SELECT deptno, dname, loc FROM dept, exceptions
WHERE exceptions.constraint = 'SYS_C00301'
AND dept.rowid = exceptions.row_id;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 RESEARCH DALLAS Rows that violate a constraint must be either updated or deleted from the table that contains the constraint. If updating exceptions, you must change the value that violates the constraint to a value consistent with the constraint or a null (if allowed). After updating or deleting a row in the master table, delete the corresponding rows for the exception in the exception report table to avoid confusion with later exception reports. The statements that update the master table and the exception report table should be in the same transaction to ensure transaction consistency.
For example, to correct the exceptions in the previous examples, the following transaction might be issued:
UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH'; DELETE FROM exceptions WHERE constraint = 'SYS_C00301'; COMMIT;
When you manage exceptions, your goal should be to eliminate all exceptions in your exception report table. After eliminating all exceptions, you must re-enable the constraint; the constraint is not automatically enabled after the exceptions are handled.
While you are correcting current exceptions for a table with the constraint disabled, other users can issue statements creating new exceptions.