How to Identify Foreign Keys with Cascade Action in SQL Server

by

When working with relational databases in SQL Server, foreign keys help maintain referential integrity between tables. Sometimes these constraints are defined with actions such as CASCADE, which automatically propagates updates or deletions from a parent table to related rows in a child table.

While this functionality can be useful, it can also introduce unexpected side effects if you are not aware of where it is enabled. Knowing how to identify foreign keys with cascade actions is an important part of understanding data dependencies, troubleshooting issues, and ensuring database operations behave as intended.

Fortunately, SQL Server provides us with an easy way to find out which foreign keys have got the CASCADE option enabled.

Example Query

The following query checks to see which foreign keys have cascading delete or cascading update enabled:

SELECT
    fk.name AS ForeignKeyName,
    tp.name AS ParentTable,
    tc.name AS ChildTable,
    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.referenced_object_id = tp.object_id
JOIN sys.tables tc ON fk.parent_object_id = tc.object_id
WHERE fk.delete_referential_action = 1
OR fk.update_referential_action = 1;

Sample output:

ForeignKeyName                  ParentTable     ChildTable      DeleteAction  UpdateAction
------------------------------  --------------  --------------  ------------  ------------
FK__Feedback__Produc__10766AC2  Products        Feedback        NO_ACTION     CASCADE     
FK_PlayerSessions_Games         Games           PlayerSessions  CASCADE       NO_ACTION   
FK_Achievements_PlayerSessions  PlayerSessions  Achievements    CASCADE       NO_ACTION

Here, we narrowed the results down to those foreign keys that have ON DELETE CASCADE or ON UPDATE CASCADE enabled. We determined this by checking the delete_referential_action and update_referential_action columns in the foreign_keys view. A value of 1 means that it will cascade on the given action. This view also has delete_referential_action_desc and update_referential_action_desc columns, which contain a description of the action (in this case CASCADE).

If you’re only interested in certain actions (like just deletes or just updates), then you can modify the WHERE clause and SELECT list accordingly.