title: SQL Server - Maintain Referential Integrity without CASCADE and INSTEAD OF trigger
body: <p>I have a table (TableB) that has a foreign key relationship with a parent table (TableA).</p>
  
  <p>When I delete a record in Table A, I want to preserve referential integrity by deleting all records in TableB that reference the deleted record in TableA.</p>
  
  <p>Normally I would ON DELETE CASCADE. However due to the table structure and the overprotective safeguards against multiple cascade paths in SQL Server, this is not possible for this specific relationship.</p>
  
  <p>I also cannot use an INSTEAD OF trigger as TableA itself has a CASCADE foreign key relationship on it.</p>
  
  <p>What I'm thinking of doing is changing the relationship between TableA and TableB to ON DELETE SET NULL, then creating an AFTER trigger to cleanup the NULL records in TableB.</p>
  
  <p>Are there better ways of dealing with this scenario?</p>