Log In
29 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)$
in Databases
edited by
@bikram sir pls explain this
how we can apply on -delete cascade in 1 single table ?

 @ set2018 

on -delete cascade in 1 single table means we delete  FK tuples related to PK on that table . 

"A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted."~ this is for multiple tables , in case of single table  we delete FK tuples which related to deleted PK tuple .

A is PK  in this table, A is on-delete cascade.

C is foreign key . We have to maintain Referential Integrity .

tuple (2, 4) is deleted , here 2 is PK ,  and 2 is in FK  in tuples (5,2) and (7,2) so both are deleted .

Now 7 is no where FK . But 5 is FK again in tuple  (9,5) hence tuple (9,5) is also deleted .

overall 3 tuples are deleted  additionally to preserve referential integrity when the tuple (2, 4) is deleted .

simple and grt explanation @Bikram sir !
Thanks Sir for the explanation.

2 Answers

41 votes
Best answer

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
Very good Question and explanation is extremely well

I understand On delete cascade in single Table
10 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 delete (9,5) which force to delete all entries in which C contains 9 .

so ans should be C

Nice explanation.Easy to understand on delete cascade  for single table.

Related questions

23 votes
4 answers
Let $E_1$ and $E_2$ be two entities in an $E/R$ diagram with simple-valued attributes. $R_1$ and $R_2$ are two relationships between $E_1$ and $E_2$, where $R_1$ is one-to-many and $R_2$ is many-to-many. $R_1$ and $R_2$ do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model? $2$ $3$ $4$ $5$
asked Sep 22, 2014 in Databases Kathleen 3.7k views
30 votes
4 answers
Let r be a relation instance with schema R = (A, B, C, D). We define $r_1 = \pi_{A, B, C} (R)$ and $r_2=\pi_{A, D} (r)$. Let $s =r_1 \: * \: r_2$ where $*$ denotes natural join. Given that the decomposition of $r$ into $r_1$ and $r_2$ is lossy, which one of the following is TRUE? $s \subset r$ $r \cup s =r$ $r \subset s$ $r*s=s$
asked Sep 22, 2014 in Databases Kathleen 5.5k views
35 votes
5 answers
Which of the following is a key factor for preferring $B^+$-trees to binary search trees for indexing database relations? Database relations have a large number of records Database relations are sorted on the primary key $B^+$-trees require less memory than binary search trees Data transfer form disks is in blocks
asked Sep 22, 2014 in Databases Kathleen 5.9k views
39 votes
5 answers
Consider the following tables $T1$ and $T2.$ ... 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 _______
asked Feb 14, 2017 in Databases Madhav 7.5k views