Composite Primary Keys in MySQL
What is a Composite Primary Key?
A composite primary key, also referred to as a compound key or concatenated key, is a type of primary key that consists of two or more columns in a database table. These columns, when taken together, ensure the uniqueness of each row in the table.
Consider the following example, where each team can have multiple individuals as members. Each person can be a member of multiple teams, but they can only be a member of the same team once.
In this scenario, the person with
person_id 100 is a member of both team 1 and team 2. Although the
person_id is repeated in multiple rows, there’s no conflict because the composite primary key (
person_id in combination) is unique for each row.
How to Create a Composite Primary Key in MySQL
The following is an example of how to create a table with a composite primary key in MySQL:
CREATE TABLE team_members (
PRIMARY KEY (team_id, person_id)
team_members table, the primary key comprises both
person_id. This implies that the pair of
person_id has to be unique for all rows in the table, ensuring that an individual can only be a member of a specific team once.