search
Log In
50 votes
7.9k views

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
in Databases 7.9k views
0

@Bikram sir what is the meaning of check assertion in sql

15
Check assertion is a constraint used in SQL , it  is used for limiting the value range for any column.
0
thank u sir
3

An assertion is a predicate expressing a condition we wish the database to always satisfy

Refer ->http://www.cs.sfu.ca/CourseCentral/354/zaiane/material/notes/Chapter6/node8.html

https://www.w3schools.com/sql/sql_check.asp

https://stackoverflow.com/questions/2443322/what-is-the-difference-between-triggers-assertions-and-checks-in-database


But i think via Triggers same thing could be achieved( means whatever is goals are archived via foreign key). What is your opinion ?

ping @Krish__, @rahul sharma 5,  @Red_devil, @Shivam Chauhan, @Tuhin Dutta, @Anu007,  @Ashwin Kulkarni @reena_kandari  and @srestha ji.

2
With checks it wouldn't definitely work. But with triggers I guess it's possible since AFTER UPDATE command can be used to set up to perform the required action on the child table if necessary. (ONLY if the user has the necessary privileges.)
6

The real reason for the second part is this: 

FOREIGN KEY (a) references R

This statement does not mean that S is referring to attribute 'a' of R. In fact, what it means is that the attribute 'a' of relation S will act as a foreign key to relation R. By default, this would mean that 'a' (of S) will refer to the combined attributes of R (which make up the primary key for R). 

However, this will be an error only because a single attribute of relation S cannot refer to more than one attribute of R. (Just think about it... how can a single attribute refer to two attributes of another relation... not possible).

This is the only reason that this statement will be erroneous.  

Also, this statement does not mean that attribute 'a' of relation R is getting referenced. Having similar attribute names have confused many of us, I think.

Those who think so, just think about an alternate statement...what if it were written 

FOREIGN KEY (d) references R

would you still say that attribute "a" of R is getting referenced?

3 Answers

83 votes
 
Best answer

(D)Both are false

S1: Foreign key constraint means a lot of constraints it has to be a primary key(which intrun 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 forms a primary key in R,  a alone cannot form a foreign key. i.e. R(a,b,c) and S( a,d,e ) a of S references to a of R but a of R is not candidate key but a prime attribute since a,b combine 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
83
Reason for S1 is not exactly correct. 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.
0
nice explanation, Aravind :)
0
What is chechk assertion in SQL  mean ?
1
@Arjun sir my doubt here is defination not include b as attribute so why we go logical simple we conclude s2  false or this is typo.
0
So lets just say we added another check assertion on the table which is having the primary key to make sure that deletion should be successfull only when the referring table does not have any entry referring to the table with primary key, will adding this check condition along with the specified one be able to replace foreign key ?
0
set of valid values for a foreign key constraint can be changed with SQL DML: SQL insert, update, delete statements. The set of valid values for an equivalent check constraint cannot be changed that way. You have to use SQL DDL to change a check constraint.

Also, different users might have different privileges on the referenced table of a foreign key constraint. Some might have only insert privileges, some might have insert and update privileges, etc. You can't control changes to a check constraint that way. A user that's allowed to change the check constraint can change it in any way so S1 is false and S2 is completely false same reason as @aravind
0
What should be correct definition for S2?
2

Ans here is D Both S1 and S2 are FALSE.

Explanation :

S1 --> Check constraint in SQL is used for limiting the value range for any column. However for foreign key we need to check referential integrity i.e column(value) you are refering in Dependent table is present in Parent table or not (It must be present in parent table).

S2 --> If a table has primary key which is combination of more than 1 attribute then table definition must be something like this ..

CREATE TABLE Persons
(
   P_Id int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255),
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName)

3
In Query It is specified that references(R) how one can conclude from this that S refers to only prime attribute of R i .e a or b ?
0
Sir, please tell any source for learning this Assetion and Check Constraint..And Can we replace Foreign key constraint if we have liberty to apply more than one check constraint
0

anchitjindal07 Even if we apply many check constraint, how are we going to keep track of future referential constraint violations? 

0
Can a foreign key be replaced by multiple check constraints ?
0
@satyam1996 i think you are right
0

Using a check condition we can have the same effect as Foreign key while adding elements to the child table

How we can say that In insertion case check has same effect as foreign key

Let PK - A{1,3,5,8}

