GATE | GATE-IT-2004 | Question 78
Consider two tables in a relational database with columns and rows as follows:
Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id
What will happen if we try to execute the following two SQL statements?
- update Student set Dept_id = Null where Roll_on = 1
- update Department set Dept_id = Null where Dept_id = 1
(A) Both (i) and (ii) will fail
(B) B) (i) will fail but (ii) will succeed
(C) (i) will succeed but (ii) will fail
(D) Both (i) and (ii) will succeed
- update Student set Dept_id = Null where Roll_on = 1 ->This will succeed as roll no is primary key and we may set a foreign key NULL
- update Department set Dept_id = Null where Dept_id = 1 ->As Student table(Roll no 1 and 2) are referring to Dept_id so according to referential integrity constraint we can NOT delete Dept_id.However, Exceptionally we may overcome it if we want using Cascaded Delete option.
Quiz of this Question
Please comment below if you find anything wrong in the above post