← VCE Spreadsheets, SQL & Visualisation

Foreign key and table relationships

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 40, VCE Data Analytics - U3O1 - Data Analytics — Module 20, VCE Data Analytics - U3O2 - Analysis and Design — Module 42, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 21, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 61, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 11, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 63, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 88, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 8, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 102, QCE Digital Solutions Unit 3 — Real-World Problems and Solution Requirements — Module 33, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 22, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 59, WA Computer Science ATAR Year 11 — Data Management — Module 29

Understanding Foreign key and table relationships in VCE Spreadsheets, SQL & Visualisation

What it does: The foreign key class_id sits on the many side (Students) and points to the one side (Classes). A foreign key stores the primary key value from another table. This is what links the two tables together. A foreign key must match an existing primary key. ClassID 9 does not exist in Classes, so it is not valid. The teacher's name is stored once in Classes. Students link to it by ClassID, so one change is enough. Repeating class details for each student wastes space and causes errors when one copy is changed. A foreign key avoids this.

Common mistakes: The keywords are in the wrong order. The statement must start with CREATE TABLE and then the table name: CREATE TABLE Classes (. ClassID has lost its PRIMARY KEY. A foreign key in Students can only link to a primary key in Classes. It should be: ClassID INT PRIMARY KEY.

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 Foreign key and table relationships 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 statement describes a foreign key?

A field that holds the primary key value of a record in another tableA field that holds a value copied from any column in the same table

Matches

Students(student_id, name, class_name, teacher, room)

↓

Class details are typed into every student record instead of using a foreign key, so data is repeated and can become inconsistent. Store them in Classes and add class_id to Students.

Applying

Meanings

Term

FOREIGN KEY

Meaning

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

Practice

CREATE TABLE Students (

Testing

Gaps

(ClassID) REFERENCES Classes(ID)

Check Answer

Spots

1CREATE Classes TABLE (

2 ClassID INT PRIMARY KEY,

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

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 statement describes a foreign key?

A. A field that holds a value copied from any column in the same table

B. A field that holds the primary key value of a record in another table ✓

A foreign key stores the primary key value from another table. This is what links the two tables together.

2. A Students table has a field ClassID that matches the ClassID primary key in the Classes table. What is ClassID in Students?

A. A foreign key ✓

B. A primary key

ClassID is the primary key in Classes, but in Students it only points to a class, so it is a foreign key.

3. One class has many students, and each student belongs to one class. What type of relationship is this?

A. One-to-one

B. One-to-many ✓

One record in Classes links to many records in Students. That is a one-to-many relationship.

4. In a one-to-many relationship, where does the foreign key go?

A. In the table on the 'many' side (Students) ✓

B. In the table on the 'one' side (Classes)

Each student row stores the ClassID of its one class. The foreign key goes on the 'many' side.

5. Why do we store ClassID in the Students table instead of typing the full class name and teacher for every student?

A. It avoids repeating data, so each class is stored only once ✓

B. It makes the database store more copies of each class, to be safe

Linking with a foreign key means class details are stored once in Classes. This avoids repeated data and errors.

Match the Pairs

FOREIGN KEY (class_id) REFERENCES Classes(class_id) → Makes class_id in this table a link to the primary key class_id in the Classes table

class_id INTEGER → A field in the Students table that stores the class_id of the class the student belongs to

One-to-many relationship: one class can have many students, so the student table holds the class link → Students(student_id, name, class_id)

Avoiding repeated data: store the class details once in Classes and link to them with the class ID → Classes(class_id, class_name, teacher)

Students(student_id, name, class_name, teacher, room) → Class details are typed into every student record instead of using a foreign key, so data is repeated and can become inconsistent. Store them in Classes and add class_id to Students.

Students: class_id = 99, Classes: 1, 2, 3 → The foreign key value 99 does not match any primary key in Classes, so it links to a class that does not exist.

Key Term

FOREIGN KEY

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

Fill in the Blanks

1. FOREIGN KEY (ClassID) REFERENCES Classes(ID)

Hint: Type the two-word keyword that makes a field link to the primary key of another table.

2. ClassID INT, FOREIGN KEY (ClassID) REFERENCES Classes(ID)

Hint: Type the keyword that says which table and field the link points to.

3. ClassID INT, FOREIGN KEY (ClassID) REFERENCES Classes(ID)

Hint: Type the name of the table that holds the primary key being linked to.

4. CREATE TABLE Classes (ID INT PRIMARY KEY);

Hint: Type the two-word keyword that makes each class unique. The foreign key in Students will point to it.

5. ClassID INT, FOREIGN KEY (ClassID) REFERENCES Classes(ID)

Hint: Type the Students field that stores the link to a class.

Spot the Error

Question 1

A CREATE Classes TABLE (

B ClassID INT PRIMARY KEY,

C ClassName TEXT

D );

Line A — The keywords are in the wrong order. The statement must start with CREATE TABLE and then the table name: CREATE TABLE Classes (

Question 2

A CREATE TABLE Classes (

B ClassName TEXT,

C ClassID INT

D );

Line C — ClassID has lost its PRIMARY KEY. A foreign key in Students can only link to a primary key in Classes. It should be: ClassID INT PRIMARY KEY

Question 3

A SELECT Name, ClassName

B FROM Students JOIN Classes

C ON Students.StudentID =

D Classes.ClassID;

Line C — The tables are linked by matching the foreign key to the primary key it points to. Students.StudentID is not that foreign key. It should be ON Students.ClassID =

Question 4

A INSERT INTO Classes

B VALUES (1, 'Maths');

C INSERT INTO Students

D VALUES (101, 'Mia', 7);

Line D — The foreign key value 7 does not match any ClassID in the Classes table, so the link is broken. Mia should use ClassID 1, which is the Maths class.

Question 5

A SELECT StudentName

B FROM Classes

C WHERE ClassID = 1;

Line B — StudentName is stored in the Students table, not in Classes. The query should read FROM Students. Then ClassID, the foreign key, picks the class.

Student with a class code? Join your class →