GATE CSE 2014 | Set 1 | Question: 22

Question

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?

• 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

Comments

yashhoskere

that’s what Harsh+Kumar is trying to say ,

don’t fall in a trap for the attributes name ..what if S had attributes like x,y,z and z is foreign key references R .

---

For the Statement A : A foreign key declaration may have a cascade delete option which is not possible with just a check insertion A foreign key declaration may have a cascade delete option which is not possible with just a check insertion

---

for S1 :- Theoretically yes, practically no.

Answer 1

(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).

Comments

Anyone please explain how to write correct sql Query for s2?

---

@pC

check assertion:

ex: create table Emp( emp_id number primary key,

                                      salary number check(salary > 5000));

means we have to insert only the salary of employee whose salary is greater than 5000. if we insert <5000 then it does't allow us to insert.

---

@its_vv Correct query for S2: Foreign key in S should be on the same number of attributes as the primary key in R
 

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

 

Variation: Query given in the question would have been correct if c was a candidate key in R and we mentioned it in the statement:

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

Answer 2

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.

Comments

@Ayush Upadhyaya If 'a' and 'b' are int how could you be sure that they can act as key for R. It may be possible that both 'a' and 'b' values be same for two rows.

---

FOREIGN KEY (a) references R

Does this line means that it references to first attribute of R ? 

---

gatecse

FOREIGN KEY (a) references R

Does this line means that it references to first attribute of R ? 

sir pls clear this!

Answer 3

    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.

Comments

Nice explaination, but can you give example for the first one? Like how can we even create foreign key constraint using check, forgetting about on-delete-cascade constraint for some time?

 

Answer 4

A SQL check constraint is a rule that limits the values that can be inserted or updated in a column (or set of columns) of a table. It enforces data integrity by ensuring that only values satisfying a specified condition or expression are allowed. If an attempt is made to enter data that violates the constraint, the database rejects the operation.

Check constraints can be defined at the column level or table level. For example, you could use a check constraint to ensure that an "Age" column must be greater than or equal to 18, or to ensure that a "Salary" column's value must always be above a certain amou

Cannot reference values in other tables; checks only conditions within the current row or tablent.

Answer 5

ans is D

S1 fk can not be replaced by check
S2 : pk is (a,d)