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.