Maintaining Constraints

During the life of a database, you may add or drop constraints on tables.

Adding Constraints

To add one or more constraints use the ALTER TABLE statement on an existing table. Refer to the ALLBASE/SQL Reference Manual for syntax. Adding a constraint may require the following considerations:

You may want to later control the level at which constraint errors are checked with the SET CONSTRAINTS statement.
You may need to add constraints to tables or columns.
You should name the constraint for easy reference in case you later need to drop it.

The following statement adds a constraint to table PurchDB.Parts:

   isql=> ALTER TABLE PurchDB.Vendors

   > ADD CONSTRAINT CHECK (VendorNumber > 0) CONSTRAINT VndNum;

The added check constraint named VndNum ensures that PartNumber will be greater than zero.

Dropping Constraints

To drop one or more constraints, use the ALTER TABLE statement on an existing table. Refer to the ALLBASE/SQL Reference Manual for syntax. Dropping a constraint requires the following considerations:

In order to drop a constraint, you must know its name. The ConstraintID is the name you optionally gave the constraint when it was defined. If you did not name the constraint, it has a system-defined name. Table and view constraint names are stored in SYSTEM.CONSTRAINT.
You cannot drop a unique or primary key constraint if there exists a referential constraint referring to that unique or primary key. The referential constraint must be dropped first.

The following statement drops a constraint:

   isql=> ALTER TABLE PurchDB.Vendors DROP CONSTRAINT VndNum;

The constraint named VndNum that was added above is dropped from table PurchDB.Parts.

Maintaining Constraints

Technical documentation

» Table of Contents

» Index

Adding Constraints

Dropping Constraints