Question

Consider two relations \(r\) and \(s\) defined on the relational schemas \(R(A,B)\) and \(S(E,C)\), respectively. \(A\) is the primary key of \(R\) and \(E\) is a foreign key of \(S\) referencing \(A\) in \(R\).

Which of the following operations will NEVER violate the foreign key constraint?

• A. Inserting records into relation r
• B. Deleting records from relation s
• C. Deleting records from relation r
• D. Inserting records into relation s

Hint:

Think about which side of the reference, the referenced primary key or the referencing foreign key, each operation changes.

Answer 1

The Correct Option is A, B

Think of $R$ as the "parent" table and $S$ as the "child" table, since $S.E$ points back to $R.A$. A foreign key constraint only cares about one thing: every $E$ value stored in $S$ must have a matching $A$ value sitting in $R$. Go through each operation with that single rule in mind.

1. Inserting records into $r$: this only grows the pool of valid $A$ values in the parent table. No existing $S$ row loses its match, so nothing can break. Always safe.
2. Deleting records from $s$: this only removes rows from the child table. Fewer rows in $S$ can never cause a reference to go missing, since you are not creating any new $E$ values that need to be checked. Always safe.
3. Deleting records from $r$: if the row you delete from the parent table has the same $A$ value that some child row's $E$ points to, that child row is now referencing a value that no longer exists. That is exactly what the constraint forbids, so this can break things.
4. Inserting records into $s$: a brand new child row might carry an $E$ value that was never a valid $A$ value in the parent table to begin with. That new row would violate the constraint the moment it is inserted. So this can also break things.

Only the two "growing the parent" and "shrinking the child" operations, inserting into $r$ and deleting from $s$, are guaranteed never to violate the foreign key constraint.

\[ \boxed{\text{(A) and (B)}} \]