Composite primary keys

A primary key^[1] allows us to link two tables (with the PRIMARY KEY <- FOREIGN KEY relationship).

A composite primary key is just a primary key that is based on two or more columns. This means that related foreign keys must be based in the same number of columns, to match.

In addition, since primary keys create an INDEX and UNIQUE constraint, having a composite primary key creates a composite index and a composite unique constraint.

If you have a 2-column primary key, neither column separately must be unique. Similarly, there is no index for either column.

However, you won't be able to have two rows where both sets of columns have the same value. Also, an index will be created for the column pair.

Let's say you have this table:

CREATE TABLE accounts (
    account_number INTEGER,
    sort_code INTEGER,
    user_name TEXT,
    PRIMARY KEY(account_number, sort_code)
);

And then we fill it in with this data:

account_number	sort_code	user_name
81795745	090566	Rolf Smith
19876543	232344	Bob Smith
19876543	151617	Anne Pun
19876543	909900	Charlie Robert
12345678	005344	Susan Williams

This is totally OK, because even though Bob, Anne, and Charlie have the same account number, their sort codes are different (that's a number used in UK banking to differentiate between bank branches that own the account).

However this would not be OK:

account_number	sort_code	user_name
19876543	909900	Anne Pun
19876543	909900	Charlie Robert

Now the accounts are the same, and this would be disallowed in PostgreSQL.

You could naturally add more columns to the composite primary key, to include the name for example. But since names are fairly non-unique, it's probably not a good approach in this situation!

Composite foreign keys

To define a composite foreign key^[2] we can do something similar:

CREATE TABLE customer_details (
    id SERIAL PRIMARY KEY,
    full_name TEXT,
    address TEXT,
    mobile TEXT,
    account_number INTEGER,
    sort_code INTEGER,
    FOREIGN KEY (account_number, sort_code) REFERENCES accounts (account_number, sort_code)
);

Note that because the referenced and referencing table columns have the same name (account_number and sort_code), that can be shortened to this:

CREATE TABLE customer_details (
    id SERIAL PRIMARY KEY,
    full_name TEXT,
    address TEXT,
    mobile TEXT,
    account_number INTEGER,
    sort_code INTEGER,
    FOREIGN KEY (account_number, sort_code) REFERENCES accounts
);

That shortcut can also be used when doing inline REFERENCES, as detailed in the official documentation.

---

1. 5.3.4 Primary Keys (PostgreSQL Official Documentation) (opens new window) ↩︎

2. 5.3.5. Foreign Keys (PostgreSQL Official Documentation) (opens new window) ↩︎