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
|
