Referential integrity

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

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

Left column		Right column
1INSERT INTO orders VALUES (11, 99, 2, 1, '2026-03-01');		Aorders.studentId = students.studentId
2DELETE FROM students WHERE studentId = 1;		BThe foreign key value 99 has no matching primary key in students, so the database blocks the insert. Add the student first, then the order.
3Every foreign key value must match an existing primary key value in the related table.		CThe orders would become orphan records pointing to a missing customer, so the database blocks the delete. Remove or reassign the orders first.
4An orphan record is a row whose foreign key points to a primary key that does not exist.		DAdds an order for studentId 99, who does not exist. Referential integrity makes the database reject it.
5Adding an order with studentId 99 when no student 99 exists, and expecting it to be saved		ETries to delete a student who still has orders. The database blocks it so no order is left pointing at a missing student.
6Deleting a customer who still has orders and expecting the orders to stay valid		Forders row: studentId = 99, but students has no studentId 99

Definitions

referential integrity

Fill in the Blanks

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

FOREIGN KEY (studentId) __________ students(studentId);

FOREIGN KEY (productId) REFERENCES ________(productId);

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

DELETE FROM ________ WHERE studentId = 1;

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

Spot the Error

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

Question 1

A -- Customers 1 and 2 exist

B INSERT INTO orders

C VALUES (10, 7);

Question 2

A ALTER TABLE customers

B ADD FOREIGN KEY (custId)

C REFERENCES customers(custId);

Question 3

A DELETE FROM orders

B WHERE custId = 2;

C DELETE FROM customers

D WHERE orderId = 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

Question 5

A INSERT INTO products

B VALUES (6, 'Kai');

C INSERT INTO orders

D VALUES (13, 6);

Answer Key

Referential integrity

Section A: Quick Questions

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

B — An orphan record

A — Block the INSERT and show an error

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

B — The foreign key

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

Section C: Definitions

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.

Section D: Fill in the Blanks

FOREIGN KEY (studentId)
REFERENCES students(studentId);

FOREIGN KEY (productId)
REFERENCES products(productId);

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

DELETE FROM students WHERE studentId = 1;

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

Section E: Spot the Error

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.

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

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

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

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.