Given the following statements: S1: A foreign key declaration can always be…
2014
Given the following statements:
S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL.
S2: Given the table \(R(a,b,c)\) where \(a\) and \(b\) together form the primary key, the following is a valid table definition.
Which one of the following statements is CORRECT?
- A.
S1 is TRUE and S2 is FALSE.
- B.
Both S1 and S2 are TRUE.
- C.
S1 is FALSE and S2 is TRUE.
- D.
Both S1 and S2 are FALSE.
Attempted by 723 students.
Show answer & explanation
Correct answer: D
Answer: Both statements are false.
Explanation:
Statement S1 is false. CHECK constraints are evaluated per row and, in standard SQL, cannot contain subqueries that reference other tables, so they cannot enforce referential integrity between tables. Although standard SQL defines ASSERTIONs that can express cross-table conditions, many SQL implementations do not support ASSERTION; therefore a foreign key declaration cannot always be replaced by an equivalent CHECK constraint in practice.
Statement S2 is false. The table R has a composite primary key (a,b), so a foreign key that references R must reference all columns of the referenced candidate key. Declaring FOREIGN KEY(a) REFERENCES R is invalid because it references only part of the primary key. To be valid you must reference both columns, for example FOREIGN KEY(a,d) REFERENCES R(a,b) with corresponding columns, or ensure R has a key on a alone.