← IGCSE Computer Science Paper 2

Choosing a suitable primary key from a table

The field chosen to identify each record. Its values must be unique and never blank, like an ID number. Names and dates of birth can repeat, so they are unsuitable.

Part of: IGCSE Computer Science 0478 — 9 Databases — Module 17, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 20, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 10, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 61, WA Computer Science ATAR Year 11 — Data Management — Module 27

Understanding Choosing a suitable primary key from a table in IGCSE Computer Science Paper 2

What it does: Names and dates of birth can repeat between records, so neither can identify one record reliably. A new ID field can be given a different value for each record, so it makes a suitable primary key when no existing field is unique. StudentID is different for every student. Two students can easily share the same name. A primary key must be unique. Many people share a date of birth, so the values can repeat. Date/time is a valid data type, so that is not the problem. A product code is unique to each product. Prices can be the same for many products.

Common mistakes: Name is the wrong primary key because two students can share the same name. The key should be the unique ID field, so the line should be PRIMARY KEY (StudentID). DOB has been made a primary key, but pupils can share a date of birth. Only PupilID, which is different for every pupil, should be the primary key. The line should be DOB DATE,.

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 Choosing a suitable primary key from a table to a class →

This module's key terms are also in a printable crossword (Modules 500–522) and word search (Modules 500–538).

Take this worksheet further — free with Cloodit

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

Learning
Duo
A Students table has StudentID, Name and DateOfBirth. Which field is the most suitable primary key?
StudentIDName
Matches
PRIMARY KEY (house)
↓
The house field has repeated values such as Red and Blue, so it cannot identify one record. Use a unique field such as studentId.
Applying
Meanings
Term
PRIMARY KEY
Meaning
The field chosen to identify each record. Its values must be unique and never blank, like an ID number. Names and dates of birth can repeat, so they are unsuitable.
Practice
SELECT studentId, firstName, lastName
Testing
Gaps
Primary key:
Check Answer
Spots
3 Name VARCHAR(30),
4 PRIMARY KEY (Name)

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

The 'Lion' badge — Inheritance King, 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 Students table has StudentID, Name and DateOfBirth. Which field is the most suitable primary key?
A. StudentID ✓
B. Name
StudentID is different for every student. Two students can easily share the same name.
2. Why is DateOfBirth a poor choice of primary key?
A. Dates cannot be stored in a database
B. Two people can be born on the same date ✓
A primary key must be unique. Many people share a date of birth, so the values can repeat. Date/time is a valid data type, so that is not the problem.
3. A Cars table has a Colour field where 'Red' appears in 4 records. Can Colour be the primary key?
A. Yes, because every car has a colour
B. No, because its values are repeated ✓
A field with repeated values fails as a primary key. Every record must have a different value.
4. Which statement about a primary key is correct?
A. It is never left blank in any record ✓
B. It may be left blank in some records
Every record must be identified, so a primary key value must always be present.
5. A Books table has Title, Author and Genre, and some titles and authors repeat. Nothing is unique. What is the best solution?
A. Add a new field such as BookID ✓
B. Use Author as the key
When no field is suitable, add an ID field and give each record its own value. Author repeats whenever an author writes more than one book.

Match the Pairs

PRIMARY KEY (studentId) → Chooses studentId as the field that identifies each record, because its values are unique and never blank
studentId INTEGER NOT NULL → An ID field that must always hold a value, so no record can have a blank key
Two students can share a first name or date of birth, so these fields are unsuitable as a primary key → firstName, dateOfBirth
When no field is unique, add a new ID field to the table and use it as the primary key → ALTER TABLE Pets ADD petId INTEGER
PRIMARY KEY (house) → The house field has repeated values such as Red and Blue, so it cannot identify one record. Use a unique field such as studentId.
PRIMARY KEY (lastName) → Last names can be shared by different students, so they are not unique. Use an ID number instead.

Key Term

PRIMARY KEY
The field chosen to identify each record. Its values must be unique and never blank, like an ID number. Names and dates of birth can repeat, so they are unsuitable.

Fill in the Blanks

1. Student(StudentID, Name, DOB, Form) Primary key: StudentID
Hint: Type the field that gives every student a different ID number
2. CREATE TABLE Student ( StudentID INTEGER PRIMARY KEY, Name TEXT);
Hint: Type the two-word SQL keyword that marks the field identifying each record
3. Can DOB be the primary key? No Two pupils can share a date of birth.
Hint: Type Yes or No: can a field whose values can repeat identify each record?
4. Pet(Name, Breed, Age) No unique field, so add a new field: PetID
Hint: Type the name of the new ID field to add for the Pet table
5. Key values must be unique and never blank
Hint: Type the word meaning no two records can have the same value

Spot the Error

Question 1
A CREATE TABLE Student (
B StudentID INTEGER,
C Name VARCHAR(30),
D PRIMARY KEY (Name)
E );
Line D — Name is the wrong primary key because two students can share the same name. The key should be the unique ID field, so the line should be PRIMARY KEY (StudentID).
Question 2
A CREATE TABLE Pupil (
B Name VARCHAR(30),
C DOB DATE PRIMARY KEY,
D PupilID INTEGER PRIMARY KEY
E );
Line C — DOB has been made a primary key, but pupils can share a date of birth. Only PupilID, which is different for every pupil, should be the primary key. The line should be DOB DATE,
Question 3
A PRIMARY KEY: Genre
B Table: Book
C Fields: ISBN, Title, Genre
D Reason: each book has its own ISBN
Line A — Genre is a poor key because many books share the same genre, so its values repeat. The key should be ISBN, which is different for every book: PRIMARY KEY: ISBN.
Question 4
A Table: Pupil
B Fields: Name, DOB, House
C Problem: no field is unique
D Fix: use House as the key
Line D — House values repeat because many pupils are in the same house. When no field is unique, the fix is to add a new ID field such as PupilID and use that as the key.
Question 5
A Key field: StaffID (blanks ok)
B Table: Staff
C Fields: StaffID, Name, Dept
D Each StaffID is different
Line A — A primary key must never be blank, because every record needs a value that identifies it. The line should say that StaffID has no blanks.

Student with a class code? Join your class →