Composite primary keys

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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)

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

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

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)

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1PRIMARY KEY (studentId, subjectId)		ACreates a junction table whose key is made of two fields that link students to subjects
2CREATE TABLE enrolments (studentId INT, subjectId INT, PRIMARY KEY (studentId, subjectId));		BA composite primary key: the two fields together identify each enrolment record uniquely
3Each field alone is not unique: a student appears in many enrolments and a subject has many students, but each pair appears only once		CA 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).
4A junction table links two tables in a many-to-many relationship by using both foreign keys together as its primary key		DstudentId alone is set as the key, so each student could only be enrolled once. The key needs both fields: PRIMARY KEY (studentId, subjectId).
5PRIMARY KEY (studentId), PRIMARY KEY (subjectId)		EPRIMARY KEY (studentId, subjectId), FOREIGN KEY (studentId) REFERENCES students(studentId)
6CREATE TABLE enrolments (studentId INT PRIMARY KEY, subjectId INT);		F(1, 1), (1, 2), (2, 1)

Definitions

PRIMARY KEY (studentId, subjectId)

Fill in the Blanks

Complete each line of code by filling in the missing parts.

___________ (studentId, subjectId)

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

CREATE TABLE enrolment ( studentId INT, subjectId INT, ___________ (studentId, subjectId));

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

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

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE enrolment (

B studentId INT PRIMARY KEY,

C subjectId INT,

D PRIMARY KEY (studentId,

E subjectId));

Question 2

A SELECT studentId subjectId

B FROM enrolment

C WHERE studentId = 1

D AND subjectId = 2;

Question 3

A INSERT INTO enrolment

B VALUES (1, 2),

C (1, 3),

D (1, 2);

Question 4

A ALTER TABLE enrolment

B ADD PRIMARY KEY

C (studentId, studentId);

Question 5

A INSERT INTO enrolment

B (studentId)

C VALUES (3, 4);

Answer Key

Composite primary keys

Section A: Quick Questions

B — PRIMARY KEY (studentId, subjectId)

A — Two or more fields together identify each record uniquely

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

A — (1, 2) and (1, 2)

B — Yes, the pairs are different even though subjectId repeats

Section B: 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).

Section C: Definitions

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.

Section D: Fill in the Blanks

PRIMARY KEY (studentId, subjectId)

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

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

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

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

Section E: Spot the Error

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,'.

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.

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);'.

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

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)'.