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 R and S
1. Insert into R
2. Insert into S
3. Delete from R
4. Delete from S
Which of the following can cause violation of the referential integrity constraint above?
(A) None of (1), (2), (3) or (4) can cause its violation
(B) All of (1), (2), (3) and (4) can cause its violation
(C) Both (1) and (4) can cause its violation
(D) Both (2) and (3) can cause its violation
Explanation: Here table R contains a primary key which is referred by the foreign key in table S.
Let’s take an example to make it more clear:
There are two tables containing information about the customers and their orders. Here Customers table contains Cust_ID as a primary key which is referred by Orders table as a foreign key.
- Statement 1 suggests that you can enter customer details in the Customers table. Yes, it is possible to have customers details even if they don’t have any orders yet. Hence doesn’t cause any violation.
- Statement 2 suggests that you can enter customer orders in the Orders table. But without having a unique Cust_ID or without having customer details, how you can enter their order details. Hence this statement causes the violation.
- Statement 3 suggests that you can delete customer details from the Customer table. If you do so, then how the Orders table will get the unique customer details if that customer doesn’t exist anymore? Hence this statement causes the violation.
- Statement 4 suggests that you can delete customer orders from the Orders table. Yes, this is possible for a unique Cust_ID to delete some or all information from Orders table if you don’t require that information anymore. Hence this statement doesn’t cause any violation.
Therefore option (D) is correct.
This explanation is contributed by Anchal Kulwal.
Quiz of this Question