# Composite primary keys
A primary key 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
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:
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:
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 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 (
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.