edited by
19,962 views
39 votes
39 votes

The following table has two attributes $A$ and $C$ where $A$ is the primary key and $C$ is the foreign key referencing $A$ with on-delete cascade.
$$\begin{array}{|c|c|} \hline \textbf {A} &  \textbf {C} \\\hline \text {2} &  \text{4} \\\hline \text{3} & \text{4} \\\hline \text{4} & \text{3} \\\hline \text{5} &  \text{2} \\\hline \text {7} &  \text{2} \\\hline \text{9} & \text{5} \\\hline \text{6} & \text{4} \\\hline \end{array}$$

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple $(2, 4)$ is deleted is:

  1. $(3, 4)$ and $(6, 4)$
  2. $(5, 2)$ and $(7, 2)$
  3. $(5, 2), (7, 2)$ and $(9, 5)$
  4. $(3, 4), (4, 3)$ and $(6, 4)$
edited by

3 Answers

Best answer
49 votes
49 votes

(C)
Since deleting $(2,4)$, since $2$ is a primary key, you have to delete its foreign key occurence i.e $(5,2)$ and $(7,2)$
Since we are delting $5$, and $7$ we have delete it foreign key occurence i.e $(9,5)$.

There is no foreign key occurence for $9$.

edited by
14 votes
14 votes

here A is behaving like parent and C is  behaving like child bcoz A is PK and C is FK referencing to A.

when (2,4) is deleted then it force to delete all entries in which C contains 2 bcoz there is no 2 remains in parent so child not able to access 2. so delete (5,2) (7,2) which force to delete all entries in which C contains 5or7 bcoz there is no 5 or 7 remain in parent so child not able to access 5 or 7.so delete (9,5) which force to delete all entries in which C contains 9 .

so ans should be C

0 votes
0 votes

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)

Answer:

Related questions

53 votes
53 votes
5 answers
3
62 votes
62 votes
6 answers
4
Madhav asked Feb 14, 2017
18,247 views
Consider the following tables $T1$ and $T2.$$$\overset{T1}{\begin{array}{|c|c|c|} \hline \textbf {P} & \textbf {Q} \\\hline \text {2} & \text{2 }\\\hline \text{3} & \te...