FOREIGN KEY ... REFERENCES in CREATE TABLE

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

FOREIGN KEY ... REFERENCES in CREATE TABLE

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

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)
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
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
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
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
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1FOREIGN KEY (studentId) REFERENCES students(studentId) AThe orders table is created before students, so the table it REFERENCES doesn't exist yet. The primary table must be created first.
2INSERT INTO orders VALUES (11, 99, 2, 1, '2026-02-20'); BLinks the studentId column in this table to the studentId key in the students table, so only matching values are accepted
3The primary table must be created first, so the table with the foreign key has something to REFERENCE CCREATE TABLE students (studentId INT PRIMARY KEY); CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId));
4A foreign key column must have the same data type as the key it references DIs rejected if no student with studentId 99 exists, because the foreign key needs a matching value
5CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students); EREFERENCES 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.
6CREATE TABLE orders (orderId INT PRIMARY KEY, studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId)); CREATE TABLE students (studentId INT PRIMARY KEY); FstudentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId)
C

Definitions

FOREIGN KEY (field) REFERENCES other_table(field)
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
FOREIGN KEY (teamId) __________ teams(id)
2.
FOREIGN KEY (teamId) REFERENCES _____(id)
3.
CREATE TABLE players ( teamId ___, FOREIGN KEY (teamId) REFERENCES teams(id)
4.
CREATE TABLE teams (id INT ___________); CREATE TABLE players (teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id)
5.
CREATE TABLE players (id INT, teamId INT, FOREIGN KEY (teamId) REFERENCES teams(__) );
E

Spot the Error

Shade in the letter of the line that contains an 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 );
Question 2
A CREATE TABLE results (
B student_id VARCHAR(20),
C mark INT,
D FOREIGN KEY (student_id)
E REFERENCES students(id)
F );
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 );
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 );
Question 5
A CREATE TABLE teams
B id INT PRIMARY KEY,
C name VARCHAR(30)
D );

Answer Key

FOREIGN KEY ... REFERENCES in CREATE TABLE
Section A: Quick Questions
Q1
A — FOREIGN KEY (studentId) REFERENCES students(studentId)
Q2
B — students, because it holds the key being referenced
Q3
A — INTEGER, the same type as the key it references
Q4
A — True: only values that exist in the referenced key are accepted
Q5
B — The insert is rejected because no student has ID 99
Section B: Match the Pairs
Q1
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
Q2
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
Q3
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));
Q4
A foreign key column must have the same data type as the key it references → studentId INT, FOREIGN KEY (studentId) REFERENCES students(studentId)
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.
Section D: Fill in the Blanks
Q1
FOREIGN KEY (teamId) REFERENCES teams(id)
Q2
FOREIGN KEY (teamId) REFERENCES teams(id)
Q3
CREATE TABLE players ( teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id)
Q4
CREATE TABLE teams (id INT PRIMARY KEY); CREATE TABLE players (teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id)
Q5
CREATE TABLE players (id INT, teamId INT, FOREIGN KEY (teamId) REFERENCES teams(id) );
Section E: Spot the Error
Q1
D — The keyword is missing KEY. A foreign key is declared with FOREIGN KEY (student_id), followed by REFERENCES.
Q2
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.
Q3
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,
Q4
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.
Q5
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 (.