Modification of Constraints

Modification of Constraints

It is possible to add, modify, or delete constraints at any time. The way to express such modifications depends on whether the constraint involved is associated with an attribute, a table, or a database schema.

Giving Names to Constraints

In order to modify or delete an existing constraint, it is essential that the constraint have a name. To do so, we precede the constraint by the keyword CONSTRAINT and a name for the constraint.

Example 1 :  We could rewrite line (2) of "Constraints and Triggers" Figure 1 to name the constraint that says attribute name is a primary key, as

Likewise, we could name the attribute-based CHECK constraint that appeared in "Constraints on Attributes and Tuples" Example 2 by:

Lastly, the following constraint:

is a rewriting of the tuple-based CHECK constraint in line (6) of "Tuple-Based CHECK Constraints" Figure 1 to give that constraint a name.

Altering Constraints on Tables

We discussed in "Deferring the Checking of Constraints" that we can switch the checking of a constraint from immediate to deferred or vice-versa with a SET CONSTRAINT statement. Other changes to constraints are effected with an ALTER TABLE statement. We previously discussed some uses of the ALTER TABLE statement in "Simple Table Declarations", where we used it to add and delete attributes.

These statements can also be used to alter constraints; ALTER TABLE is used for both attribute-based and tuple-based checks. We may drop a constraint with keyword DROP and the name of the constraint to be dropped. We may also add a constraint with the keyword ADD, followed by the constraint to be added. Note, however, that you cannot add a constraint to a table unless it holds for the current instance of that table.

Example 2 : Let us see how we would drop and add the constraints of Example 1 on relation MovieStar. The following sequence of three statements drops them:

Should we wish to reinstate these constraints, we would alter the schema for relation MovieStar by adding the same constraints, for instance:

Name Your Constraints

These constraints are now tuple-based, rather than attribute-based checks. We could not bring them back as attribute-based constraints.

The name is optional for these reintroduced constraints. On the other hand, we cannot rely on SQL remembering the dropped constraints. Therefore, when we add a former constraint we need to write the constraint again; we cannot refer to it by its former name.