2,526 views

The following table has two attributes $X$ and $Y$ where $X$ is the primary key and $Y$ is the foreign key referencing $X$ with on-delete cascade.

$\begin{array}{|c|c|} \hline X & Y \\ \hline 2 & 4 \\ \hline 3 & 4 \\ \hline 4 & 3 \\ \hline 5 & 2 \\ \hline 7 & 2 \\ \hline 9 & 5 \\ \hline 6 & 4 \\ \hline\end{array}$

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

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

@Shaik Masthan i think more tuples will be deleted

I think all are deleted.
all rows from that instance are deleted
even i think all should be deleted

Since $X$ is a primary key, on deleting $(3, 4)$, all the foreign key occurrences of $3$ will also be deleted: $(4, 3)$.
We've deleted $4$, which was also primary key in $(4, 3)$, its foreign key occurrences will also be deleted: $(2, 4), (6, 4)$.
$2$ and $6$ are also deleted, so their foreign key occurrences will also be deleted: $(5, 2), (7, 2)$.
Likewise, $5$ and $7$'s key foreign key occurrences will also be deleted: $(9, 5)$.

And that deletes whole of the tuples of the table.

### 1 comment

edited
Logically whole table will be deleted. But from the given options (A) is correct. (4,3), (2,4), (6,4) are deleted first. Might be question is trying to say that which cascade are deleted first. Option (C) is incorrect because there is no (3,2) cascade is present in the table. Option (D) is also incorrect because (3,4) is already deleted, we are asked for the additional deleted cascade.

A FOREIGN KEY with on-delete cascade states that if A RECORD IS DELETED FROM PARENT TABLE THEN THAT CORRESPONDING RECORD MUST ALSO BE DELETED FROM THE CHILD TABLE.

But here we have single table so we delete foreign keys corresponding to DELETED primary keys

here X IS PARENT AS PK (PRIMARY KEY)and Y IS CHILD AS FK(FOREIGN KEY)

(3,4) deleted 3 is PK and 3 is FK in tuple (4,3) so deleted.

now again 4 is FK in (6,4) & (2,4) so these will also be deleted.

again 2 is FK in (5,2) & (7,2) so deleted

similarly 5 is FK in (9,5) so deleted.

so BY DELETING ONLY (3,4) all entries are deleted thus none of the option matches.

but official key says option A is correct only.

→ On removal of row (3,4), row (4,3) must also be deleted as they depend on value 3.
→ On removal of row (4,3), row (2,4) and (6,4) must also be deleted as it depends on value 4.
→ As there is no option with row(2,4) and also the question says additional tuples should be deleted. So, option D is eliminated.
by

1 vote
1
128 views