GATE CS 2014 Set 1 Q.32 (CS Q.22): SQL Queries and Integrity Constraints

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.

CREATE TABLE S (
  a INTEGER,
  d INTEGER,
  e INTEGER,
  PRIMARY KEY (d),
  FOREIGN KEY (a) references R
)

Which one of the following statements is CORRECT?

S1 is TRUE and S2 is FALSE.

Both S1 and S2 are TRUE.

S1 is FALSE and S2 is TRUE.

Both S1 and S2 are FALSE.

Official answer and solution

Solution

Analyze SQL constraints and foreign key syntax rules.

Evaluate each statement:

Statement S1: 'A foreign key declaration can always be replaced by an equivalent check assertion in SQL.'
- A foreign key not only enforces referential integrity on insert/update of the child table, but also dictates cascade actions (e.g., ON DELETE CASCADE, ON UPDATE SET NULL) when the referenced row in the parent table is deleted or modified. Standard check constraints/assertions cannot replicate these automated referential update behaviors.
- Hence S1 is FALSE.
Statement S2: 'Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition: ... FOREIGN KEY (a) references R'
- In SQL standard and relational database design, a foreign key must reference a candidate key (primary key or unique constraint) of the referenced table.
- In table $R$ , the primary key is composite: $(a, b)$ . A foreign key referencing only attribute $a$ does not reference a candidate key of $R$ , which is an error and violates SQL semantics.
- Hence S2 is FALSE.

Both S1 and S2 are FALSE.

Final answer

Common trap

Thinking a foreign key can reference any individual attribute belonging to a composite primary key.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.