In SQL Server, foreign keys are essential for maintaining referential integrity between tables. When setting up a foreign key, we have various options for handling actions when the referenced data is deleted. One of these options is ON DELETE SET DEFAULT.

This feature can be particularly useful in scenarios where it’s important to maintain the relationship while ensuring that the foreign key column doesn’t end up with invalid or null values.

What Exactly Does ON DELETE SET DEFAULT Do?

The ON DELETE SET DEFAULT option is used in the context of foreign key constraints. When we define a foreign key on a table, we can specify how the database should react when a referenced record in the parent table is deleted.

The default behavior is to return an error and prevent the deletion. We can also define this explicitly by using ON DELETE NO ACTION. But we don’t have to use the default. We have other options.

If we choose the ON DELETE SET DEFAULT option, SQL Server will set the foreign key column in the dependent table to a default value (as defined for that column) whenever the referenced record is deleted.

Why Use ON DELETE SET DEFAULT?

This option is particularly useful in scenarios where:

• We need to retain the dependent record even after the parent record is deleted.
• The dependent record should not have an invalid or null reference after the deletion.
• We want to avoid breaking referential integrity by ensuring that the foreign key always has a valid, non-null value.

Example Use Case: Employee-Department Relationship

Let’s consider a scenario where a company has a database with two tables: Departments and Employees. Each employee belongs to a department, but if a department is deleted, we want to move the employees to a “General” department rather than deleting the employees or leaving them with a null department reference.

Step 1: Create the Tables

We will create two tables: Departments and Employees. The Employees table will have a foreign key that references the Departments table:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50) NOT NULL,
    DepartmentID INT DEFAULT 1,  -- Default department ID
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT
);

Step 2: Insert Initial Data

Next, we’ll insert some sample data into these tables and select all data:

-- Insert departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'General'), (2, 'Sales'), (3, 'HR');

-- Insert employees
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES 
(1, 'Jess Bower', 2),
(2, 'Chad Davies', 3),
(3, 'Max Power', 2);

-- Select all data
SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName
------------  --------------
1             General       
2             Sales         
3             HR            

EmployeeID  EmployeeName  DepartmentID
----------  ------------  ------------
1           Jess Bower    2           
2           Chad Davies   3           
3           Max Power     2           

Step 3: Demonstrate ON DELETE SET DEFAULT

Now, let’s delete the Sales department and see how the ON DELETE SET DEFAULT option works:

-- Delete the Sales department
DELETE FROM Departments WHERE DepartmentID = 2;

-- Verify the changes
SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName
------------  --------------
1             General       
3             HR            

EmployeeID  EmployeeName  DepartmentID
----------  ------------  ------------
1           Jess Bower    1           
2           Chad Davies   3           
3           Max Power     1           

After running the delete statement, we can see that all employees who were in the Sales department (DepartmentID = 2) now have their DepartmentID set to 1, which corresponds to the General department.

Recap

In the above example:

• The Departments table contains various departments, including a General department with DepartmentID = 1.
• The Employees table references the Departments table via a foreign key constraint.
• When the Sales department (DepartmentID = 2) is deleted, the ON DELETE SET DEFAULT constraint ensures that the employees previously in the Sales department are reassigned to the General department, thus maintaining data integrity.

Other Options

ON DELETE SET DEFAULT isn’t the only option we can use for these types of scenarios. The ON DELETE clause accepts the following arguments:

• NO ACTION (the default)
• CASCADE
• SET NULL
• SET DEFAULT

The option we chose will depend on the situation, whether columns are nullable, etc.