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.

Practice

1. What is a composite primary key in a database table?

easy

A. A primary key that uses only one column to identify rows.

B. A primary key made up of two or more columns combined to uniquely identify a row.

C. A key that allows duplicate values in the table.

D. A foreign key that references multiple tables.

Solution

1. Step 1: Understand primary key basics

   A primary key uniquely identifies each row in a table.

2. Step 2: Define composite primary key

   A composite primary key uses two or more columns together to ensure uniqueness.

3. Final Answer:

   A primary key made up of two or more columns combined to uniquely identify a row. -> Option B

4. Quick Check:

   Composite primary key = multiple columns [OK]

Hint: Composite keys combine columns to ensure unique rows [OK]

Common Mistakes:

• Thinking a primary key can have duplicates
• Confusing composite key with foreign key
• Assuming composite key uses only one column

2. Which of the following is the correct syntax to define a composite primary key on columns order_id and product_id in SQL?

easy

A. PRIMARY KEY (order_id, product_id)

B. PRIMARY KEY order_id, product_id

C. PRIMARY KEY order_id & product_id

D. PRIMARY KEY (order_id + product_id)

Solution

1. Step 1: Recall SQL syntax for composite keys

   Composite keys are defined by listing columns inside parentheses separated by commas.

2. Step 2: Match correct syntax

   PRIMARY KEY (order_id, product_id) uses parentheses and comma correctly: PRIMARY KEY (order_id, product_id).

3. Final Answer:

   PRIMARY KEY (order_id, product_id) -> Option A

4. Quick Check:

   Composite key syntax uses parentheses and commas [OK]

Hint: Use parentheses and commas for composite keys [OK]

Common Mistakes:

• Omitting parentheses around columns
• Using symbols like & or + incorrectly
• Listing columns without commas

3. Given the table OrderDetails with composite primary key (order_id, product_id), what will this query return?

SELECT * FROM OrderDetails WHERE order_id = 101;

medium

A. No rows because both keys must be specified.

B. Only one row with order_id 101 and any product_id.

C. An error because product_id is missing in WHERE clause.

D. All rows where order_id is 101, regardless of product_id.

Solution

1. Step 1: Understand composite key usage in queries

   Composite keys uniquely identify rows, but queries can filter by any column(s).

2. Step 2: Analyze the query filter

   The query filters only by order_id = 101, so it returns all rows with that order_id regardless of product_id.

3. Final Answer:

   All rows where order_id is 101, regardless of product_id. -> Option D

4. Quick Check:

   Filtering by part of composite key returns matching rows [OK]

Hint: Filtering by part of composite key returns matching rows [OK]

Common Mistakes:

• Assuming missing key column causes error
• Thinking only full composite key filters work
• Expecting only one row when multiple match

4. You try to create a table with this SQL:

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

What is the problem?

medium

A. Missing parentheses around the composite key columns.

B. student_id and course_id cannot be primary keys.

C. PRIMARY KEY must be declared after all columns.

D. Composite keys require UNIQUE keyword instead.

Solution

1. Step 1: Check syntax for composite primary key

   Composite keys require parentheses around the column list in PRIMARY KEY declaration.

2. Step 2: Identify error in given SQL

   The statement uses PRIMARY KEY student_id, course_id without parentheses, causing syntax error.

3. Final Answer:

   Missing parentheses around the composite key columns. -> Option A

4. Quick Check:

   Composite keys need parentheses [OK]

Hint: Always use parentheses for composite primary keys [OK]

Common Mistakes:

• Omitting parentheses in PRIMARY KEY clause
• Confusing primary key with unique constraint
• Placing PRIMARY KEY before column definitions

5. You have a table Attendance with columns student_id, class_date, and session. You want to ensure each student can only have one attendance record per class date and session. Which composite primary key should you define?

hard

A. (class_date, session)

B. (student_id, class_date)

C. (student_id, class_date, session)

D. (student_id, session)

Solution

1. Step 1: Understand uniqueness requirement

   Each student must have only one record per class date and session, so all three columns combined must be unique.

2. Step 2: Choose composite key covering all uniqueness factors

   Composite key must include student_id, class_date, and session to enforce this rule.

3. Final Answer:

   (student_id, class_date, session) -> Option C

4. Quick Check:

   Composite key covers all uniqueness columns [OK]

Hint: Include all columns that define uniqueness in composite key [OK]

Common Mistakes:

• Leaving out session or class_date from key
• Using only two columns when three needed
• Confusing foreign keys with primary keys