Understanding and Locating SET DEFAULT Referential Actions in SQL Server

When you define a foreign key in SQL Server, you can choose what happens to the child rows when the parent row is deleted or updated. One option is SET DEFAULT. With this setting, SQL Server updates the foreign key column in the child table to its default value whenever the parent key is deleted or updated.

It’s not the most common option, but it can be useful if you want to preserve child records while moving them to a “default” category, user, or state.

How SQL Server Stores This Information

The rules for foreign keys are stored in the sys.foreign_keys catalog view. The following columns define what happens:

• delete_referential_action and its descriptive counterpart delete_referential_action_desc
• update_referential_action and its descriptive counterpart update_referential_action_desc

The numeric values for the non-_desc columns map like this:

• 0 = NO ACTION
• 1 = CASCADE
• 2 = SET NULL
• 3 = SET DEFAULT

The _desc versions are more human-readable (e.g. SET_DEFAULT) and are usually easier to work with in queries.

Here’s a query that finds all foreign keys using SET DEFAULT for either deletes or updates:

SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tr.name AS ReferencedTable,
    fk.delete_referential_action,
    fk.delete_referential_action_desc,
    fk.update_referential_action,
    fk.update_referential_action_desc
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
WHERE fk.delete_referential_action = 3
   OR fk.update_referential_action = 3;

Example

Let’s create a new example schema that contains employees and the departments they belong to. If a department gets deleted or its ID changes, we don’t want to lose the employees. Instead, we’ll assign them to a default department.

CREATE TABLE Departments (
    DepartmentId INT PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL
);

-- Insert a default "General" department
INSERT INTO Departments (DepartmentId, DepartmentName) VALUES
(0, 'General'),
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Finance');

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    EmployeeName NVARCHAR(100) NOT NULL,
    DepartmentId INT NOT NULL DEFAULT 0,
    CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentId)
        REFERENCES Departments(DepartmentId)
        ON DELETE SET DEFAULT
        ON UPDATE SET DEFAULT
);

INSERT INTO Employees (EmployeeId, EmployeeName, DepartmentId) VALUES
(201, 'Wanida', 1),
(202, 'Claude', 1),
(203, 'Lance', 2),
(204, 'Zara', 3);

SELECT 
    e.DepartmentId,
    d.DepartmentId AS ParentDepartmentId,
    d.DepartmentName,
    e.EmployeeId,
    e.EmployeeName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentId = d.DepartmentId
ORDER BY e.EmployeeId;

Output:

DepartmentId  ParentDepartmentId  DepartmentName  EmployeeId  EmployeeName
------------  ------------------  --------------  ----------  ------------
1             1                   Engineering     201         Wanida      
1             1                   Engineering     202         Claude      
2             2                   Marketing       203         Lance       
3             3                   Finance         204         Zara        

The query at the end allows us to see the departments along with their respective employees.

Here, Employees.DepartmentId has a default of 0, which maps to the “General” department. When we delete or update a department, all employees that belong to that department will be set to this default value (due to us using SET DEFAULT).

We can use the query from earlier to verify the SET DEFAULT setting:

SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tr.name AS ReferencedTable,
    fk.delete_referential_action,
    fk.delete_referential_action_desc,
    fk.update_referential_action,
    fk.update_referential_action_desc
FROM sys.foreign_keys fk
JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
WHERE fk.delete_referential_action = 3
   OR fk.update_referential_action = 3;

Result:

ForeignKeyName            ParentTable  ReferencedTable  delete_referential_action  delete_referential_action_desc  update_referential_action  update_referential_action_desc
------------------------  -----------  ---------------  -------------------------  ------------------------------  -------------------------  ------------------------------
FK_Employees_Departments  Employees    Departments      3                          SET_DEFAULT                     3                          SET_DEFAULT                   

We can see that it has SET_DEFAULT on both delete and updates. That means, whenever there’s a delete or update to the parent, this foreign key column will be set to NULL.

Test Deleting a Department

We can test this by deleting a department:

DELETE FROM Departments WHERE DepartmentId = 1;

And now let’s check the data again:

SELECT 
    e.DepartmentId,
    d.DepartmentId AS ParentDepartmentId,
    d.DepartmentName,
    e.EmployeeId,
    e.EmployeeName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentId = d.DepartmentId
ORDER BY e.EmployeeId;

Result:

DepartmentId  ParentDepartmentId  DepartmentName  EmployeeId  EmployeeName
------------  ------------------  --------------  ----------  ------------
0             0                   General         201         Wanida      
0             0                   General         202         Claude      
2             2                   Marketing       203         Lance       
3             3                   Finance         204         Zara        

Now the employees that belonged to department 1 remain, but they now belong to the “General” department (i.e., their DepartmentId is now 0).

Test Updating a Department ID

Updating a department should also result in the same outcome:

UPDATE Departments SET DepartmentId = 5 WHERE DepartmentId = 2;

Employee Lance will now point to the default department (0), since his old department ID no longer exists.

Now let’s check it again:

SELECT 
    e.DepartmentId,
    d.DepartmentId AS ParentDepartmentId,
    d.DepartmentName,
    e.EmployeeId,
    e.EmployeeName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentId = d.DepartmentId
ORDER BY e.EmployeeId;

Result:

DepartmentId  ParentDepartmentId  DepartmentName  EmployeeId  EmployeeName
------------  ------------------  --------------  ----------  ------------
0             0                   General         201         Wanida      
0             0                   General         202         Claude      
0             0                   General         203         Lance       
3             3                   Finance         204         Zara        

As expected, we can see that Lance has also been moved to the “General” department (DepartmentId = 0).

Conclusion

SET DEFAULT gives you more control over how orphaned child rows are handled. Instead of breaking referential integrity or leaving NULL values, you can redirect those rows to a known default.

The query against sys.foreign_keys (with both numeric and descriptive action columns) is handy when auditing a database to confirm where SET DEFAULT is in play. If you inherit a schema or troubleshoot strange reassignments, this check can save time and reveal exactly which relationships rely on defaults.