Understanding the Various ON DELETE Options in SQL Server Foreign Keys

It’s widely understood among SQL Server developers that foreign keys can prevent us from deleting a row from the parent table if there’s child table that references the key in that row.

But did you know that we can specify a different course of action in such cases?

For example, we could configure our foreign key to delete the child as well. Or we could set it to NULL, or to its default value.

These options are all available, thanks to the ON DELETE clause.

The Options

Here are the options available for the ON DELETE clause:

NO ACTION: Prevents the delete (and returns an error) if it would cause a foreign key violation.
CASCADE: Propagates the delete operation to the foreign key in the child table. In other words, it deletes the related row(s) in the child table (in addition to the row in the parent table).
SET NULL: Sets the foreign key in the child table to NULL.
SET DEFAULT: Sets the foreign key in the child table to its default value.

This article explores each of these options in detail, along with examples that demonstrate how each option impacts relational data in the database.

The `NO ACTION` Option

The NO ACTION option is the default setting. This means that if the ON DELETE clause is not specified, the foreign key will be set to NO ACTION.

When NO ACTION is set, SQL Server will not allow any parent rows to be deleted if there’s a dependency. It will return an error, and nothing will be deleted (nothing in the parent table, and nothing in the child table).

Behavior

Immediate Effect: When you attempt to delete a record in the parent table that is referenced by a child table, SQL Server will check for existing references in the child table. If any such references exist, the delete operation is blocked, and an error is raised.
Use Case: This option is useful when you want to ensure that all related records are explicitly handled before deleting a parent record. For example, you may want to manually review and delete child records first to avoid accidental data loss.

Example

Suppose we create two related tables and populate them with data:

-- Create the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

-- Create the Employees table with a foreign key referencing Departments
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE NO ACTION
);

-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES 
(1, 'HR'),
(2, 'Finance');

-- Insert data into Employees
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES 
(1, 'Zoe Angkor', 1),
(2, 'Barry Fletcher', 2);

Now let’s try to delete a department that is referenced by the Employees table:

DELETE FROM Departments WHERE DepartmentID = 1;

Output:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employees__Depar__0DCF0841". The conflict occurred in database "test", table "dbo.Employees", column 'DepartmentID'.

As expected, we got an error.

The `CASCADE` Option

The CASCADE option ensures that when a record in the parent table is deleted, all corresponding records in the child table are also also deleted. This maintains referential integrity by ensuring that no orphaned records remain in the child table.

Behavior

Automatic Deletion: When a parent record is deleted, SQL Server will automatically delete all child records that reference the parent.
Use Case: CASCADE is ideal when the child records are dependent on the existence of the parent record and should not exist independently. For example, if you have an Orders table and an OrderDetails table, deleting an order should also delete its associated order details.

Example

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE
);

-- Create the OrderDetails table with a foreign key referencing Orders
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductName NVARCHAR(100),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE
);

-- Insert data into Orders
INSERT INTO Orders (OrderID, OrderDate) VALUES 
(1, '2024-08-21'),
(2, '2024-08-22');

-- Insert data into OrderDetails
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductName) VALUES 
(1, 1, 'Product A'),
(2, 1, 'Product B'),
(3, 2, 'Product C');

-- Check the current contents
SELECT * FROM Orders;
SELECT * FROM OrderDetails;

Here’s the output of the SELECT statements:

OrderID  OrderDate               
-------  ------------------------
1        2024-08-21T00:00:00.000Z
2        2024-08-22T00:00:00.000Z

OrderDetailID  OrderID  ProductName
-------------  -------  -----------
1              1        Product A  
2              1        Product B  
3              2        Product C

Now let’s delete an order in the parent table and check the tables’ contents again:

-- Delete row from parent
DELETE FROM Orders WHERE OrderID = 1;

-- Check the current contents
SELECT * FROM Orders;
SELECT * FROM OrderDetails;

Output:

OrderID  OrderDate               
-------  ------------------------
2        2024-08-22T00:00:00.000Z

OrderDetailID  OrderID  ProductName
-------------  -------  -----------
3              2        Product C

We can see that both the parent row and the child rows are deleted. Two child rows were deleted because they both pointed to the deleted parent key.

The `SET NULL` Option

