Composite primary keys in SQL - Step-by-Step Execution

Concept Flow - Composite primary keys

Define Table

↓

Specify Columns

↓

Set Composite Primary Key

↓

Insert Rows

↓

Check Uniqueness of Combined Columns

↓

Reject Duplicate Composite Key Rows

↓

Table Ready with Composite PK

Create a table with multiple columns combined to form a unique identifier, ensuring no duplicate pairs exist.

Execution Sample

SQL

CREATE TABLE Orders (
  OrderID INT,
  ProductID INT,
  Quantity INT,
  PRIMARY KEY (OrderID, ProductID)
);

INSERT INTO Orders VALUES (1, 101, 5);

Creates Orders table with composite primary key on OrderID and ProductID, then inserts a row.

Execution Table

Step	Action	Evaluation	Result
1	Create table Orders with columns OrderID, ProductID, Quantity	Table schema defined	Table created with columns
2	Set PRIMARY KEY on (OrderID, ProductID)	Composite key constraint applied	Composite primary key set
3	Insert row (1, 101, 5)	Check if (1,101) exists in PK	No duplicate, row inserted
4	Insert row (1, 102, 3)	Check if (1,102) exists in PK	No duplicate, row inserted
5	Insert row (1, 101, 2)	Check if (1,101) exists in PK	Duplicate found, insertion rejected
6	Insert row (2, 101, 4)	Check if (2,101) exists in PK	No duplicate, row inserted
7	End of inserts	No more rows	Table ready with unique composite keys

💡 Insertion stops when all rows are processed or duplicates rejected due to composite key constraint

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	After Step 6	Final
Orders Table Rows	empty	[(1,101,5)]	[(1,101,5),(1,102,3)]	[(1,101,5),(1,102,3)] (no change)	[(1,101,5),(1,102,3),(2,101,4)]	[(1,101,5),(1,102,3),(2,101,4)]

Key Moments - 2 Insights

Why does the insertion of (1, 101, 2) fail even though Quantity is different?

Because the composite primary key is on OrderID and ProductID, the pair (1,101) already exists (see step 5 in execution_table), so the database rejects the duplicate key.

Can a single column in a composite primary key have duplicate values?

Yes, individual columns can have duplicates as long as the combined values of all columns in the composite key are unique (see steps 3 and 4 where OrderID 1 repeats but ProductID differs).

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what rows exist in the Orders table?

A [(1,101,5)]

B [(1,101,5), (1,102,3)]

C [(1,101,5), (1,101,2)]

D []

💡 Hint

Check the 'Result' column for step 4 in execution_table

At which step does the database reject an insertion due to duplicate composite key?

A Step 5

B Step 3

C Step 6

D Step 7

💡 Hint

Look for 'Duplicate found, insertion rejected' in execution_table

If the primary key was only on OrderID, what would happen when inserting (1, 102, 3)?

A Insertion would succeed

B Insertion would fail due to duplicate ProductID

C Insertion would fail due to duplicate OrderID

D Insertion would be ignored

💡 Hint

Consider uniqueness constraint on OrderID alone, not composite keys

Concept Snapshot

Composite Primary Keys:
- Use multiple columns combined as a unique identifier
- Syntax: PRIMARY KEY (col1, col2, ...)
- Ensures no duplicate pairs of values
- Individual columns can have duplicates
- Useful for many-to-many relationships

Full Transcript

Composite primary keys combine two or more columns to uniquely identify each row in a table. When creating a table, you specify the columns and then set the primary key to include multiple columns. During insertion, the database checks if the combination of these columns already exists. If yes, it rejects the insertion to maintain uniqueness. Individual columns in the composite key can have duplicate values, but the combined values must be unique. This is useful when no single column can uniquely identify a row, such as in many-to-many relationships.

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