Managing Constraints   «Prev  Next»

Lesson 7 Adding constraints to an existing table
ObjectiveUse the EXCEPTIONS INTO clause of the CONSTRAINT definition.

Adding Constraints to Existing Table

Use the EXCEPTIONS INTO clause of the CONSTRAINT definition

Up to this point, we discussed adding constraints to tables when the tables are created. If you want to add a constraint to a table that already exists, you can use the ALTER TABLE command. Adding a constraint to a table that contains no data is no problem. The situation becomes more complex, however, if the table contains data that does not conform to the limitations imposed by the constraint.

Adding constraints

When you use the ALTER TABLE command to add a constraint, you have three choices of how the constraint deals with data:
  1. You can set the constraint with the keyword DISABLED. This command will create the constraint, but will disable its operation. You can enable the constraint with a later ALTER TABLE command.
  2. You can set the constraint with the keywords ENABLE VALIDATE. This command will cause the constraint to be immediately enforced on the data in the table. If any of the data in the table violates the constraint, the ALTER TABLE statement will return an error. This setting is the default.
  3. You can set the constraint with the keywords ENABLE NOVALIDATE. The command will not check the existing data, but will be enforced on all subsequent data added to the table.

EXCEPTIONS INTO

You can also use the EXCEPTIONS INTO tablename clause as part of your definition of a constraint, either when you add a constraint to a table or when you enable a constraint. The exception table must be created before you can use this option because the EXCEPTIONS INTO clause causes all data that violates the constraint to be written to an exception table. A script for creating an exception table is called UTLEXCPT.SQL and comes with your Oracle database.
The following series of images illustrates the operation of the EXCEPTIONS INTO clause, where the exception table has been created with the name EXCEPTION.

1) The AUCTION_ID table contains a duplicate a value in the AUCTION_ID column.
1) The AUCTION_ID table contains a duplicate a value in the AUCTION_ID column.

2) A PRIMARY KEY constraint is added to the AUCTION_ID column with the EXCEPTIONS INTO clause.
2) A PRIMARY KEY constraint is added to the AUCTION_ID column with the EXCEPTIONS INTO clause.

3) The ROWID of the offending row is written to the EXCEPTIONS table, along with other identifying information
3) The ROWID of the offending row is written to the EXCEPTIONS table, along with other identifying information.

  1. The AUCTION_ID table contains a duplicate value in the AUCTION_ID column.
  2. A PRIMARY KEY constraint is added to the AUCTION_ID column with the EXCEPTIONS INTO clause.
  3. The ROWID of the offending row is written to the EXCEPTIONS table, along with other identifying information.

Constraint Added with Exceptions Into

To create a unique constraint, just replace the primary key clause with unique. When you attempt to create a unique index on a table that already has data in it, the command will fail if any duplicates exist. If the create unique index statement succeeds, any future attempt to insert (or update) a row that would create a duplicate key will fail and result in this error message 

ERROR at line 1:
ORA-00001: unique constraint (BOOKSHELF_AUTHOR.BA_PK) violated



Deleting Constraints

You can delete a constraint with the ALTER TABLE DROP constraint command, where constraint is the name of the constraint. By default, dropping a constraint will not drop any other constraints that are dependent on it. This could result in errors. For example, if you drop a primary key constraint, any FOREIGN KEY constraints that depend on it are invalidated. To avoid this problem, you can use the DROP constraint clause with the CASCADE keyword, which will automatically drop any dependent constraints. The next lesson explains the particulars of implementing constraints.