and FK - A'{1,1,3,5}  //A' reffering to A

Now What check constraint on A' will ensure that A'$\subseteq$A

1

@aravind Shouldn't S1 be true? look at this screenshot from Korth (4.4.7, P. No 134, 6th edition).

 

0
See the first comment on this answer, it clarifies why CHECK is not used to implement foreign key :)
0

Yep. I saw that explanation but that's what I'm confused about. Book clearly mentioned that Check condition verifies even in case of deletion/modification in parent relation (time_slot).

So isn't it mean that if we're deleting a record from parent relation then Check will verify if there is any record in another table which links back to this record via foreign key and deletes that linked record as well?

0
In general in CHECK clause we are not going to check condition after deletion.

But here along with CHECK, 'IN' is used so if we delete a tuple then result of

SELECT timeslot_id FROM timeslot;

might change. Which may lead to violation of CHECK condition.

Hope it is clear now....
0
In case of deletion is there any possibility if using triggers when check constraint violates?

If possible I think it would handle the situation.
0

@srestha foreign key constraint and foreign key ?

i am confused. i think i messed up something.

 

0

What's the difference between two?

@VIDYADHAR SHELKE 1

1
Foreign Key can be NULL as well as may contain duplicate tuples.

 foreign key constraint = it should be a candidate key in some other table.

 i am doing something wrong but not getting what.
0
Yes, that's correct.

What you are not getting?
0
can u give example where to use both.
0

 foreign key constraint and foreign key

Both refer same . Where  r u getting different meaning for both?

0

ma'am foreign key allow duplicate and null values .

but above answer said that " Foreign key definition: it should be a candidate key in some other table(in our case it is only a prime attribute). "

candidate key does not allow duplicate & null values

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

Does this mean, that if i want foreign key from table A to B on attribute "X" but its not candidate key in B but is candidate key in table C. Will that work????

0

S2 could be correct if 'a' be the primary key of R. But, primary key could be ab,bc or abc also.

By default, 'a' of S will refer to 'a' of R. So, S2 is false. 

18 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

@Ayush Upadhyaya

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

This will be valid right ?


Composite foreign key referencing composite primary key

3

@jatin khachane 1-Here domain of the attributes of relation R are not told.So, we cannot assume that what you have provided is exactly correct.However, if a and b are int, then it is fine.

Try executing some SQL code  on it,share your result and let me know also :)

0
@Ayush bhai i got the second one but i am not getting first one can you give a little example
0

@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.

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

27 votes
4 answers
1
5.1k views
Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) You want to display the last names and hire dates of all latest hires in their respective departments in the ... because of pairwise comparison. It generates an error because of the GROUP BY clause cannot be used with table joins in a sub-query.
asked Sep 28, 2014 in Databases jothee 5.1k views
30 votes
4 answers
2
4.9k views
Given the following two statements: S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF. S2: $AB \to C$, $D \to E$, $E \to C$ is a minimal cover for the set of functional dependencies $AB \to C$, $D \to E$, $AB \to E$, $E \to C$. ... one of the following 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.
asked Sep 26, 2014 in Databases jothee 4.9k views
19 votes
3 answers
3
3.8k views
Consider the following four schedules due to three transactions (indicated by the subscript) using read and write on a data item x, denoted by $r(x)$ and $w(x)$ respectively. Which one of them is conflict serializable? $r_1(x)$; $r_2(x)$; $w_1(x)$; $r_3(x)$; $w_2(x)$; $r_2(x)$; $r_1(x)$; $w_2(x)$ ... $r_2(x)$; $r_1(x)$; $w_2(x)$; $w_1(x)$; $r_2(x)$; $w_2(x)$; $r_3(x)$; $r_1(x)$; $w_1(x)$;
asked Sep 26, 2014 in Databases jothee 3.8k views
25 votes
4 answers
4
2.8k views
Consider the relation scheme $R = (E, F, G, H, I, J, K, L, M, N)$ and the set of functional dependencies $\left\{ \{E, F \} \to \{G\}, \{F\} \to \{I, J\}, \{E, H\} \to \{K, L\}, \\ \{K\} \to \{M\}, \{L\} \to \{N\}\right\}$ on $R$. What is the key for $R$? $\{E, F\}$ $\{E, F, H\}$ $\{E, F, H, K, L\}$ $\{E\}$
asked Sep 26, 2014 in Databases jothee 2.8k views
...