Examples of the Multiple-Column Constraint Format

The following example creates a standard table, called accounts, with a unique constraint, called acc_num, using the multiple-column constraint format. (Nothing in this example, however, would prevent you from using the single-column constraint format to define this constraint.)

CREATE TABLE accounts
   (acc_name CHAR(12), 
    acc_num  SERIAL, 
    UNIQUE  (acc_num) CONSTRAINT acc_num) 

For constraint names, see Declaring a Constraint Name.

Defining Check Constraints Across Columns

When you use the multiple-column constraint format to define check constraints, a check constraint can apply to more than one column in the same table. (You cannot, however, create a check constraint whose condition uses a value from a column in another table.)

This example compares two columns, acct1 and acct2, in the new table:

CREATE TABLE my_accounts 
   (
   chk_id   SERIAL PRIMARY KEY,
   acct1    MONEY,
   acct2    MONEY,
   CHECK (0 < acct1 AND acct1 < 99999),
   CHECK (0 < acct2 AND acct2 < 99999),
   CHECK (acct1 > acct2)
   )

In this example, the acct1 column must be greater than the acct2 column, or the insert or update fails.

Defining Composite Primary and Foreign Keys

When you use the multiple-column constraint format, you can create a composite key. A composite key specifies multiple columns for a primary-key or foreign-key constraint.

The next example creates two tables. The first table has a composite key that acts as a primary key, and the second table has a composite key that acts as a foreign key.

CREATE TABLE accounts (
   acc_num INTEGER,
   acc_type INTEGER,
   acc_descr CHAR(20),
   PRIMARY KEY (acc_num, acc_type))

CREATE TABLE sub_accounts (
   sub_acc INTEGER PRIMARY KEY,
   ref_num INTEGER NOT NULL,
   ref_type INTEGER NOT NULL,
   sub_descr CHAR(20),
   FOREIGN KEY (ref_num, ref_type) REFERENCES accounts
      (acc_num, acc_type))

In this example, the foreign key of the sub_accounts table, ref_num and ref_type, references the composite key, acc_num and acc_type, in the accounts table. If, during an insert or update, you tried to insert a row into the sub_accounts table whose value for ref_num and ref_type did not exactly correspond to the values for acc_num and acc_type in an existing row in the accounts table, the database server would return an error.

A referential constraint must have a one-to-one relationship between referencing and referenced columns. In other words, if the primary key is a set of columns (a composite key), then the foreign key also must be a set of columns that corresponds to the composite key.

Because of the default behavior of the database server, when you create the foreign-key reference, you do not have to reference the composite-key columns (acc_num and acc_type) explicitly. You can rewrite the references section of the previous example as follows:

FOREIGN KEY (ref_num, ref_type) REFERENCES accounts

Options Clauses

Options:
 
|--+--------------------+--------------------------------------->
   |  (1)               |
   '--------WITH CRCOLS-'
 
>--+---------------------------------+-------------------------->
   |  (2)                       (3)  |
   '--------| Storage Options |------'
 
>--+----------------------------+------------------------------->
   |                       (4)  |
   '-| LOCK MODE Options |------'
 
>--+-------------------------------------+----------------------|
   |                                (5)  |
   '-| USING Access-Method Clause |------'