Managing Integrity Constraints

• Managing Constraints That Have Associated Indexes

• Enabling and Disabling Integrity Constraints Upon Definition

• Enabling and Disabling Existing Integrity Constraints

• Dropping Integrity Constraints

• Reporting Constraint Exceptions

enabled	When a constraint is enabled, the rule defined by the constraint is enforced on the data values in the columns that define the constraint. The definition of the constraint is stored in the data dictionary.
disabled	When a constraint is disabled, the rule defined by the constraint is not enforced on the data values in the columns included in the constraint; however, the definition of the constraint is retained in the data dictionary.

You can think of an integrity constraint as a statement about the data in a database. This statement is always not false when the constraint is enabled. However, the statement may or may not be true when the constraint is disabled because data in violation of the integrity constraint can be in the database.

To enforce the rules defined by integrity constraints, the constraints should always be enabled. In certain situations it is desirable to temporarily disable the integrity constraints of a table for the following performance reasons:

• when loading large amounts of data into a table using SQL*Loader

• when performing batch operations that make massive changes to a table (for example, changing every employee's number by adding 1000 to the existing number)

• when importing or exporting one table at a time

While a constraint is enabled, no row violating the constraint can be inserted into the table. While the constraint is disabled, though, such a row can be inserted; this row is known as an exception to the constraint. While exceptions to a constraint can exist in a table, the constraint cannot be enabled. The rows that violate the constraint must be either updated or deleted in order for the constraint to be enabled.

See Also: You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. See "Reporting Constraint Exceptions" .

Managing Constraints That Have Associated Indexes

When disabling or dropping UNIQUE key and PRIMARY KEY integrity constraints, consider the following issues:

• The constraint's associated index will be dropped when the constraint is dropped or disabled.

• While enabled foreign keys reference a primary or unique key, you cannot disable or drop the primary or unique key constraint.

Because unique and primary keys have associated indexes, you should factor in the cost of dropping and creating indexes when considering whether to disable or drop a UNIQUE or PRIMARY KEY constraint. If the associated index for a UNIQUE key or PRIMARY KEY constraint is extremely large, you may save time by leaving the constraint enabled rather than dropping and re-creating the large index.

Enabling and Disabling Integrity Constraints Upon Definition

Enabling Constraints Upon Definition

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.

To enable a UNIQUE key or PRIMARY KEY, which creates an associated index, the owner of the table also needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.

Disabling Constraints Upon Definition

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 because of rows of the table that violate the integrity constraint. The definition of the constraint is allowed because its rule is not enforced.

See Also: For more information about constraint exceptions, see "Reporting Constraint Exceptions" .

Enabling and Disabling Existing Integrity Constraints

Enabling Disabled Constraints

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 may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled.

To enable a UNIQUE key or PRIMARY KEY (which creates an associated index), the owner of the table also needs a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege.

Disabling Enabled Constraints

ALTER TABLE dept

   DISABLE CONSTRAINT dname_ukey;

ALTER TABLE dept

   DISABLE PRIMARY KEY,

   DISABLE UNIQUE (dname, loc);

To disable or drop a UNIQUE key or PRIMARY KEY constraint and all dependent FOREIGN KEY constraints in a single step, use the CASCADE option of the DISABLE or DROP clauses. For example, the following statement disables a PRIMARY KEY constraint and any FOREIGN KEY constraints that depend on it:

ALTER TABLE dept

   DISABLE PRIMARY KEY CASCADE;

See Also: For more information about constraint exceptions, see "Reporting Constraint Exceptions" .

Dropping Integrity Constraints

ALTER TABLE dept

   DROP UNIQUE (dname, loc);

ALTER TABLE emp

   DROP PRIMARY KEY,

   DROP CONSTRAINT dept_fkey;

Dropping UNIQUE key and PRIMARY KEY constraints drops the associated indexes. Also, if FOREIGN KEYs reference a UNIQUE or PRIMARY KEY, you must include the CASCADE CONSTRAINTS clause in the DROP statement, or you cannot drop the constraint.

Reporting Constraint Exceptions

If exceptions exist when a constraint is enabled, 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, issue the CREATE TABLE or ALTER TABLE statement with the EXCEPTIONS option in the ENABLE clause. 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, and if exceptions exist, information is inserted into a table named EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;

Note: You must create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause before enabling the constraint. You can create an exception table by submitting the script UTLEXCPT.SQL, which creates a table named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and re-submitting 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, as shown in the following 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

All rows that violate a constraint must be either updated or deleted from the table containing the constraint. When updating exceptions, you must change the value violating the constraint to a value consistent with the constraint or a null. After the row in the master table is updated or deleted, the corresponding rows for the exception in the exception report table should be deleted 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.

To correct the exceptions in the previous examples, you might issue the following transaction:

UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH';

DELETE FROM exceptions WHERE constraint = 'SYS_C00301';

COMMIT;

When managing exceptions, the goal is to eliminate all exceptions in your exception report table.

Note: While you are correcting current exceptions for a table with the constraint disabled, other users may issue statements creating new exceptions.

See Also: The exact name and location of the UTLEXCPT.SQL script is operating system-specific. For more information, see your operating system-specific Oracle documentation.