Consider two tables in a relational database with columns and rows as follows:…
2004
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
Attempted by 644 students.
Show answer & explanation
Correct answer: C
Answer: (i) will succeed but (ii) will fail.
Why the first update succeeds: Updating the Student.Dept_id to NULL is allowed as long as the Student.Dept_id column is defined to allow NULL values. Foreign key constraints permit NULLs in the referencing column, and a NULL value does not need to match any value in the parent table.
Why the second update fails: Department.Dept_id is the primary key and primary keys are NOT NULL by definition. Attempting to set the primary key to NULL will violate the NOT NULL/primary key constraint. Even if NULL were allowed, changing a parent key to NULL would break referential integrity for Student rows that reference Dept_id = 1.
Additional note: If you needed to remove the department while preserving referential integrity, you would either delete or update referencing Student rows first (or define appropriate ON DELETE/ON UPDATE actions), but you cannot set the parent primary key to NULL.