Finding Foreign Keys that Use SET NULL for Deletes and Updates in SQL Server

When you set up foreign key relationships in SQL Server, you have a choice for how changes in the parent table affect related rows in the child table. One option is SET NULL, which replaces the foreign key value with NULL whenever the parent row is deleted or updated.

This behavior is useful in scenarios where you’d rather keep the child record around but cut the link once the parent no longer exists. For example, if a project is deleted, you might want to keep related tasks but mark their ProjectId as NULL.

The problem is that it’s not always obvious which foreign keys are configured with SET NULL, especially in large databases. Fortunately, SQL Server’s system views make it possible to query this information directly.

Querying Foreign Keys with `SET NULL` Actions

You can use the catalog views sys.foreign_keys and sys.foreign_key_columns to find these constraints. The main fields are:

delete_referential_action – tells you what happens when the parent row is deleted.
update_referential_action – tells you what happens when the parent key is updated.

The values map like this:

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

There’s also a delete_referential_action_desc column and an update_referential_action_desc column. These provide the actual description of the action (e.g., SET_NULL, CASCADE, etc).

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

SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tr.name AS ReferencedTable,
    fk.delete_referential_action_desc AS DeleteAction,
    fk.update_referential_action_desc AS UpdateAction
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 = 2
   OR fk.update_referential_action = 2;

This gives you the foreign key name, the parent and referenced tables, and the actions defined.

Example

Let’s build a small example so you can test this for yourself. Run this in a fresh database:

CREATE TABLE Projects (
    ProjectId INT PRIMARY KEY,
    ProjectName NVARCHAR(100) NOT NULL
);

CREATE TABLE Tasks (
    TaskId INT PRIMARY KEY,
    TaskName NVARCHAR(100) NOT NULL,
    ProjectId INT NULL,
    CONSTRAINT FK_Tasks_Projects FOREIGN KEY (ProjectId)
        REFERENCES Projects(ProjectId)
        ON DELETE SET NULL
        ON UPDATE SET NULL
);

INSERT INTO Projects (ProjectId, ProjectName) VALUES
(1, 'AI Research'),
(2, 'Migration to Cloud');

INSERT INTO Tasks (TaskId, TaskName, ProjectId) VALUES
(101, 'Draft proposal', 1),
(102, 'Set up compute cluster', 1),
(103, 'Migrate database', 2),
(104, 'Update documentation', 2);

SELECT 
    p.ProjectId AS ParentProjectId,
    p.ProjectName,
    t.TaskId,
    t.TaskName,
    t.ProjectId
FROM Tasks t
LEFT JOIN Projects p ON t.ProjectId = p.ProjectId
ORDER BY t.TaskId;

Output:

ParentProjectId  ProjectName         TaskId  TaskName                ProjectId
---------------  ------------------  ------  ----------------------  ---------
1                AI Research         101     Draft proposal          1        
1                AI Research         102     Set up compute cluster  1        
2                Migration to Cloud  103     Migrate database        2        
2                Migration to Cloud  104     Update documentation    2

Now you have Projects as the parent table and Tasks as the child. The foreign key is set to SET NULL on both delete and update.

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

SELECT 
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tr.name AS ReferencedTable,
    fk.delete_referential_action_desc AS DeleteAction,
    fk.update_referential_action_desc AS UpdateAction
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 = 2
   OR fk.update_referential_action = 2;

Result:

ForeignKeyName     ParentTable  ReferencedTable  DeleteAction  UpdateAction
-----------------  -----------  ---------------  ------------  ------------
FK_Tasks_Projects  Tasks        Projects         SET_NULL      SET_NULL

We can see that it has SET_NULL 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.

Now let’s try deleting project 1:

DELETE FROM Projects WHERE ProjectId = 1;

And now let’s check the data again:

SELECT 
    p.ProjectId AS ParentProjectId,
    p.ProjectName,
    t.TaskId,
    t.TaskName,
    t.ProjectId
FROM Tasks t
LEFT JOIN Projects p ON t.ProjectId = p.ProjectId
ORDER BY t.TaskId;

Result:

ParentProjectId  ProjectName         TaskId  TaskName                ProjectId
---------------  ------------------  ------  ----------------------  ---------
null             null                101     Draft proposal          null     
null             null                102     Set up compute cluster  null     
2                Migration to Cloud  103     Migrate database        2        
2                Migration to Cloud  104     Update documentation    2

Now the tasks that pointed to project 1 remain, but their ProjectId is now NULL.

Or try updating project 2 to a new ID:

UPDATE Projects SET ProjectId = 5 WHERE ProjectId = 2;

And run the query again:

SELECT 
    p.ProjectId AS ParentProjectId,
    p.ProjectName,
    t.TaskId,
    t.TaskName,
    t.ProjectId
FROM Tasks t
LEFT JOIN Projects p ON t.ProjectId = p.ProjectId
ORDER BY t.TaskId;

Result:

ParentProjectId  ProjectName  TaskId  TaskName                ProjectId
---------------  -----------  ------  ----------------------  ---------
null             null         101     Draft proposal          null     
null             null         102     Set up compute cluster  null     
null             null         103     Migrate database        null     
null             null         104     Update documentation    null

The tasks still remain, but they no longer belong to a project.

Conclusion

Knowing which foreign keys use SET NULL helps you avoid surprises. If you’re troubleshooting why child rows suddenly lost their foreign key value, this is a likely culprit. It also matters when designing reports or analytics. If you join on a foreign key without accounting for nulls, you might quietly lose rows.

The query above gives you a quick way to audit your schema so you can make intentional design decisions. Sometimes SET NULL is exactly what you want. Other times, you might prefer a cascade or no action at all. The important part is being able to find and understand what’s currently in place.

Querying Foreign Keys with SET NULL Actions

Example

Conclusion

Querying Foreign Keys with `SET NULL` Actions