← VCE Spreadsheets, SQL & Visualisation

Primary key

A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 39, VCE Data Analytics - U3O1 - Data Analytics — Module 19, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 20, IGCSE Computer Science 0478 — 9 Databases — Module 16, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 59, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 9, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 60, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 87, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 7, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 93, QCE Digital Solutions Unit 3 — Real-World Problems and Solution Requirements — Module 32, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 58, WA Computer Science ATAR Year 11 — Data Management — Module 26

Understanding Primary key in VCE Spreadsheets, SQL & Visualisation

What it does: AUTOINCREMENT creates a new unique number for each record, so nobody has to type IDs and risk repeating one. StudentID is declared as the primary key, so each student record can be identified exactly. A primary key must be unique for every record. If a value repeated, you could not tell which record you meant. Auto-numbering makes the database assign a new unique ID, such as 1, 2, 3, so you cannot create duplicates by accident. Many students are in the same year, so Year repeats. StudentID is different for every student. In SQL, the field name and data type come first, then the words PRIMARY KEY.

Common mistakes: The keyword is PRIMARY KEY, both words. Without KEY, the database does not mark StudentID as the unique identifier. It should be: StudentID INT PRIMARY KEY,. Auto-numbering counts up 1, 2, 3 and so on, so the field needs a number type. It should be INT, not TEXT.

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

This module's key terms are also in a printable crossword (Modules 35–44) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which field would make the best primary key for a Students table?
StudentIDFirstName
Matches
Name TEXT PRIMARY KEY
↓
Name is a poor primary key because two students can have the same name. Use a unique ID number such as StudentID instead.
Applying
Meanings
Term
PRIMARY KEY
Meaning
A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.
Practice
CREATE TABLE students (
Testing
Gaps
CREATE TABLE Pet (PetID INT );
Check Answer
Spots
1CREATE TABLE Student (
2 StudentID INT PRIMARY,

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

The 'Spider' badge — Web Weaver, 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. Which field would make the best primary key for a Students table?
A. StudentID ✓
B. FirstName
StudentID is unique for every student. Two students can share a first name, so it cannot identify one record exactly.
2. True or false: A primary key value can be repeated in two different records of the same table.
A. False ✓
B. True
A primary key must be unique for every record. If a value repeated, you could not tell which record you meant.
3. Why is a person's name a poor primary key?
A. Names are too short to store
B. Two people can have the same name ✓
Names can repeat, such as two students called Sam Nguyen. A key must be unique for each record.
4. What does auto-numbering do for a primary key field?
A. Gives each new record the next unique number ✓
B. Lets you type any number, even a repeated one
Auto-numbering makes the database assign a new unique ID, such as 1, 2, 3, so you cannot create duplicates by accident.
5. Which table design uses a primary key correctly?
A. Students(StudentID, Name, Year) with Year as the key
B. Students(StudentID, Name, Year) with StudentID as the key ✓
Many students are in the same year, so Year repeats. StudentID is different for every student.

Match the Pairs

StudentID INTEGER PRIMARY KEY → Makes the StudentID field the primary key, so every record has its own unique ID number
INSERT INTO Students VALUES (101, 'Mia'); → Adds a record whose ID 101 must not already be used by another record
Auto-numbering lets the database give each new record the next unique ID automatically → StudentID INTEGER PRIMARY KEY AUTOINCREMENT
A primary key is a field that is unique for every record in a table → CREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name TEXT);
Name TEXT PRIMARY KEY → Name is a poor primary key because two students can have the same name. Use a unique ID number such as StudentID instead.
Students: ID 101 Mia, ID 101 Jack → Two records share the ID 101, so the primary key is not unique. Each record needs its own ID, such as 102 for Jack.

Key Term

PRIMARY KEY
A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

Fill in the Blanks

1. CREATE TABLE Pet (PetID INT PRIMARY KEY);
Hint: Type the two-word constraint that makes this field unique for every record
2. CREATE TABLE Pet ( PetID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT);
Hint: Type the keyword that makes the database number each new record automatically
3. CREATE TABLE Student (StudentID INT, Name TEXT, PRIMARY KEY (StudentID));
Hint: Type the field name that is unique for each student, not the one that could repeat
4. SELECT * FROM Pet WHERE PetID = 7;
Hint: Type the primary key field that identifies exactly one record
5. CREATE TABLE Game ( GameID INT PRIMARY KEY, Title TEXT);
Hint: Type the data type for a whole-number ID field

Spot the Error

Question 1
A CREATE TABLE Student (
B StudentID INT PRIMARY,
C Name TEXT,
D Year INT
E );
Line B — The keyword is PRIMARY KEY, both words. Without KEY, the database does not mark StudentID as the unique identifier. It should be: StudentID INT PRIMARY KEY,
Question 2
A CREATE TABLE Pet (
B PetID TEXT AUTO_INCREMENT,
C Name TEXT,
D PRIMARY KEY (PetID)
E );
Line B — Auto-numbering counts up 1, 2, 3 and so on, so the field needs a number type. It should be INT, not TEXT.
Question 3
A -- Name is the primary key
B CREATE TABLE Player (
C PlayerID INT PRIMARY KEY,
D Name TEXT
E );
Line A — Names are poor keys because two players can share a name. The unique ID number, PlayerID, is the primary key, as line 3 shows.
Question 4
A INSERT INTO Student VALUES
B (101, 'Mia', 9),
C (NULL, 'Sam', 9);
Line C — A primary key can never be empty (NULL), because every record needs an ID to be identified. Sam needs a unique ID such as 102.
Question 5
A CREATE TABLE Student (
B StudentID INT,
C Name TEXT,
D PRIMARY KEY (Name)
E );
Line D — Names can repeat, so Name cannot identify a record exactly. The primary key should be the unique field: PRIMARY KEY (StudentID).

Student with a class code? Join your class →