HP 3000 Manuals

ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX

Defining and Dropping Table Constraints 

By using either the CREATE TABLE or the ALTER TABLE statement, you can
add any type of table constraint (check, unique, or referential) on an
existing column or a new column and you can drop any type of constraint.

The following example uses a CREATE TABLE statement to define a table
level check constraint based on the Date and Time columns for the Events
table:

     CREATE PUBLIC TABLE Events
     (Event CHAR(30),
      Coordinator CHAR(20),
      SponsorClub CHAR(15),
      Date DATE DEFAULT CURRENT_DATE,
       CHECK (Date >= '1992-02-21' AND Time > '00:00:00') CONSTRAINT DateTimeCheck, 
      Time TIME,
      FOREIGN KEY (Coordinator, SponsorClub)
         REFERENCES Members (MemberName, Club) CONSTRAINT Events_FK)
     IN RecFS;

Note that you can define a table level constraint at any point in the
column definition list of a CREATE TABLE statement.

To define a column level check constraint on the Date column of the
Events table, the CREATE TABLE statement might look like this:

     CREATE PUBLIC TABLE Events
     (Event CHAR(30),
      Coordinator CHAR(20),
      SponsorClub CHAR(15),
      Date DATE DEFAULT CURRENT_DATE  CHECK (Date >= '1992-02-21') CONSTRAINT DateCheck, 
      Time TIME,
      FOREIGN KEY (Coordinator, SponsorClub)
         REFERENCES Members (MemberName, Club) CONSTRAINT Events_FK)
     IN RecFS;

The following examples illustrate use of the ALTER TABLE statement to add
a column, add a constraint, and drop a constraint in the Recreation
database:

Adding a Column to the Recreation Database 

You can use the ALTER TABLE statement to add one or more columns to a
table.  In the following example the ClubContact column is added to the
Clubs table.  The new column will contain the member name of the person
designated as the contact for the club.  The column's value cannot be
null.

        ALTER TABLE RecDB.Clubs
      ADD COLUMN ClubContact CHAR(20) NOT NULL 

Adding a Constraint to the Recreation Database 

With the ALTER TABLE statement, you can add any type of constraint to a
table without having to drop the table and recreate it with the new
constraint.  In the following example, a referential constraint is added
to the Clubs table.  The new constraint ensures that any club contact
name exists as a member name in the Members table.

     Note that you must have REFERENCES authority on the Members table to add a constraint 
     that references a column in the Members table. 

        ALTER TABLE RecDB.Clubs
      ADD CONSTRAINT    FOREIGN KEY (ClubContact)     REFERENCES RecDB.Members (MemberName) CONSTRAINT (Contact_PK) 

When the ClubContact column of the RecDB.Clubs table is modified, a club
contact name that does not appear in the Members table will cause a
referential constraint error.

Note that you cannot add a constraint to a view without dropping the view
and recreating it.

Dropping a Constraint from the Recreation Database 

A constraint can be dropped from a table without having to drop the table
and recreate it.  In the following example, a referential constraint is
dropped from the Clubs table.  After the constraint is dropped, future
modifications to the ClubContact column of the Clubs table will not
involve a check for a corresponding MemberName in the Members table.

     ALTER TABLE RecDB.Clubs  DROP CONSTRAINT Contact_PK 

MPE/iX 5.0 Documentation