HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

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.

MPE/iX 5.5 Documentation