Table Constraint Declarations

Following all column definitions, table constraints can be defined. Table constraints are similar to column constraints and are used to specify multi column PRIMARY/UNIQUE KEY, KEY and FOREIGN KEY  definitions. The syntax for specifying table constraints is as follows.

tab_constraint:
        CONSTRAINT constraint_name {key_def | forkey_def}

constraint_name	Optional name for a column constraint.
key_def	Indexes can be defined to allow for fast navigation or to guarantee no duplicate entries depending on the index type. For more information, refer to the Key (Index) Specification section.
forkey_def	Foreign indexes can be defined to allow for fast navigation based on relationship or to validate a correct range of data values for a given field or column. For more information, refer to the Foreign Key (References) Specification section.

Examples

Example 1: Compound Sort Key

create table sponsor(
    name        char(50) primary key,
    addr        char(40),
    city        char(20),
    state       char(2),
    zip         char(5),
    constraint geo_loc key(state, city)
);

The above example creates a sort key (index) that can be used to access the rows of the sponsor table order by state and by city within each state.

Examples 2: Compound Primary Key

create table product 
(
    prod_id smallint primary key,
    prod_desc CHAR(39) NOT NULL,
    price float,
    cost float,
    constraint prod_pricing key (price desc, prod_id)
);

create table outlet 
(
    loc_id char(3) primary key,
    city char(17) not null,
    state char(2) not null,
    region smallint not null,
    constraint loc_geo key (state, city)
);

create table on_hand 
(
    loc_id char(3) not null references outlet(loc_id),
    prod_id smallint not null references product,
    quantity int not null,
    constraint on_hand_key primary key (loc_id, prod_id)
);

The above example create a compound PRIMARY KEY in the on_hand table.

Because the loc_id and prod_id columns are used in the PRIMARY KEY, those values cannot be altered OR contain a NULL value.

This example also contains additional examples of compound sort keys in the outlet and product tables.