SQL for Data Analytics · Composite primary keys

Composite primary keys

Composite Primary Keys5 min

Composite Primary Keys5 min

When the pair is enough

A joining table's primary key can be the two foreign keys together, or a new ID column of its own. Which one to use depends on what it takes to pick out exactly one row.

A student and a class are a many-to-many relationship, with a joining table between them. You could call it StudentClass, or enrollment. Each row is one student in one class. In this joining table, a student can't be enrolled in the same class twice, so no two rows can have the same student and the same class. The pair of IDs picks out one row, and it can be the primary key.

StudentID ClassID GPA
1 1 3.0
1 2 3.7
2 1 2.5

StudentID 1 is on two rows, and so is ClassID 1. The pair is different on every row.

In the CREATE TABLE, the joining table gets no ID column. Both StudentID and ClassID go in the PRIMARY KEY, and each one is also a FOREIGN KEY.

CREATE TABLE StudentClass (
  StudentID INT,
  ClassID INT,
  GPA DECIMAL(2, 1),
  PRIMARY KEY (StudentID, ClassID),
  FOREIGN KEY (StudentID) REFERENCES Student(ID),
  FOREIGN KEY (ClassID) REFERENCES Class(ID)
);
When the pair isn't enough

Suppose a shop lets a user pay off an order over time, in several payments. A user can make many payments, and an order can have many payments, so a Payments table can look like a joining table between users and orders. But the same user can pay on the same order more than once, on different days, and even for the same amount. OrderID and UserID together don't pick out one payment.

OrderID UserID AmountPaid PaidOn
7 3 25.00 2024-03-01
7 3 25.00 2024-04-01

With PRIMARY KEY (OrderID, UserID), the second payment would be refused, because its pair is already in the table. So each payment gets an ID of its own, and that ID is the primary key. OrderID and UserID stay in the table as foreign keys.

CREATE TABLE Payments (
  ID INT,
  OrderID INT,
  UserID INT,
  AmountPaid DECIMAL(10, 2),
  PRIMARY KEY (ID),
  FOREIGN KEY (OrderID) REFERENCES Orders(ID),
  FOREIGN KEY (UserID) REFERENCES Users(ID)
);

The other way out is to add more columns to the key, until the combination is unique again. In a payment system, the usual choice is an ID for each payment.

A library keeps track of which members have borrowed which books. A member can borrow the same book again months later. Should the Loans table's primary key be (MemberID, BookID), or should it get a new ID?

How I'd answer it

It needs a new ID. A member who borrows the same book twice would need two rows with the same MemberID and BookID, and the primary key would refuse the second one. With a LoanID as the key, every loan gets its own row, and MemberID and BookID are foreign keys.

If the library only wanted to record which members had ever borrowed which books, once each, the pair would be enough.

Writing a joining table

The script in the box makes a database named library with Authors and Books tables. Add the CREATE TABLE for the joining table between them, and run the script. Make the two columns together its primary key, and make the database refuse a book or an author it doesn't have.

Write the CREATE TABLE for BooksAuthors. It has two columns, BookID and AuthorID. Each row is one author of one book, and the same author can't be listed twice for the same book.

⌘/Ctrl + Enter

Both columns go in one PRIMARY KEY, with a comma between them. Then add a FOREIGN KEY for each column, pointing at the table it comes from. If BooksAuthors was already made wrong by an earlier run, add DROP TABLE IF EXISTS BooksAuthors; before your CREATE TABLE.