composite primary key

A composite key has more than one attribute (field). In this example we store details of tracks on albums - we need to use three columns to get a unique key - each album may have more than one disk - each disk will have tracks numbered 1, 2, 3...
The primary key must be different for each row of the table. The primary key may not contain a null.

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

What is composite primary key?