With the SET NULL option, when a parent record is deleted, the foreign key column in the child table is set to NULL. This option is useful when the relationship is optional and the child record can exist without being linked to a parent.

Behavior

Nullification: On deletion of a parent record, the foreign key in the child table is set to NULL.
Use Case: SET NULL is appropriate when the child records should remain in the database, but the association with the parent is no longer relevant. For example, in a situation where an employee can belong to a department, but may remain in the company even if the department is deleted, this option makes sense.

Example

-- Create the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

-- Create the Employees table with a foreign key referencing Departments
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET NULL
);

-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES 
(1, 'HR'),
(2, 'Finance');

-- Insert data into Employees
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES 
(1, 'Zoe Angkor', 1),
(2, 'Barry Fletcher', 2);

-- Check the data in both tables
SELECT * FROM Departments;
SELECT * FROM Employees;

Output:

DepartmentID  DepartmentName
------------  --------------
1             HR            
2             Finance       

EmployeeID  EmployeeName    DepartmentID
----------  --------------  ------------
1           Zoe Angkor      1           
2           Barry Fletcher  2

Now let’s delete the HR department and check the result:

-- Delete a row from the parent table
DELETE FROM Departments WHERE DepartmentID = 1;

-- Check the data in both tables
SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName
------------  --------------
2             Finance       

EmployeeID  EmployeeName    DepartmentID
----------  --------------  ------------
1           Zoe Angkor      null        
2           Barry Fletcher  2

The employee from the (now deleted) HR department now has a NULL DepartmentID.

The `SET DEFAULT` Option

The SET DEFAULT option will set the foreign key column in the child table to its default value when the corresponding parent record is deleted. This option requires that the foreign key column has a default value defined.

Behavior

Default Assignment: When a parent record is deleted, the foreign key in the child table is set to a predefined default value.
Use Case: SET DEFAULT is useful when you want to reassign the orphaned child records to a default category or status, rather than leaving them unassigned (as with SET NULL). For instance, you might reassign employees to a default department if their current department is deleted.

Example

-- Create the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

-- Create the Employees table with a foreign key referencing Departments
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT DEFAULT 0, -- Default to 0 (unassigned or generic department)
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET DEFAULT
);

-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES 
(0, 'Special Operations'),
(1, 'HR'),
(2, 'Finance');

-- Insert data into Employees
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES 
(1, 'Zoe Angkor', 1),
(2, 'Barry Fletcher', 2);

-- Check data
SELECT * FROM Departments;
SELECT * FROM Employees;

Here’s the data so far:

DepartmentID  DepartmentName    
------------  ------------------
0             Special Operations
1             HR                
2             Finance           

EmployeeID  EmployeeName    DepartmentID
----------  --------------  ------------
1           Zoe Angkor      1           
2           Barry Fletcher  2

Now let’s delete the HR department and check the data again:

-- Delete a parent row
DELETE FROM Departments WHERE DepartmentID = 1;

-- Check the result
SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName    
------------  ------------------
0             Special Operations
2             Finance           

EmployeeID  EmployeeName    DepartmentID
----------  --------------  ------------
1           Zoe Angkor      0           
2           Barry Fletcher  2

We can see that Zoe has now been moved to the Special Operations department.

Choosing the Right `ON DELETE` Option

Selecting the appropriate ON DELETE option depends on the nature of your data and the relationships between your tables. Here are some considerations:

Data Integrity: NO ACTION enforces strict data integrity by preventing deletion of parent records if dependent records exist.
Automatic Cleanup: CASCADE is useful when child records should be automatically deleted along with their parent.
Optional Relationships: SET NULL is ideal when the child record can exist without a parent, but the association should be removed on deletion.
Default Reassignment: SET DEFAULT is useful when you want to reassign orphaned records to a specific default category or entity.

The Options

The NO ACTION Option

Behavior

Example

The CASCADE Option

Behavior

Example

The SET NULL Option

Behavior

Example

The SET DEFAULT Option

Behavior

Example

Choosing the Right ON DELETE Option

The `NO ACTION` Option

The `CASCADE` Option

The `SET NULL` Option

The `SET DEFAULT` Option

Choosing the Right `ON DELETE` Option