Composite Primary Keys in SQLite

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 made up of two or more columns in a database table. These columns, when combined, ensure the uniqueness of each record in the table.

Consider 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 (team_id and person_id together) is unique in each case.

How to Create a Composite Primary Key in SQLite

Here’s an example on how to create a table with a composite primary key in SQLite:

CREATE TABLE team_members (
    team_id INTEGER,
    person_id INTEGER,
    PRIMARY KEY (team_id, person_id)
);

In this team_members table, the primary key is composed of both team_id and person_id. This implies that the combination of team_id and person_id must be unique across all rows in the table, ensuring that a person can only belong to a particular team once.