SQL Server allows you to define cascading ... 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.

A foreign key is a relationship ... the date range for when a particular set of values was active. And because of this temporality, the “Start Date” column becomes part of the table’s primary key, ...