If you analyze clearly NO tuple will be deleted from T2 in any case untill we want to delete.[ since t2 has on delete set null]

Dark Mode

14,328 views

54 votes

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} & \text{8} \\\hline \text{7} & \text{3} \\\hline \text{5} & \text{8} \\\hline \text{6 } &\text{9} \\\hline \text{8} & \text{5} \\\hline \text{9} & \text{8} \\\hline \end{array}}

\qquad \overset{T2}{\begin{array}{|c|c|c|} \hline \textbf {R} & \textbf {S} \\\hline \text {2} & \text{2 }\\\hline \text{8} & \text{3} \\\hline \text{3} & \text{2} \\\hline \text{9} & \text{7} \\\hline \text{5 } &\text{7} \\\hline \text{7} & \text{2} \\\hline \end{array}}$$

In table $T1$ **P** is the primary key and **Q** is the foreign key referencing **R** in table $T2$ with on-delete cascade and on-update cascade. In table $T2,$ **R **is the primary key and **S** is the foreign key referencing **P ** in table $T1$ with on-delete set NULL and on-update cascade. In order to delete record $\langle 3, 8 \rangle$ from the table $T1,$ the number of additional records that need to be deleted from table $T1$ is _______

2

Only thing to be known is On Delete Cascade / On Delete Set Null apply to tables i.e here **On-Delete Cascade **is on **T1 **and **On Delete Set Null** is on **T2**.

that means tuple wont be deleted from **T2** ,instead the corresponding value of attribute which violate referential integrity will be set to **Null **.

Rest is simple if concept of ODC and ODSN is clear.

7

85 votes

Best answer

0

In case of on delete cascade,i believe yes.

on deletion of 3,8 no effect on Q-> R reference. though for S->P , now all entries in S which have entry 3 should be removed. this leads to removal of 8 from R which affects the Q->R reference. Hence all entries in R which have 8 would be deleted. which leads to removal of 5 and 9 from P. This will have impact on S via S->R reference. However there are no 5 and 9 entries in S. So over all 5,8 and 9,8 get deleted from T1 as a result of the 3,8 deletion.

on deletion of 3,8 no effect on Q-> R reference. though for S->P , now all entries in S which have entry 3 should be removed. this leads to removal of 8 from R which affects the Q->R reference. Hence all entries in R which have 8 would be deleted. which leads to removal of 5 and 9 from P. This will have impact on S via S->R reference. However there are no 5 and 9 entries in S. So over all 5,8 and 9,8 get deleted from T1 as a result of the 3,8 deletion.

0

47 votes

1

Can anyone explain why the answer would be 2 if S is referencing P with on delete cascade instead of on delete set null???? any records from T2 would be deleted???

Is it because when deleting (5,8) record 5 is being searched in T2's S but since there's no 5 available no record would be deleted in T2.

same for record (9,8).

Is it because when deleting (5,8) record 5 is being searched in T2's S but since there's no 5 available no record would be deleted in T2.

same for record (9,8).

0

15 votes

On-delete set NULL would set 3 in table T2 under S column as NULL. That's it. No record needs to be deleted off of T1.

Ans: **0**

However, if On-delete-cascade was the situation, < 8, 3> would be deleted from T2, which would in turn result in the deletion of < 5, 8 > and < 9,8 > from T1.

Answer would have been 2 in this case.

PS: 'On delete set null' would make the row < 8,3 > as < 8, NULL> and not <NULL, NULL>