Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted.

By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.

SQL Server allows you to define cascading referential integrity constraints. These actions have a trickle-down or cascading effect, sometimes affecting several tables that were related to the primary key table. Let's look at how these constraints are defined, and some situations where you can use them.

The previous script introduces the cascading referential integrity options. In the first statement, I use the ON UPDATE SET NULL option; 

in the second statement, I use the ON DELETE CASCADE ON UPDATE CASCADE option. Here's an overview of what these constraints mean.

SET NULL

If a delete statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value NULL after the primary key record has been updated. The foreign key columns affected must allow NULL values.

CASCADE

If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.

SET DEFAULT

All the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns in the related table must have default constraints defined on them.

NO ACTION

This is the default action. This specifies that if an update or delete statement affects rows in foreign key tables, the action will be denied and rolled back. An error message will be raised.

Ref: 

https://www.techrepublic.com/blog/the-enterprise-cloud/defining-cascading-referential-integrity-constraints-in-sql-server/

https://www.c-sharpcorner.com/article/cascading-referential-integrity/

https://dotnettutorials.net/lesson/cascading-referential-integrity-constraints-sql-server/