Question

Consider two relations R(A,B) and S(E,C). A is primary key and E is a FK referring A. Which of the following operations never violate FK constraint?

• A. Insert in s
• B. Delete from R
• C. Insert in R
• D. Delete from S

Hint:

Think of the FK constraint as a "parent-child" rule: every child must have a valid parent. - Creating a child with no parent (Insert into S) is a violation. - Killing a parent with existing children (Delete from R) is a violation. - Creating a new potential parent (Insert into R) is always safe. - Removing a child (Delete from S) is always safe.

Answer 1

The Correct Option is D

Step 1: Understanding the Question:
We have two tables, R (parent table) and S (child table). The foreign key (FK) in S refers to the primary key (PK) of R. This means that any value in column E of table S must also exist in column A of table R. We need to identify which database operations will never cause this rule to be broken.
Step 2: Detailed Explanation:
Let's analyze each operation:
- (A) Insert in S: When we insert a new row into S, we provide a value for the foreign key column E. If this value does not already exist in the primary key column A of table R, the foreign key constraint will be violated. So, this operation {can} violate the constraint.
- (B) Delete from R: When we delete a row from the parent table R, we remove a primary key value from column A. If this value is currently being referenced by one or more rows in the foreign key column E of table S, those references in S will become invalid ("dangling pointers"). This would violate the foreign key constraint. So, this operation {can} violate the constraint. (Note: database systems can be configured with `ON DELETE` actions like `CASCADE` or `SET NULL` to handle this, but the fundamental violation can occur).
- (C) Insert in R: When we insert a new row into the parent table R, we are adding a new, valid primary key to column A. This action does not affect any existing rows in S. It only increases the set of valid keys that S can refer to. Therefore, it can never violate the FK constraint.
- (D) Delete from S: When we delete a row from the child table S, we are removing a foreign key reference. This action does not create any invalid references, it simply removes an existing valid one. It cannot violate the FK constraint.
Step 3: Final Answer:
The operations that will never violate the foreign key constraint are inserting into the parent table (R) and deleting from the child table (S).