Changing data across related tables

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. You want to add a new order for a student who is not yet in the students table. Which step must come first?

A INSERT the student row, then INSERT the order row

B INSERT the order row, then INSERT the student row

2. You want to delete a student who has rows in the orders table. Which order is safe?

A DELETE the student first, then DELETE the student's orders

B DELETE the student's orders first, then DELETE the student

3. Which statement is true about the parent table and the child table?

A The table with the primary key is the parent and the table with the foreign key is the child

B The table with the foreign key is the parent and the table with the primary key is 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?

A A foreign key error, because productId 99 does not exist in products

B The order is saved and the product is created automatically

5. Which pair of statements will add a new subject and a result for it without an error?

A INSERT INTO results ... then INSERT INTO subjects ...

B INSERT INTO subjects ... then INSERT INTO results ...

Match the Pairs

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

Left column		Right column
1INSERT INTO students VALUES (11, 'Tom', 'Ng', 7, 'Red', 0); INSERT INTO orders VALUES (11, 11, 2, 1, '2026-03-01');		AINSERT INTO subjects VALUES (6, 'Art', 'Ms Cho'); INSERT INTO results VALUES (1, 6, 90);
2DELETE FROM orders WHERE studentId = 3; DELETE FROM students WHERE studentId = 3;		BDELETE FROM results WHERE subjectId = 5; DELETE FROM subjects WHERE subjectId = 5;
3Insert the parent record before the child record, so the foreign key value already exists in the parent table.		CThe 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.
4Delete child records before the parent record, so no child row is left pointing at a missing parent.		DThe 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.
5INSERT INTO orders VALUES (11, 11, 2, 1, '2026-03-01'); INSERT INTO students VALUES (11, 'Tom', 'Ng', 7, 'Red', 0);		ERemoves the child rows (orders for student 3) first, then deletes the parent student row without breaking the foreign key.
6DELETE FROM students WHERE studentId = 3; DELETE FROM orders WHERE studentId = 3;		FAdds the parent row (student 11) first, then a child row in orders that points to it, so the foreign key rule is kept.

Definitions

INSERT parent, DELETE child

Fill in the Blanks

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

INSERT INTO ________ VALUES (11, 'Zed', 'Kim', 7, 'Red', 0); INSERT INTO results VALUES (11, 1, 80);

DELETE FROM _______ WHERE studentId = 3; DELETE FROM students WHERE studentId = 3;

DELETE FROM orders WHERE _________ = 2; DELETE FROM students WHERE studentId = 2;

DELETE FROM orders WHERE productId = 4; DELETE FROM ________ WHERE productId = 4;

INSERT INTO students VALUES (12, 'Kai', 'Ito', 8, 'Blue', 0); INSERT INTO results VALUES (__, 1, 90);

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A DELETE FROM students

B WHERE studentId = 4;

C DELETE FROM students

D WHERE studentId = 4;

Question 2

A INSERT INTO results

B (studentId, subjectId, mark)

C VALUES (1, 9, 75);

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;

Question 4

A DELETE FROM results

B WHERE productId = 3;

C DELETE FROM products

D WHERE productId = 3;

Question 5

A INSERT INTO subjects

B VALUES (7, 'Art', 'Ms Ray');

C INSERT INTO results

D VALUES (1, 6, 75);

Answer Key

Changing data across related tables

Section A: Quick Questions

A — INSERT the student row, then INSERT the order row

B — DELETE the student's orders first, then DELETE the student

A — The table with the primary key is the parent and the table with the foreign key is the child

A — A foreign key error, because productId 99 does not exist in products

B — INSERT INTO subjects ... then INSERT INTO results ...

Section B: 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.

Section C: Definitions

INSERT parent, DELETE child — 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.

Section D: Fill in the Blanks

INSERT INTO students
VALUES (11, 'Zed', 'Kim', 7, 'Red', 0);
INSERT INTO results VALUES (11, 1, 80);

DELETE FROM results WHERE studentId = 3;
DELETE FROM students WHERE studentId = 3;

DELETE FROM orders WHERE studentId = 2;
DELETE FROM students WHERE studentId = 2;

DELETE FROM orders WHERE productId = 4;
DELETE FROM products WHERE productId = 4;

INSERT INTO students
VALUES (12, 'Kai', 'Ito', 8, 'Blue', 0);
INSERT INTO results VALUES (12, 1, 90);

Section E: Spot the Error

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.

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.

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.

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.

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, ...).