Composite primary keys in SQL

Introduction

A composite primary key uses more than one column to uniquely identify each row in a table. It helps when one column alone can't guarantee uniqueness.

When you have a table that records student grades for different courses, and you want to identify each record by both student ID and course ID.

When you track orders in a store where each order can have multiple products, and you want to identify each item by order ID and product ID.

When you log attendance for employees by date and employee ID, and both together identify each attendance record.

When you have a table of book loans in a library, identified by member ID and book ID together.

Syntax

SQL

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...,
  PRIMARY KEY (column1, column2)
);

The PRIMARY KEY clause lists two or more columns inside parentheses, separated by commas.

All columns in the composite key together must be unique for each row.

Examples

This table uses student_id and course_id together as the unique key.

SQL

CREATE TABLE Enrollment (
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  PRIMARY KEY (student_id, course_id)
);

Each attendance record is unique by employee and date.

SQL

CREATE TABLE Attendance (
  employee_id INT,
  attendance_date DATE,
  status VARCHAR(10),
  PRIMARY KEY (employee_id, attendance_date)
);

An order can have many products, identified by order_id and product_id.

SQL

CREATE TABLE Orders (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

Sample Program

This creates a table where each loan is uniquely identified by member_id and book_id together. Then it inserts three loans and selects all rows.

SQL

CREATE TABLE LibraryLoans (
  member_id INT,
  book_id INT,
  loan_date DATE,
  PRIMARY KEY (member_id, book_id)
);

INSERT INTO LibraryLoans (member_id, book_id, loan_date) VALUES
(1, 101, '2024-06-01'),
(1, 102, '2024-06-02'),
(2, 101, '2024-06-03');

SELECT * FROM LibraryLoans;

Important Notes

Composite primary keys ensure uniqueness using multiple columns combined, not just one.

They can make queries more complex because you must always use all key columns to identify a row.

Use composite keys when no single column can uniquely identify rows, but a combination can.

Summary

Composite primary keys use two or more columns to uniquely identify rows.

They are useful when one column alone is not unique.

Always include all key columns in queries that use composite keys.