Questions on Foreign Keys and Constraint Violations –

Questions on Foreign Keys and Constraint Violations –

Question 1 : Assume one is primary key and other one is Foreign Key. Identify which of the set is the primary key and which of the set  is foreign key ?

A C 2 4 3 4 4 3 5 2 7 2 9 5 6 4

Solution :
Here A is the PRIMARY KEY, and C is the FOREIGN KEY. Because A follows all the properties of primary key. That is,
1) A does not contain any null values.
2) All the values in A are distinct. So A is Primary key.
C is the Foreign key referencing A as it contains the values referencing with A.

Question 2 :
What are the tuples additionally deleted to preserve referential integrity when the tuple(2,4) is deleted ?

Solution :

Question 3:
Let R(a, b, c) and S(d, e, f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations on R and S
(a) Insert into R
(b) Insert into S
(c) Delete from R
(d) Delete from S
Which of the following statements is true about the referential integrity constraint above?
(A) None of (a), (b), (c), or (d) can cause its violation
(B) All of (a), (b), (c), and (d) can cause its violation
(C) Both (a) and (d) can cause its violation
(D) Both (b) and (c) can cause its violation

Solution : (D) Both (b) and (c) can cause its violation Let’s take an example,

R a b c S1  — — S2  — — S3  — —

S d e f S1 — — S2  — —

Here ‘d’ is the foreign key of S and let ‘a’ is the primary key of R. a) Insertion into R – will cause no violation as it does not result inconsistency. b) Insertion into S – may cause violation because there may not be entry of the tuple in relation R. Example entry of <S4,-,-> is not allowed because there is no S4 tuple exists in relation R and it results in inconsistency. c) Delete form R – may cause violation. For example, Deletion of tuple <S2,-,-> will cause violation as there is entry of S2 in the foreign key table and so produce inconsistency. d) delete from S – will cause no violation as it does not result inconsistency.

]]>