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 need 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