Changing data across related tables
Add the parent row before its child rows, and delete child rows before their parent. Otherwise the foreign key rule is broken and SQL gives a foreign key error.
Understanding Changing data across related tables in QCE Digital Solutions Unit 2
What it does: The subject (parent) is added first, then the result (child) uses subjectId 6 as its foreign key. Foreign key errors on insert usually mean the value in the foreign key column has no matching primary key in the parent table. The student must exist in students before an order can use studentId 11 as its foreign key.
Common mistakes: Line 1 deletes the parent row first while child rows in orders still point to it, so SQL gives a foreign key error. Line 1 should be DELETE FROM orders so the child rows go first. The child row uses subjectId 9, but no parent row with that ID exists in subjects, so the foreign key rule is broken. It should use an existing subject ID such as 3.
Class: _____________________ · Name: _____________________ · Date: _________
Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.
This module's key terms are also in a printable crossword (Modules 337–409) and word search (Modules 234–409).
Take this worksheet further — free with Cloodit
Each concept is reinforced from every angle — learned, then applied, then tested.
Learning
Duo
You want to add a new order for a student who is not yet in the students table. Which step must come first?
INSERT the order row, then INSERT the student rowINSERT the student row, then INSERT the order row
Matches
INSERT INTO orders VALUES (11, 11, 2, 1, '2026-03-01');
INSERT INTO students VALUES (11, 'Tom', 'Ng', 7, 'Red', 0);
↓
The child row is inserted before its parent. Order 11 refers to studentId 11, which does not exist yet, so SQL gives a foreign key error. Insert the student first.
Applying
Meanings
Term
INSERT parent, DELETE child
Meaning
Add the parent row before its child rows, and delete child rows before their parent. Otherwise the foreign key rule is broken and SQL gives a foreign key error.
Practice
INSERT INTO students VALUES (11, 'Kai', 'Brown', 7, 'Green', 50);
Testing
Gaps
INSERT INTO
Check Answer
Spots
1DELETE FROM students
2WHERE studentId = 4;
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.
Students lock in with Sprints
Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.
Custom printable certificates
Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.
Quick Questions
1. You want to add a new order for a student who is not yet in the students table. Which step must come first?
The student is the parent row. orders.studentId is a foreign key, so the parent must exist before any child row can point to it.
2. You want to delete a student who has rows in the orders table. Which order is safe?
Delete child rows before their parent. If the student is deleted first, the orders would point to a student that no longer exists.
3. Which statement is true about the parent table and the child table?
In students and orders, students holds the primary key (the parent) and orders holds the foreign key studentId (the child).
4. A new product is added with INSERT INTO orders (orderId, studentId, productId, quantity, orderDate) VALUES (11, 1, 99, 2, '2026-02-20'). There is no product 99. What happens?
SQL does not create parent rows for you. The foreign key rule is broken when a child row points to a parent that does not exist.
5. Which pair of statements will add a new subject and a result for it without an error?
results.subjectId refers to subjects.subjectId, so subjects is the parent. Insert the subject row first, then the result row.
Match the Pairs
INSERT INTO students VALUES (11, 'Tom', 'Ng', 7, 'Red', 0);
INSERT INTO orders VALUES (11, 11, 2, 1, '2026-03-01');
→
Adds the parent row (student 11) first, then a child row in orders that points to it, so the foreign key rule is kept.
DELETE FROM orders WHERE studentId = 3;
DELETE FROM students WHERE studentId = 3;
→
Removes the child rows (orders for student 3) first, then deletes the parent student row without breaking the foreign key.
Insert the parent record before the child record, so the foreign key value already exists in the parent table.
→
INSERT INTO subjects VALUES (6, 'Art', 'Ms Cho');
INSERT INTO results VALUES (1, 6, 90);
Delete child records before the parent record, so no child row is left pointing at a missing parent.
→
DELETE FROM results WHERE subjectId = 5;
DELETE FROM subjects WHERE subjectId = 5;
INSERT INTO orders VALUES (11, 11, 2, 1, '2026-03-01');
INSERT INTO students VALUES (11, 'Tom', 'Ng', 7, 'Red', 0);
→
The child row is inserted before its parent. Order 11 refers to studentId 11, which does not exist yet, so SQL gives a foreign key error. Insert the student first.
DELETE FROM students WHERE studentId = 3;
DELETE FROM orders WHERE studentId = 3;
→
The parent row is deleted before its child rows. The orders still point to student 3, so SQL gives a foreign key error. Delete the orders first.
Key Term
INSERT parent, DELETE childAdd the parent row before its child rows, and delete child rows before their parent. Otherwise the foreign key rule is broken and SQL gives a foreign key error.
Fill in the Blanks
1. INSERT INTO students
VALUES (11, 'Zed', 'Kim', 7, 'Red', 0);
INSERT INTO results VALUES (11, 1, 80);
Hint: Type the parent table that must receive the new row before its child row in results.
2. DELETE FROM results WHERE studentId = 3;
DELETE FROM students WHERE studentId = 3;
Hint: Type the child table whose rows must be deleted first, before the parent student row.
3. DELETE FROM orders WHERE studentId = 2;
DELETE FROM students WHERE studentId = 2;
Hint: Type the foreign key column that links each child order row back to its parent student.
4. DELETE FROM orders WHERE productId = 4;
DELETE FROM products WHERE productId = 4;
Hint: Type the parent table, which is deleted second, after the orders that refer to it are gone.
5. INSERT INTO students
VALUES (12, 'Kai', 'Ito', 8, 'Blue', 0);
INSERT INTO results VALUES (12, 1, 90);
Hint: Type the studentId value that matches the parent row just added, so the foreign key rule is not broken.
Spot the Error
Question 1
A
DELETE FROM students
B
WHERE studentId = 4;
C
DELETE FROM students
D
WHERE studentId = 4;
Line A — Line 1 deletes the parent row first while child rows in orders still point to it, so SQL gives a foreign key error. Line 1 should be DELETE FROM orders so the child rows go first.
Question 2
A
INSERT INTO results
B
(studentId, subjectId, mark)
C
VALUES (1, 9, 75);
Line C — The child row uses subjectId 9, but no parent row with that ID exists in subjects, so the foreign key rule is broken. It should use an existing subject ID such as 3.
Question 3
A
DELETE FROM results
B
WHERE studentId = 7;
C
DELETE FROM products
D
WHERE studentId = 7;
E
DELETE FROM students
F
WHERE studentId = 7;
Line C — Line 3 must delete the child rows in orders. Deleting from products leaves orders for student 7 in place, so deleting the student on line 5 causes a foreign key error. It should be DELETE FROM orders.
Question 4
A
DELETE FROM results
B
WHERE productId = 3;
C
DELETE FROM products
D
WHERE productId = 3;
Line A — The child table that points at products is orders, not results. Its rows must be deleted first, or removing the product on line 3 breaks the foreign key rule. Line 1 should be DELETE FROM orders.
Question 5
A
INSERT INTO subjects
B
VALUES (7, 'Art', 'Ms Ray');
C
INSERT INTO results
D
VALUES (1, 6, 75);
Line B — The parent subject is added with ID 7, but the child row in results refers to subjectId 6. No subject 6 exists, so there is a foreign key error. The parent should be VALUES (6, ...).
Student with a class code? Join your class →