Questions: Referential Integrity and Cascading Delete/Update Actions

Question 1

A university database has a `students` table and an `enrollments` table with a foreign key referencing students. After a student withdraws, an administrator deletes the student's record. The university wants to keep enrollment records for institutional history, but with the student reference cleared. Which ON DELETE action achieves this?

• A. CASCADE — deletes the student and automatically removes all their enrollment records
• B. SET NULL — keeps enrollment records but sets the student_id foreign key to NULL
• C. RESTRICT — prevents the delete until all enrollment records are removed manually
• D. SET DEFAULT — replaces the student_id with a placeholder value defined at table creation

Answer: (B)

SET NULL keeps the child rows (enrollment records) while clearing the foreign key that points to the now-deleted student. This is appropriate for 'association' relationships where the child has independent value. CASCADE would destroy the enrollment records entirely — the wrong outcome here. RESTRICT would block the delete, forcing manual cleanup. SET DEFAULT works only if a meaningful default was defined.

Question 2

A developer applies ON DELETE CASCADE to every foreign key in a schema 'to avoid constraint violation errors.' They then delete a single top-level department record. What is the most dangerous likely consequence?

• A. The delete will fail because cascading across multiple tables is not permitted
• B. Child rows (employees, projects, budgets) will be automatically deleted, potentially removing far more data than the developer intended
• C. The foreign key constraints will be silently ignored for the operation
• D. Child rows will be preserved and their foreign keys set to NULL automatically

Answer: (B)

CASCADE propagates deletes automatically through every table that references the deleted row, and through tables that reference those tables, and so on. A single top-level delete can silently cascade through the entire dependency chain, removing large amounts of data with no confirmation prompt. The database is doing exactly what was configured — the danger is that the developer did not think through all downstream effects. This is why 'default to RESTRICT and use CASCADE only when you can articulate why automatic deletion is the correct business behavior' is the safer practice.

Question 3

Referential integrity enforcement applies primarily when you delete rows from a parent table — not during INSERT or UPDATE operations on the child table.

• T. True
• F. False

Answer: False

Referential integrity is enforced in both directions. When you INSERT a row into a child table with a foreign key value that does not exist in the parent table, the database rejects the INSERT. Similarly, an UPDATE that changes a child row's foreign key to a non-existent parent value is rejected. ON DELETE and ON UPDATE cascading actions address modifications to the *parent* table, but the database also continuously checks that child rows never reference non-existent parents.

Question 4

Using ON DELETE CASCADE is most appropriate when child rows have no meaningful existence independent of their parent — for example, order line items that belong to a specific order.

• T. True
• F. False

Answer: True

This is the 'composition' relationship pattern: the child is part of the parent and has no reason to exist on its own. If you delete an order, its line items should go with it — keeping orphaned line items would be meaningless. By contrast, for 'association' relationships (like students enrolled in courses), both sides have independent existence and CASCADE would be destructive. The relationship semantics determine the right cascading action.

Question 5

Explain the difference between a 'composition' relationship and an 'association' relationship in database design, and give one example of each showing why they call for different ON DELETE actions.

Model answer: A composition relationship is one where the child has no meaningful existence without the parent — the child is a part of the parent. Example: order line items belong to an order; delete the order, and CASCADE-deleting the line items is correct because orphaned line items are meaningless. An association relationship is one where both sides have independent existence. Example: students and courses — a student enrolls in a course, but both exist independently. Deleting a course should not delete the students, and deleting a student should probably use SET NULL or RESTRICT on enrollment records, not CASCADE.

The composition vs. association distinction comes from object-oriented modeling (UML) and directly maps to the right cascading behavior. The key test: if the parent disappears, does the child still make sense on its own? If yes, use RESTRICT or SET NULL. If no, CASCADE may be appropriate — but only after explicitly reasoning through all the consequences.