Consider two tables in a relational database with columns and rows as follows:
Table: Student| Roll_no | Name | Dept_id |
|---|
| 1 | ABC | 1 |
| 2 | DEF | 1 |
| 3 | GHI | 2 |
| 4 | JKL | 3 |
Table: Department| Dept_id | Dept_name |
|---|
| 1 | A |
| 2 | B |
| 3 | C |
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
Answer: (C)
Explanation:
- 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