← ACARA Digital Technologies Years 9–10

Referential integrity

Every foreign key value must match an existing primary key in the related table, so no record points to something missing. The database blocks changes that break this.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 17, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 66, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 96, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 11, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 108, WA Computer Science ATAR Year 11 — Data Management — Module 32

Understanding Referential integrity in ACARA Digital Technologies Years 9–10

What it does: This shows the rule: each studentId in orders (foreign key) must be found in students (primary key). The order points to a student who is missing, which is an orphan. Referential integrity exists to prevent this. An orphan record has a foreign key value that matches no primary key in the related table. The database blocks the invalid change, because the foreign key would not match any primary key. Handle the child records (the orders) first so no orders are left pointing at a missing customer.

Common mistakes: Customer 7 does not exist, so this order would be an orphan record and the database blocks it. The custId must match an existing customer, such as 1. The foreign key belongs in the orders table, the many side that points to customers. It should be ALTER TABLE orders.

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 Referential integrity to a class →

This module's key terms are also in a printable crossword (Modules 433–451) and word search (Modules 413–451).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which statement describes referential integrity?

Every table must have at least ten records stored in itEvery foreign key value must match an existing primary key value in the related table

Matches

Adding an order with studentId 99 when no student 99 exists, and expecting it to be saved

↓

The foreign key value 99 has no matching primary key in students, so the database blocks the insert. Add the student first, then the order.

Applying

Meanings

Term

referential integrity

Meaning

Every foreign key value must match an existing primary key in the related table, so no record points to something missing. The database blocks changes that break this.

Practice

INSERT INTO orders

Testing

Gaps

students(studentId);

Check Answer

Spots

2INSERT INTO orders

3VALUES (10, 7);

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 referential integrity?

A. Every foreign key value must match an existing primary key value in the related table ✓

B. Every table must have at least ten records stored in it

Referential integrity keeps links between tables valid, so no record points to something that is missing.

2. An orders table has a row with customerId 99, but the customers table has no customer 99. What is this row called?

A. A primary record

B. An orphan record ✓

An orphan record has a foreign key value that matches no primary key in the related table.

3. A student tries to add an order for a customer who does not exist. What should a database enforcing referential integrity do?

A. Block the INSERT and show an error ✓

B. Save the order and fix the customer later

The database blocks the invalid change, because the foreign key would not match any primary key.

4. Customer 5 still has three orders. You try to run DELETE FROM customers WHERE customerId = 5; What happens?

A. The database blocks the delete, because the orders would become orphans ✓

B. The customer is deleted and the orders stay with no customer

Deleting a customer who still has orders would leave orphan records, so the database refuses the change.

5. Which column must match a value in another table's primary key to keep referential integrity?

A. Any column with text in it

B. The foreign key ✓

The foreign key is the field that links to the primary key of the related table.

Match the Pairs

INSERT INTO orders VALUES (11, 99, 2, 1, '2026-03-01'); → Adds an order for studentId 99, who does not exist. Referential integrity makes the database reject it.

DELETE FROM students WHERE studentId = 1; → Tries to delete a student who still has orders. The database blocks it so no order is left pointing at a missing student.

Every foreign key value must match an existing primary key value in the related table. → orders.studentId = students.studentId

An orphan record is a row whose foreign key points to a primary key that does not exist. → orders row: studentId = 99, but students has no studentId 99

Adding an order with studentId 99 when no student 99 exists, and expecting it to be saved → The foreign key value 99 has no matching primary key in students, so the database blocks the insert. Add the student first, then the order.

Deleting a customer who still has orders and expecting the orders to stay valid → The orders would become orphan records pointing to a missing customer, so the database blocks the delete. Remove or reassign the orders first.

Key Term

referential integrity

Every foreign key value must match an existing primary key in the related table, so no record points to something missing. The database blocks changes that break this.

Fill in the Blanks

1. FOREIGN KEY (studentId) REFERENCES students(studentId);

Hint: Type the keyword that links the foreign key to the parent table it must match.

2. FOREIGN KEY (productId) REFERENCES products(productId);

Hint: Type the name of the parent table whose primary key this foreign key must match.

3. INSERT INTO orders VALUES (11, 99, 2, 1, '2026-03-01');

Hint: Type the child table that would get an orphan record if student 99 does not exist. The database blocks this.

4. DELETE FROM students WHERE studentId = 1;

Hint: Type the parent table. Deleting a row that still has orders pointing to it is blocked.

5. SELECT * FROM orders WHERE studentId NOT IN (SELECT studentId FROM students);

Hint: Type the primary key column of the parent table. Orders with no match are orphan records.

Spot the Error

Question 1

A -- Customers 1 and 2 exist

B INSERT INTO orders

C VALUES (10, 7);

Line C — Customer 7 does not exist, so this order would be an orphan record and the database blocks it. The custId must match an existing customer, such as 1.

Question 2

A ALTER TABLE customers

B ADD FOREIGN KEY (custId)

C REFERENCES customers(custId);

Line A — The foreign key belongs in the orders table, the many side that points to customers. It should be ALTER TABLE orders.

Question 3

A DELETE FROM orders

B WHERE custId = 2;

C DELETE FROM customers

D WHERE orderId = 2;

Line D — The customers table has no orderId column. After deleting customer 2's orders, delete that same customer with WHERE custId = 2.

Question 4

A CREATE TABLE orders (

B orderId INT PRIMARY KEY,

C custId INT,

D FOREIGN KEY (orderId)

E REFERENCES customers(custId)

F covered

Line D — The foreign key must be the column that links to customers, which is custId. orderId is the orders table's own primary key.

Question 5

A INSERT INTO products

B VALUES (6, 'Kai');

C INSERT INTO orders

D VALUES (13, 6);

Line A — Customer 6 must be added to the customers table first. Otherwise the order for customer 6 points to a missing customer. It should be INSERT INTO customers.

Student with a class code? Join your class →