← ACARA Digital Technologies Years 9–10

FOREIGN KEY ... REFERENCES in CREATE TABLE

Used in CREATE TABLE to link a column to another table's key. The database then only accepts values that match an existing key, so bad links are rejected.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 23, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 79, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 49, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 109, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 23, WA Computer Science ATAR Year 11 — Data Management — Module 52

Understanding FOREIGN KEY ... REFERENCES in CREATE TABLE in ACARA Digital Technologies Years 9–10

What it does: students is created before orders, so when orders names students(studentId) that table and key already exist. The column in this table goes in the first brackets. After REFERENCES comes the other table's name, then its key in brackets. A foreign key column should have the same data type as the key it references, so the values can be compared and matched. The constraint enforces the link. It is not just a comment, so bad links are rejected. The database never creates rows in the other table for you. With no matching key, the insert is rejected.

Common mistakes: The keyword is missing KEY. A foreign key is declared with FOREIGN KEY (student_id), followed by REFERENCES. The foreign key column must have the same data type as the key it references. students(id) is an INT, so student_id must be INT, not VARCHAR.

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 ... REFERENCES in CREATE TABLE 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 line correctly declares studentId as a foreign key in the orders table?

FOREIGN KEY students(studentId) REFERENCES (studentId)FOREIGN KEY (studentId) REFERENCES students(studentId)

Matches

CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students);

↓

REFERENCES must name the key field in brackets after the table, such as students(studentId). Without it the database doesn't know which key to link to.

Applying

Meanings

Term

FOREIGN KEY (field) REFERENCES other_table(field)

Meaning

Used in CREATE TABLE to link a column to another table's key. The database then only accepts values that match an existing key, so bad links are rejected.

Practice

CREATE TABLE clubs (

Testing

Gaps

teams(id)

Check Answer

Spots

3 student_id INT,

4 FOREIGN (student_id)

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 line correctly declares studentId as a foreign key in the orders table?

A. FOREIGN KEY (studentId) REFERENCES students(studentId) ✓

B. FOREIGN KEY students(studentId) REFERENCES (studentId)

The column in this table goes in the first brackets. After REFERENCES comes the other table's name, then its key in brackets.

2. You want orders.studentId to link to students.studentId. Which table should you create first?

A. orders, because it holds the foreign key

B. students, because it holds the key being referenced ✓

The referenced table must already exist. Create the primary key table first, then the table that points to it.

3. students.studentId is INTEGER. Which data type suits orders.studentId?

A. INTEGER, the same type as the key it references ✓

B. TEXT, so it can hold any value

A foreign key column should have the same data type as the key it references, so the values can be compared and matched.

4. True or false: FOREIGN KEY ... REFERENCES makes the database check that values match an existing key.

A. True: only values that exist in the referenced key are accepted ✓

B. False: it is just a label and checks nothing

The constraint enforces the link. It is not just a comment, so bad links are rejected.

5. The students table has studentId values 1 to 10. What happens when you run INSERT INTO orders with studentId 99?

A. The insert works and a new student 99 is created automatically

B. The insert is rejected because no student has ID 99 ✓

The database never creates rows in the other table for you. With no matching key, the insert is rejected.

Match the Pairs

FOREIGN KEY (studentId) REFERENCES students(studentId) → Links the studentId column in this table to the studentId key in the students table, so only matching values are accepted

INSERT INTO orders VALUES (11, 99, 2, 1, '2026-02-20'); → Is rejected if no student with studentId 99 exists, because the foreign key needs a matching value

The primary table must be created first, so the table with the foreign key has something to REFERENCE → CREATE TABLE students (studentId INT PRIMARY KEY); CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId));

A foreign key column must have the same data type as the key it references → studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId)

CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students); → REFERENCES must name the key field in brackets after the table, such as students(studentId). Without it the database doesn't know which key to link to.

CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId)); CREATE TABLE students (studentId INT PRIMARY KEY); → The orders table is created before students, so the table it REFERENCES doesn't exist yet. The primary table must be created first.

Key Term

FOREIGN KEY (field) REFERENCES other_table(field)

Used in CREATE TABLE to link a column to another table's key. The database then only accepts values that match an existing key, so bad links are rejected.

Fill in the Blanks

1. FOREIGN KEY (teamId) REFERENCES teams(id)

Hint: Type the keyword that points the foreign key at another table's key.

2. FOREIGN KEY (teamId) REFERENCES teams(id)

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

3. CREATE TABLE players ( teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id)

Hint: Type the data type, which must match the type of the key this column references.

4. CREATE TABLE teams (id INT PRIMARY KEY); CREATE TABLE players (teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id)

Hint: Type the constraint that makes the parent table's id a key, created before the table that links to it.

5. CREATE TABLE players (id INT, teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id) );

Hint: Type the key column in the parent table that the foreign key must match.

Spot the Error

Question 1

A CREATE TABLE orders (

B order_id INT PRIMARY KEY,

C student_id INT,

D FOREIGN (student_id)

E REFERENCES students(id)

F );

Line D — The keyword is missing KEY. A foreign key is declared with FOREIGN KEY (student_id), followed by REFERENCES.

Question 2

A CREATE TABLE results (

B student_id VARCHAR(20),

C mark INT,

D FOREIGN KEY (student_id)

E REFERENCES students(id)

F );

Line B — The foreign key column must have the same data type as the key it references. students(id) is an INT, so student_id must be INT, not VARCHAR.

Question 3

A CREATE TABLE pets (

B pet_id INT PRIMARY KEY,

C owner_id INT

D FOREIGN KEY (owner_id)

E REFERENCES owners(id)

F );

Line C — A comma is missing at the end of the owner_id column line. Each column or constraint in CREATE TABLE is separated by a comma, so it should be owner_id INT,

Question 4

A CREATE TABLE loans (

B loan_id INT PRIMARY KEY,

C book_id INT,

D FOREIGN KEY (loan_id)

E REFERENCES books(id)

F );

Line D — The foreign key must be the column that links to the other table. That is book_id, which matches books(id). loan_id is the loans table's own primary key.

Question 5

A CREATE TABLE teams

B id INT PRIMARY KEY,

C name VARCHAR(30)

D );

Line A — The opening bracket is missing. The primary table is created first so other tables can reference it, and its column list must start with (.

Student with a class code? Join your class →