Referential actions: restrict, cascade, set null; tracing cascading deletes

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. Which of the four basic constraints can a delete violate? from Lesson 2.3.1
2. Can a foreign key reference its own relation? from Lesson 2.2.2
3. How many superkeys does R(A, B, C, D, E) have with the single candidate key {A, B}? from Lesson 2.2.1

1 · The Hook

Bhavin leaves the college. His row in STUDENT is deleted. His three rows in ENROLL still say "roll 2 took …". The DBMS has three sensible choices: refuse the delete, delete his enrolments too, or keep the enrolments with roll set to NULL. The choice is written into the foreign key, and GATE loves the version where one delete cascades through a chain of tuples.

2 · Predict First

EMP(eid, mgr) where mgr → EMP.eid with ON DELETE CASCADE. Rows: (1, NULL), (2, 1), (3, 2), (4, 2), (5, 1). Employee 1 is deleted. Which rows are left?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

None. Deleting 1 cascades to 2 and 5 (they reference 1); deleting 2 cascades to 3 and 4. All five rows are deleted. A cascade keeps going until no remaining row references a deleted one.

Referential actions

When a referenced (parent) tuple is deleted or its primary key is updated, the foreign key declaration says what happens to the referencing (child) tuples:

SQL writes these as ON DELETE CASCADE, ON UPDATE SET NULL, and so on (Lesson 3.3.1). RESTRICT and NO ACTION differ only in when the check is made; for GATE treat both as "reject".

SET NULL is impossible if the FK is part of the child's primary key (entity integrity) or declared NOT NULL.

Tracing a cascading delete

1. Put the tuple to be deleted in a "to delete" list.
2. Repeatedly: for each tuple in the list, find all tuples whose foreign key references it and whose action is CASCADE; add them to the list.
3. Stop when nothing new is added. All tuples in the list are deleted.

With a self-referencing foreign key, the cascade follows the chain down the "tree" of references. With a RESTRICT foreign key anywhere on the path, the whole delete is rejected if that restricted reference exists.

Cascades across relations

STUDENT ← ENROLL (ON DELETE CASCADE) and STUDENT ← HOSTEL_ALLOT (ON DELETE SET NULL): deleting a student deletes their enrolments and blanks their hostel allotments' roll column.

4 · Visual

EMP(eid, mgr) with ON DELETE CASCADE. Deleting 1 removes everything that can reach 1 by following arrows: all five rows.

5 · Worked Examples

Example 1 Easy

ENROLL.roll → STUDENT.roll is ON DELETE RESTRICT. Bhavin (roll 2) has 1 enrolment. What happens on "delete student 2"?

Solution

The delete is rejected: a child row still references roll 2.

Example 2 Medium

R(A, C), C → R.A ON DELETE CASCADE. Rows: (1, NULL), (2, 1), (3, 1), (4, 3), (5, 4), (6, 2). Delete (3, 1). Which tuples are deleted?

Solution

3 → 4 (refers to 3) → 5 (refers to 4). Rows 1, 2, 6 do not reach 3. Deleted: (3,1), (4,3), (5,4), three tuples.

Example 3 GATE

Same relation, but the action is ON DELETE SET NULL. Delete (3, 1). What is the state afterwards?

Solution

Only (3, 1) is deleted. Rows referring to 3 get C = NULL: (4, 3) becomes (4, NULL). (5, 4) still refers to 4, which exists. Result: (1, NULL), (2, 1), (4, NULL), (5, 4), (6, 2).

8 · Connect the Dots

Lesson 2.3.1 listed which operations break referential integrity; this lesson gave the four ways to respond and how to trace cascades. Next: Chapter 2.4 starts the mapping from ER diagrams to tables, where every relationship becomes either a foreign key or a table.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 2 mark(s) — Cascade

R(A, C) with C → R.A ON DELETE CASCADE holds (1, NULL), (2, 1), (3, 2), (4, 2), (5, 3), (6, 1). How many tuples are deleted in total when (2, 1) is deleted?

Numerical answer: ________

Q2 · MCQ · 1 mark(s) — Actions

ENROLL.roll → STUDENT.roll is ON DELETE SET NULL, but roll is part of ENROLL's primary key. Deleting a referenced student:

1. (A) works and sets roll to NULL
2. (B) cannot use SET NULL, since a PK attribute cannot be NULL
3. (C) deletes all students
4. (D) deletes the enrolments

Q3 · MCQ · 1 mark(s) — Actions

Which action changes child FK values to the parent's new key when the parent's key is updated?

1. (A) ON DELETE CASCADE
2. (B) ON UPDATE RESTRICT
3. (C) ON UPDATE CASCADE
4. (D) ON UPDATE SET NULL

Q4 · MSQ · 2 mark(s) — Set null

R(A, C), C → R.A ON DELETE SET NULL, holds (1, NULL), (2, 1), (3, 2). Delete (1, NULL). Which tuples remain afterwards?

1. (A) (2, NULL)
2. (B) (2, 1)
3. (C) (3, 2)
4. (D) (3, NULL)

Q5 · NAT · 2 mark(s) — Cascade

EMP(eid, mgr) with mgr → eid ON DELETE CASCADE: (1, NULL), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3), (7, 6), (8, NULL). Delete employee 3. How many rows remain?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

referential actionRESTRICT / NO ACTIONCASCADESET NULLSET DEFAULTcascading delete traceON DELETE / ON UPDATE