Adding Constraints to Existing Table

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.

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.

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.
This Slideshow illustrates the operation of the EXCEPTIONS INTO clause, where the exception table has been created with the name EXCEPTION.

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.