A composite primary key is a primary key consisting of multiple columns. Microsoft usually refers to these as multicolumn primary keys in its documentation.

This article provides an example of creating a composite primary key using Transact-SQL in SQL Server.

You can create a composite primary key just as you would create a single primary key, except that instead of specifying just one column, you provide the name of two or more columns, separated by a comma.

Like this:

CONSTRAINT PK_Name PRIMARY KEY (Column1, Column2)

Example 1 – Create a Composite Primary Key

Here’s an example of a database using a composite primary key.

For the purposes of this example, I’ll create a database called PK_Test:

CREATE DATABASE PK_Test;

Now that the database is created, let’s go ahead and create the tables.

USE PK_Test;

CREATE TABLE Musician (
MusicianId int NOT NULL,
FirstName varchar(60),
LastName varchar(60),
CONSTRAINT PK_Musician PRIMARY KEY (MusicianID)
);

CREATE TABLE Band (
BandId int NOT NULL,
BandName varchar(255),
CONSTRAINT PK_Band PRIMARY KEY (BandId)
);

CREATE TABLE BandMember (
MusicianId int NOT NULL,
BandId int NOT NULL,
CONSTRAINT PK_BandMember PRIMARY KEY (MusicianID, BandId),
CONSTRAINT FK_BandMember_Band FOREIGN KEY (BandId) REFERENCES Band(BandId),
CONSTRAINT FK_BandMember_Musician FOREIGN KEY (MusicianId) REFERENCES Musician(MusicianId)
);

In this example, the BandMember table has a multicolumn primary key. In this case each column in the primary key is also a foreign key to the primary key of another table, but this is not a requirement.

The reasoning behind the above database design is that, a musician could potentially be a member of many bands. Also, each band can have many musicians. So we have a many-to-many relationship. This is why the BandMember table is created – it is used as a cross-reference table between the Musician table and the Band table.

This particular case supports a composite primary key, because a musician being a member of a band should be a unique occurrence. In other words, we wouldn’t want multiple rows with a musician being a member of the same band. That would violate data integrity. It could also cause havoc when trying to maintain referential integrity in the even that we ever create a relationship between this table and another (which we do here).

Example 2 – Insert Data

Having just run the above code, I can now load the database with data:

INSERT INTO Musician
VALUES 
( 1, 'Ian', 'Paice' ),
( 2, 'Roger', 'Glover' ),
( 3, 'Richie', 'Blackmore' ),
( 4, 'Rod', 'Evans' ),
( 5, 'Ozzy', 'Osbourne' );

INSERT INTO Band
VALUES 
( 1, 'Deep Purple' ),
( 2, 'Rainbow' ),
( 3, 'Whitesnake' ),
( 4, 'Iron Maiden' );

INSERT INTO BandMember
VALUES 
( 1, 1 ),
( 1, 3 ),
( 2, 1 ),
( 2, 2 ),
( 3, 1 ),
( 3, 2 ),
( 4, 1 );

Example 3 – Basic Query

Now that data is in our database, let’s run a query to return some of that data.

Here’s a basic query:

SELECT 
  CONCAT(m.FirstName, ' ', m.LastName) AS 'Musician',
  b.BandName AS 'Band'
FROM Musician m
JOIN BandMember bm
  ON m.MusicianId = bm.MusicianId
JOIN Band b 
  ON b.BandId = bm.BandId AND m.MusicianId = bm.MusicianId;

Result:

+------------------+-------------+
| Musician         | Band        |
|------------------+-------------|
| Ian Paice        | Deep Purple |
| Ian Paice        | Whitesnake  |
| Roger Glover     | Deep Purple |
| Roger Glover     | Rainbow     |
| Richie Blackmore | Deep Purple |
| Richie Blackmore | Rainbow     |
| Rod Evans        | Deep Purple |
+------------------+-------------+

So as expected, this only returns those musicians and bands that have an entry in the BandMember reference table.

Example 4 – Slightly Modified Query

Here’s a modified version of the above query that presents the results in a different way:

SELECT 
  b.BandName AS 'Band',
  STRING_AGG(CONCAT(m.FirstName, ' ', m.LastName), ', ') AS 'Musicians'
FROM Musician m
JOIN BandMember bm
  ON m.MusicianId = bm.MusicianId
JOIN Band b 
  ON b.BandId = bm.BandId AND m.MusicianId = bm.MusicianId
GROUP BY b.BandName;

Result:

+-------------+------------------------------------------------------+
| Band        | Musicians                                            |
|-------------+------------------------------------------------------|
| Deep Purple | Ian Paice, Roger Glover, Richie Blackmore, Rod Evans |
| Rainbow     | Roger Glover, Richie Blackmore                       |
| Whitesnake  | Ian Paice                                            |
+-------------+------------------------------------------------------+

Here the results are grouped by band, and all musicians for each band are all displayed as a comma separated list in a single field.

To do this I use the STRING_AGG() function to concatenate the musicians.

Composite Foreign Key

The problem with the above example is that most of the data is out of date. Some of these musicians have actually left those bands. And some have left and then returned at a later date.

How can we deal with this?

We could create another reference table to record the time period each musician is a member of each band. Such a table would need to reference the BandMember table via a foreign key. And seeing as this table has a composite primary key, we would need to use a composite foreign key on the new table that references it.

See How to Create a Composite Foreign Key in SQL Server for an example. That article uses the same example as above, except with an extra table with a composite foreign key that references the above composite primary key.