You want to enforce referential integrity between Employees and Departments tables. When a department is deleted, you want all employees in that department to be reassigned to department ID 0 (which means 'Unassigned'). Which foreign key option should you use?
AFOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE RESTRICT
BFOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
CFOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET NULL
DFOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT
Step-by-Step Solution
Solution:
Step 1: Understand ON DELETE SET DEFAULT
This option sets the foreign key column to its default value when the referenced row is deleted.
Step 2: Match requirement
Since you want employees reassigned to department ID 0, set DepartmentID column default to 0 and use ON DELETE SET DEFAULT to assign that value automatically.
Final Answer:
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT -> Option D
Quick Check:
Reassign on delete = ON DELETE SET DEFAULT [OK]
Quick Trick:Use ON DELETE SET DEFAULT to assign default on delete [OK]
Common Mistakes:
MISTAKES
Using CASCADE deletes employees instead of reassigning
Using SET NULL when column disallows NULL
Using RESTRICT blocks deletion
Master "Table Relationships" in SQL
9 interactive learning modes - each teaches the same concept differently
