23,214 views
72 votes
72 votes

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?

  1. S1 is TRUE and S2 is FALSE
  2. Both S1 and S2 are TRUE
  3. S1 is FALSE and S2 is TRUE
  4. Both S1 and S2 are FALSE

3 Answers

Best answer
111 votes
111 votes

(D) Both are false.

S1: Foreign key constraint means a lot of constraints it has to be a primary key(which in turn has few constraints).

Alternate reason: Using a check condition we can have the same effect as Foreign key while adding elements to the child table. But when we delete an element from the parent table the referential integrity constraint is no longer valid. So, a check constraint cannot replace a foreign key.
So, we cannot replace it with a single check.

S2:  if $a$ and $b$ form a primary key in $R$, $a$ alone cannot form a foreign key. i.e. $R(\underline{a, b}, c)$ and $S(a, d, e)$, $a$ of $S$ references to $a$ of $R$, but $a$ of $R$ is not candidate key, instead a prime attribute since $a, b$ combined is a key.

Foreign key definition: it should be a candidate key in some other table(in our case it is only a prime attribute).

edited by
27 votes
27 votes

I think this is a more reasonable solution

Explanation:

    S1: A foreign key declaration can always
        be replaced by an equivalent check
        assertion in SQL. 

False:
Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

    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) 

False:
Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S doesn’t uniquely identify a row in table R.

Take an example 

R                                                           

A B C
1 1 L
1 2 Z
2 1 K

S

A D E
1 2 H
1 4 G
1 3 T

Now, here in S, attribute A alone cannot be used to uniquely identify a particular row in R. Hence S2 is false.

Hence (D) is the answer.

3 votes
3 votes
    S1: A foreign key declaration can always
        be replaced by an equivalent check
        assertion in SQL. 

False:
Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

    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) 

False:
Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field ‘a’ of R. The field ‘a’ in table S doesn’t uniquely identify a row in table R.

Answer:

Related questions

43 votes
43 votes
4 answers
2