← WA Computer Science ATAR Unit 2

Composite primary keys

A composite primary key: two or more fields together identify each record uniquely. Each field can repeat alone, but the pair cannot. Used in junction tables.

Part of: WA Computer Science ATAR Year 11 — Data Management — Module 28

Understanding Composite primary keys in WA Computer Science ATAR Unit 2

What it does: studentId 1 repeats and subjectId 1 repeats, but no (studentId, subjectId) pair repeats, so the pair can be the key. The junction table's key is made of the two foreign keys, and each one still references its own parent table. A table has only one primary key. A composite key is a single key made up of several fields combined. The combination must be unique. (1, 2) twice is a duplicate pair. (1, 2) and (1, 3) differ in subjectId, so they are allowed. They link to students and subjects, so they are foreign keys. Together they also make up the composite primary key.

Common mistakes: studentId alone is not unique, because a student enrols in many subjects. Declaring it as a PRIMARY KEY by itself is wrong. Remove it here and keep the pair in the single composite PRIMARY KEY line: ' studentId INT,'. The two fields are missing a comma between them. The correct line is 'SELECT studentId, subjectId'. Together these two fields identify one enrolment row.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Composite primary keys to a class →

This module's key terms are also in a printable crossword (Modules 28–122) and word search (Modules 28–266).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

A table stores enrolments. Which primary key design is correct for one row per student per subject?

PRIMARY KEY (studentId)PRIMARY KEY (studentId, subjectId)

Matches

PRIMARY KEY (studentId), PRIMARY KEY (subjectId)

↓

A table can only have one primary key. Two separate keys do not make a composite key. Both fields must go in one bracket: PRIMARY KEY (studentId, subjectId).

Applying

Meanings

Term

PRIMARY KEY (studentId, subjectId)

Meaning

A composite primary key: two or more fields together identify each record uniquely. Each field can repeat alone, but the pair cannot. Used in junction tables.

Practice

SELECT studentId, subjectId, mark

Testing

Gaps

(studentId, subjectId)

Check Answer

Spots

1CREATE TABLE enrolment (

2 studentId INT PRIMARY KEY,

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A table stores enrolments. Which primary key design is correct for one row per student per subject?

A. PRIMARY KEY (studentId)

B. PRIMARY KEY (studentId, subjectId) ✓

One student takes many subjects, so studentId repeats. The pair studentId + subjectId is unique for each enrolment, so both fields form the composite key.

2. Which statement describes a composite primary key?

A. Two or more fields together identify each record uniquely ✓

B. Two separate primary keys, one in each field

A table has only one primary key. A composite key is a single key made up of several fields combined.

3. In an enrolment table, studentId 1 appears in rows for Maths and English. What does this show?

A. studentId alone is not unique, so it cannot be the primary key ✓

B. studentId is a good primary key because it identifies students

A primary key value must never repeat. studentId repeats whenever a student takes more than one subject, so it needs subjectId alongside it.

4. Which pair of rows would break the composite key PRIMARY KEY (studentId, subjectId)?

A. (1, 2) and (1, 2) ✓

B. (1, 2) and (1, 3)

The combination must be unique. (1, 2) twice is a duplicate pair. (1, 2) and (1, 3) differ in subjectId, so they are allowed.

5. Is this allowed in a table with PRIMARY KEY (studentId, subjectId)? Rows (1, 1) and (2, 1)

A. No, subjectId 1 appears twice so it is a duplicate

B. Yes, the pairs are different even though subjectId repeats ✓

Each field can repeat on its own. Only the full combination of both fields must be unique.

Match the Pairs

PRIMARY KEY (studentId, subjectId) → A composite primary key: the two fields together identify each enrolment record uniquely

CREATE TABLE enrolments (studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId)); → Creates a junction table whose key is made of two fields that link students to subjects

Each field alone is not unique: a student appears in many enrolments and a subject has many students, but each pair appears only once → (1, 1), (1, 2), (2, 1)

A junction table links two tables in a many-to-many relationship by using both foreign keys together as its primary key → PRIMARY KEY (studentId, subjectId), FOREIGN KEY (studentId) REFERENCES students(studentId)

PRIMARY KEY (studentId), PRIMARY KEY (subjectId) → A table can only have one primary key. Two separate keys do not make a composite key. Both fields must go in one bracket: PRIMARY KEY (studentId, subjectId).

CREATE TABLE enrolments (studentId INT PRIMARY KEY, subjectId INT); → studentId alone is set as the key, so each student could only be enrolled once. The key needs both fields: PRIMARY KEY (studentId, subjectId).

Key Term

PRIMARY KEY (studentId, subjectId)

A composite primary key: two or more fields together identify each record uniquely. Each field can repeat alone, but the pair cannot. Used in junction tables.

Fill in the Blanks

1. PRIMARY KEY (studentId, subjectId)

Hint: Type the two-word keyword that marks the field or fields identifying each record uniquely.

2. CREATE TABLE enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));

Hint: Type the second field that joins studentId to make the composite key for each enrolment.

3. CREATE TABLE enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));

Hint: Type the keyword that makes the pair of fields in brackets identify each row uniquely.

4. CREATE TABLE enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));

Hint: Type the field that is combined with subjectId to identify which student is enrolled.

5. CREATE TABLE enrolment ( studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));

Hint: Type the name of the second field this junction table must declare, which is used in its composite key.

Spot the Error

Question 1

A CREATE TABLE enrolment (

B studentId INT PRIMARY KEY,

C subjectId INT,

D PRIMARY KEY (studentId,

E subjectId));

Line B — studentId alone is not unique, because a student enrols in many subjects. Declaring it as a PRIMARY KEY by itself is wrong. Remove it here and keep the pair in the single composite PRIMARY KEY line: ' studentId INT,'.

Question 2

A SELECT studentId subjectId

B FROM enrolment

C WHERE studentId = 1

D AND subjectId = 2;

Line A — The two fields are missing a comma between them. The correct line is 'SELECT studentId, subjectId'. Together these two fields identify one enrolment row.

Question 3

A INSERT INTO enrolment

B VALUES (1, 2),

C (1, 3),

D (1, 2);

Line D — The pair (1, 2) is already in the table, so the composite primary key rejects the duplicate. Each field may repeat alone, but the pair cannot. The line should be ' (2, 2);'.

Question 4

A ALTER TABLE enrolment

B ADD PRIMARY KEY

C (studentId, studentId);

Line C — A composite key needs two different fields. Listing studentId twice does not make records unique. The second field should be subjectId: ' (studentId, subjectId);'.

Question 5

A INSERT INTO enrolment

B (studentId)

C VALUES (3, 4);

Line B — Both parts of the composite primary key must be given for every new row. Leaving out subjectId also leaves the VALUES list with too many values. It should be ' (studentId, subjectId)'.

Student with a class code? Join your class →