Composite Primary Keys in PostgreSQL
What is a Composite Primary Key?
A composite primary key, also known as a compound key or concatenated key, is a type of primary key that is composed of two or more columns in a database table. These columns, when combined, ensure the uniqueness of each record in the table.
Take the following example, where each team can have multiple persons as members. Each person can belong to multiple teams, but can only once belong to the same team.
In this example, the person with
person_id 100 is part of both team 1 and team 2. Despite the
person_id being the same in multiple entries, there is no conflict because the composite primary key (
person_id together) is unique in each case.
How to Create a Composite Primary Key in PostgreSQL
Here’s an example on how to create a table with a composite primary key in PostgreSQL:
CREATE TABLE team_members (
PRIMARY KEY (team_id, person_id)
team_members table, the primary key is made up of both
person_id. This means that the combination of
person_id must be unique across all rows in the table, ensuring that a person can only belong to a particular team once.