Introduction of Column- and Table Constraints

If a constraint is defined within the create table command or added using the alter table command, the constraint is automatically enabled. A constraint can be disabled using the command

alter table disable constraint | primary key | unique[]
[cascade];

To disable a primary key, one must disable all foreign key constraints that depend on this primary key. The clause cascade automatically disables foreign key constraints that depend on the (disabled) primary key. Example: Disable the primary key of the table DEPT and disable the foreign key constraint in the table EMP:

alter table DEPT disable primary key cascade;

In order to enable an integrity constraint, the clause enable is used instead of disable. A constraint can only be enabled successfully if no tuple in the table violates the constraint. Otherwise an error message is displayed. Note that for enabling/disabling an integrity constraint it is important that you have named the constraints. In order to identify those tuples that violate an integrity constraint whose activation failed, one can use the clause exceptions into EXCEPTIONS with the alter table statement.

EXCEPTIONS is a table that stores information about violating tuples.3 Each tuple in this table is identified by the attribute ROWID. Every tuple in a database has a pseudo-column ROWID that is used to identify tuples. Besides the rowid, the name of the table, the table owner as well as the name of the violated constraint are stored.

Example: Assume we want to add an integrity constraint to our table EMP which requires that each manager must earn more than 4000:

alter table EMP add constraint manager sal
check(JOB != ’MANAGER’ or SAL >= 4000)
exceptions into EXCEPTIONS;

If the table EMP already contains tuples that violate the constraint, the constraint cannot be activated and information about violating tuples is automatically inserted into the table

EXCEPTIONS.

Detailed information about the violating tuples can be obtained by joining the tables EMP and EXCEPTIONS, based on the join attribute ROWID:

select EMP._, CONSTRAINT from EMP, EXCEPTIONS where EMP.ROWID = EXCEPTIONS.ROW ID;

Before this table can be used, it must be created using the SQL script utlexcept.sql which can be found in the directory $ORACLE HOME/rdbms/admin.

Tuples contained in the query result now can be modified (e.g., by increasing the salary of managers) such that adding the constraint can be performed successfully. Note that it is important to delete “old” violations from the relation EXCEPTIONS before it is used again. If a table is used as a reference of a foreign key, this table can only be dropped using the command

drop table cascade constraints;.

All other database objects that refer to this table remain in the database system, but they are not valid. Information about integrity constraints, their status (enabled, disabled) etc. is stored in the data dictionary, more precisely, in the tables USER CONSTRAINTS and USER CONS CONSTRAINTS.