The ON DELETE CASCADE
option in SQL is used when we define a FOREIGN KEY. It helps to ensure that when a row (usually PRIMARY KEY elements) in the Parent / Referenced Table is deleted, all the related rows (usually FOREIGN KEY elements) in the Child / Referencing Table is also deleted (often referred as “Leave No Orphans”). Link: https://dba.stackexchange.com/a/44962
In the given question, C (Foreign Key) is referencing A (Primary Key).
A |
C |
2 |
4 |
3 |
4 |
4 |
3 |
5 |
2 |
7 |
2 |
9 |
5 |
6 |
4 |
Step 1: Let’s delete the tuple $(2, 4)$ from the table (we will mark red for those rows which are being deleted).
A |
C |
${\color{Red} 2}$ |
${\color{Red} 4}$ |
3 |
4 |
4 |
3 |
5 |
2 |
7 |
2 |
9 |
5 |
6 |
4 |
Step 2: But if we deleted $2$, all the referenced child rows having $2$ in $C$ should also be deleted
A |
C |
${\color{Red} 2}$ |
${\color{Red} 4}$ |
3 |
4 |
4 |
3 |
${\color{Red} 5}$ |
${\color{Red} 2}$ |
${\color{Red} 7}$ |
${\color{Red} 2}$ |
9 |
5 |
6 |
4 |
Step 3: Oh but now, by the ripple effect of CASCADE, we also need to delete any occurrence of $5$ and $7$ from the $C$.
A |
C |
${\color{Red} 2}$ |
${\color{Red} 4}$ |
3 |
4 |
4 |
3 |
${\color{Red} 5}$ |
${\color{Red} 2}$ |
${\color{Red} 7}$ |
${\color{Red} 2}$ |
${\color{Red} 9}$ |
${\color{Red} 5}$ |
6 |
4 |
Step 4: Note that there is only one occurrence of $5$ in $C$ column, there is not any $7$ there.
That’s it. So in addition to the tuple $(2, 4)$, set of tuples that were deleted to preserve referential integrity are:
$(5, 2), (7, 2)$ and $(9, 5)$ (which is there in Option C and hence our answer)