← QCE Digital Solutions Unit 2

Secondary key

A non-primary field used to search or sort records, such as surname. Unlike a primary key, its values need not be unique, and it is often indexed to make searches faster.

Part of: QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 89

Understanding Secondary key in QCE Digital Solutions Unit 2

What it does: Sorting by lastName uses a secondary key. Two students can share a surname, so the values need not be unique. A secondary key is a non-primary field used to search or sort. studentId is already the primary key, so surname is the secondary key here. An index works like a sorted lookup list so the database can find matching records without checking every row. It does not make values unique. CREATE INDEX name ON table(field) builds an index on the chosen field. CREATE KEY is not valid SQL for this. The broken statement confuses secondary keys with primary keys. Uniqueness is the rule for a primary key only.

Common mistakes: The index is meant to speed up searches by surname, so it must be built on the secondary key lastName. studentId is the primary key and already has an index. It should be ON students(lastName);. lastName is not unique because many students can share a surname, so it is a secondary key, not a primary key. The comment should say secondary key: lastName.

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 Secondary key to a class →

This module's key terms are also in a printable crossword (Modules 186–228) and word search (Modules 144–228).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A student table has studentId as its primary key. Which field is a good secondary key for finding students quickly by name?
studentIdsurname
Matches
A secondary key must be unique, so no two records can share the same lastName.
↓
Secondary key values do not have to be unique. Two students can have the same surname. Only a primary key must be unique.
Applying
Meanings
Term
secondary key
Meaning
A non-primary field used to search or sort records, such as surname. Unlike a primary key, its values need not be unique, and it is often indexed to make searches faster.
Practice
SELECT firstName, lastName, house
Testing
Gaps
CREATE idxSurname
Check Answer
Spots
1CREATE INDEX idx_surname
2ON students(studentId);

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

The 'Woodland Hedgehog' badge — Import Master, one of Cloodit's 106 collectible badges
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 student table has studentId as its primary key. Which field is a good secondary key for finding students quickly by name?
A. surname ✓
B. studentId
A secondary key is a non-primary field used to search or sort. studentId is already the primary key, so surname is the secondary key here.
2. Which statement about secondary key values is correct?
A. Every value must be unique, just like a primary key
B. Values can repeat, such as two students with the surname Nguyen ✓
Unlike a primary key, a secondary key does not need to be unique. Many records can share the same surname.
3. A search for surname = 'Smith' returns three records. What does this show?
A. A secondary key can match more than one record ✓
B. The surname must be the primary key
Surnames are not unique, so searching on a secondary key can return many records. A primary key search returns at most one.
4. Which search is using a primary key?
A. Finding the student with studentId = 7 ✓
B. Finding all students with surname = 'Lee'
studentId uniquely identifies one record, so it is the primary key. Surname is a secondary key.
5. What is an index on a secondary key used for?
A. Making every value in the field unique
B. Making searches on that field faster ✓
An index works like a sorted lookup list so the database can find matching records without checking every row. It does not make values unique.

Match the Pairs

SELECT * FROM students WHERE lastName = 'Nguyen'; → Searches the students table using lastName, a secondary key, and may return more than one record
CREATE INDEX idx_lastName ON students(lastName); → Builds an index on a secondary key so searches by surname run faster
A secondary key is a non-primary field used to sort records, and its values do not have to be unique → SELECT * FROM students ORDER BY lastName;
A primary key finds exactly one record, while a secondary key can match many records → WHERE studentId = 1 (primary) versus WHERE house = 'Red' (secondary)
A secondary key must be unique, so no two records can share the same lastName. → Secondary key values do not have to be unique. Two students can have the same surname. Only a primary key must be unique.
PRIMARY KEY (lastName) → Using a surname as the primary key is wrong because surnames repeat. lastName is better as a secondary key, with a unique field like studentId as the primary key.

Key Term

secondary key
A non-primary field used to search or sort records, such as surname. Unlike a primary key, its values need not be unique, and it is often indexed to make searches faster.

Fill in the Blanks

1. CREATE INDEX idxSurname ON students(lastName);
Hint: Type the keyword that builds a lookup structure on a secondary key to speed up searches.
2. SELECT firstName, lastName FROM students WHERE lastName = 'Nguyen';
Hint: Type the column used as the secondary key to search by surname.
3. SELECT * FROM students ORDER BY lastName;
Hint: Type the non-primary column that sorts students by surname.
4. CREATE INDEX idxHouse ON students(house);
Hint: Type the column that many students share, such as their school colour group. It is a secondary key with repeated values.
5. SELECT * FROM students WHERE studentId = 3;
Hint: Type the primary key column, whose values are unique for every student.

Spot the Error

Question 1
A CREATE INDEX idx_surname
B ON students(studentId);
C SELECT * FROM students
D WHERE lastName = 'Nguyen';
Line B — The index is meant to speed up searches by surname, so it must be built on the secondary key lastName. studentId is the primary key and already has an index. It should be ON students(lastName);
Question 2
A -- primary key: lastName
B SELECT * FROM students
C WHERE lastName = 'Smith';
Line A — lastName is not unique because many students can share a surname, so it is a secondary key, not a primary key. The comment should say secondary key: lastName.
Question 3
A SELECT * FROM students
B WHERE lastName = Nguyen
C ORDER BY firstName;
Line B — A text value searched on a secondary key such as a surname must be in quotes. Without them SQL treats Nguyen as a column name. It should be WHERE lastName = 'Nguyen'
Question 4
A found <- false
B for i from 1 to n
C if rec[i].surname <- key then
D found <- true
E end if
Line C — The search must compare the surname with the key using =. The <- arrow assigns a value instead of testing it. It should be if rec[i].surname = key then
Question 5
A CREATE TABLE students (
B studentId INT PRIMARY KEY,
C lastName VARCHAR(30) UNIQUE,
D firstName VARCHAR(30)
E );
Line C — A secondary key need not be unique, because two students can share a surname. UNIQUE would reject the second Nguyen. Only the primary key studentId must be unique, so remove UNIQUE.

Student with a class code? Join your class →