SQL Server - maintain referential integrity without running CASCADE and INSTEAD OF

Question

I have a table (TableB) that has a foreign key relationship with the parent table (TableA).

When I delete a record in table A, I want to maintain referential integrity by deleting all records in table B that reference the deleted record in table A.

Normally I would DELETE CASCADE. However, due to table structure and redundant protections against multiple cascading paths in SQL Server, this is not possible for this particular relationship.

I also cannot use the INSTEAD OF trigger, since table A itself has a foreign key relationship CASCADE on it.

What I'm going to do is change the relationship between TableA and TableB to ON DELETE SET NULL, and then create an AFTER trigger to clear the NULL entries in TableB.

Are there any better ways to solve this scenario?

Answer 1

Can you change another restriction that prevents you from adding it as ON DELETE CASCADE ?

Can you add the DeleteMe column, then type UPDATE A SET DeleteMe = 1 , then use the trigger to delete the rows of table B first and then the requested table. Rows?

Is it possible to split or combine tables (vertically, that is) in some way, separating their mutually exclusive dependencies?