← VCE Spreadsheets, SQL & Visualisation

Naming columns, primary keys and foreign keys

A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 42, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 19, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 20, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 17, WA Computer Science ATAR Year 11 — Data Management — Module 38

Understanding Naming columns, primary keys and foreign keys in VCE Spreadsheets, SQL & Visualisation

What it does: The primary key column is named student_id in its own table, using the _id suffix. The foreign key column has exactly the same name as the primary key it points to, so the link between the tables is easy to see. Key names should be descriptive and end in _id, so student_id tells you exactly what the column holds. Pick one style, such as lowercase with underscores, and use it for every column in the database. A short descriptive name with an _id suffix, like book_id, is clear and consistent. Column names must follow one style throughout. Mixing capitals, spaces and hyphens makes the names inconsistent and harder to use in queries.

Common mistakes: Column names must follow one style. The others use lowercase snake_case, so YearLevel should be year_level. A foreign key reuses the exact name of the primary key it points to. It should be student_id, not stu_id.

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 Naming columns, primary keys and foreign keys 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 is the better name for the primary key column in a table of students?
xstudent_id
Matches
Enrolment table: StudentRef (foreign key to Student.student_id)
↓
The foreign key name does not match the primary key name. It should be student_id.
Applying
Meanings
Term
student_id
Meaning
A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.
Practice
CREATE TABLE student (
Testing
Gaps
INTEGER PRIMARY KEY,
Check Answer
Spots
3 first_name TEXT,
4 YearLevel INT

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

The 'Coelacanth' badge — Living Fossil, 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 is the better name for the primary key column in a table of students?
A. student_id ✓
B. x
Key names should be descriptive and end in _id, so student_id tells you exactly what the column holds.
2. A Results table links to the Students table. Which foreign key name should Results use?
A. student_id ✓
B. stu_ref
A foreign key reuses the exact name of the primary key it points to, so it stays student_id.
3. Which set of column names follows one consistent style?
A. first_name, LastName, DOB
B. first_name, last_name, date_of_birth ✓
Pick one style, such as lowercase with underscores, and use it for every column in the database.
4. True or false: a foreign key can have a different name from the primary key it links to, as long as the data matches.
A. True: any name is fine
B. False: it should reuse the same name ✓
Using the same name, such as teacher_id in both tables, makes the relationship easy to spot and avoids confusion.
5. Which column name is more descriptive for storing a student's year level?
A. year_level ✓
B. col3
Descriptive names tell the reader what the data is. Names like col3 say nothing about the content.

Match the Pairs

student_id → A key column name: lowercase, descriptive, and ending in the _id suffix
first_name → A descriptive column name that says what data it holds, in the same lowercase underscore style
A primary key in the Student table is named with the _id suffix → CREATE TABLE Student (student_id INTEGER PRIMARY KEY);
A foreign key in the Enrolment table reuses the primary key name from the Student table → student_id INTEGER REFERENCES Student(student_id)
Enrolment table: StudentRef (foreign key to Student.student_id) → The foreign key name does not match the primary key name. It should be student_id.
Student table columns: student_id, FirstName, last name, Year-Level → The column names use mixed styles. They should all be lowercase with underscores: first_name, last_name, year_level.

Key Term

student_id
A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

Fill in the Blanks

1. CREATE TABLE student ( student_id INTEGER PRIMARY KEY, first_name TEXT);
Hint: Type the primary key column name: the table name in lowercase, an underscore, then the id suffix.
2. CREATE TABLE result ( mark INTEGER, student_id INTEGER);
Hint: Type the foreign key column name, which reuses the exact name of the primary key in the student table.
3. CREATE TABLE class ( class_id INTEGER PRIMARY KEY, class_name TEXT);
Hint: Type the suffix that marks a key column, including the underscore.
4. CREATE TABLE student ( last_name TEXT, first_name TEXT);
Hint: Type a descriptive column name for a surname, in the same lowercase underscore style as the column below.
5. ALTER TABLE mark ADD FOREIGN KEY (subject_id) REFERENCES subject(subject_id);
Hint: Type the foreign key column name, matching the primary key name shown on the last line.

Spot the Error

Question 1
A CREATE TABLE student (
B student_id INT PRIMARY KEY,
C first_name TEXT,
D YearLevel INT
E );
Line D — Column names must follow one style. The others use lowercase snake_case, so YearLevel should be year_level.
Question 2
A CREATE TABLE result (
B result_id INT PRIMARY KEY,
C stu_id INT,
D mark INT
E );
Line C — A foreign key reuses the exact name of the primary key it points to. It should be student_id, not stu_id.
Question 3
A CREATE TABLE teacher (
B first_name TEXT,
C id INT PRIMARY KEY,
D last_name TEXT
E );
Line C — A primary key is named after its table with an _id suffix. A bare id is not descriptive, so it should be teacher_id.
Question 4
A CREATE TABLE book (
B book_key INT PRIMARY KEY,
C title TEXT,
D author TEXT
E );
Line B — Key columns use the _id suffix. book_key should be book_id.
Question 5
A CREATE TABLE club (
B c1 TEXT,
C club_id INT PRIMARY KEY,
D suburb TEXT
E );
Line B — Column names should be descriptive so the reader knows what they store. c1 should be club_name.

Student with a class code? Join your class →