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.
