3.8k 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
asked | 3.8k views
0

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

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

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

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.

+1
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.)

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

answered by Active (3.3k points)
edited
+53
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 ?
0
@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),
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)

+1
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

0

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

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.

answered by Boss (19.1k points)
0

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

+1

@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 :)

    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.

answered by Active (4.7k points)

1
2