Why Composite primary keys in SQL? - Purpose & Use Cases

The Big Idea

What if one key isn't enough to find your data? Discover how combining keys solves this puzzle!

The Scenario

Imagine you have a big notebook where you write down orders from customers. Each page has a customer name and a product name. To find a specific order, you try to remember just the customer or just the product, but many pages have the same customer or the same product. It becomes very hard to find the exact order you want.

The Problem

Using only one piece of information like customer name or product name to find orders is slow and confusing. You might pick the wrong order because many orders share the same customer or product. Writing down extra notes to keep orders unique is messy and easy to make mistakes.

The Solution

Composite primary keys let you use two or more pieces of information together to uniquely identify each order. This means you can quickly and correctly find the exact order by looking at both customer and product together, avoiding confusion and mistakes.

Before vs After

✗ Before

CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerName VARCHAR(50), ProductName VARCHAR(50));

✓ After

CREATE TABLE Orders (CustomerName VARCHAR(50), ProductName VARCHAR(50), PRIMARY KEY (CustomerName, ProductName));

What It Enables

It enables precise and reliable identification of records using multiple related fields, making data management clearer and safer.

Real Life Example

In a school, a student's grade in a subject is unique only when you know both the student and the subject. Using a composite primary key with student ID and subject ID ensures each grade record is unique and easy to find.

Key Takeaways

Composite primary keys use multiple columns to uniquely identify records.

This prevents confusion when one column alone is not unique.

It helps keep data accurate and easy to search.

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