4

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

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.

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.

I also cannot use an INSTEAD OF trigger as TableA itself has a CASCADE foreign key relationship on it.

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.

Are there better ways of dealing with this scenario?

Brett Postin
  • 11,215
  • 10
  • 60
  • 95
  • Can you explain what you mean by "due to the table structure and the overprotective safeguards against multiple cascade paths" and maybe include your table definitions? – JohnFx Jan 27 '12 at 17:22
  • I understand that people's immediate thoughts would be to look at the design, however I don't think it is particularly unreasonable and in any case restructuring is not an option. So I'm just looking for suggestions on how to tackle this particular problem without a re-design. I think this answer sums up my situation and thoughts on SQL Servers safeguards [cascading deletes causing multiple cascade paths](http://stackoverflow.com/a/5407007/295813) – Brett Postin Jan 27 '12 at 17:29
  • 1
    Just curious, what real-world limitations prevent you from issuing a delete on the child table followed by a delete on the parent, with both perhaps wrapped in a transaction? – Philip Kelley Jan 27 '12 at 17:34
  • 1
    It sounds like you are a couple of layers deep on complexity with this cascading problem. I'd really suggest reworking your tables to take advantage of the built in SQL functionality. I just don't think you are going to home-brew a better answer than is built into the platform. – JohnFx Jan 27 '12 at 17:34
  • @PhilipKelley There are no real limitations for doing that, and I have already considered that as an alternative solution. However if I can let the database enforce RI in cases where someone may modify the data directly (unlikely but possible) that would be preferable. – Brett Postin Jan 27 '12 at 17:38
  • I'm not seeing automatically why the fact that TableA itself has a CASCADE foreign key relationship on it prevents you from using an AFTER trigger. I'm sure I will smack my forehead when I figure it out. Could you explain? – ErikE Feb 06 '12 at 00:10
  • @ErikE The relationship between TableB and TableA has referential integrity enforced. So when a record from TableA is deleted, the records in TableB that reference that deleted record violate the integrity before the AFTER trigger can be invoked. – Brett Postin Feb 06 '12 at 09:24
  • @Poz Like I said I knew I would hit myself... yes of course the delete must not be an after trigger or it isn't happening at the right time in the process... – ErikE Feb 06 '12 at 17:20
  • Does this still work? Using SQL Server 2016 I find that it prevents `on delete set null` in the same circumstances that it prevents `on delete cascade`. – Neutrino Feb 20 '20 at 16:46
  • Just realized, my situation is slightly different in that I have 3 related tables, TableB references TabeA, TableC references TableA, and TableC references TableB. Tables B and C cascade delete from TableA, but I also need TableC to delete when TableB is deleted from. It seems SQL Server is the only database that can't handle this. – Neutrino Feb 21 '20 at 14:21

1 Answers1

0

Can you change the other constraint that is preventing you from adding this one as ON DELETE CASCADE?

Can you add a DeleteMe column, then issue an UPDATE A SET DeleteMe = 1, then use an after trigger to delete Table B rows first, then the requested table A rows?

Can you split or merge tables (vertically, that is) in some way that separates their mutually exclusive dependencies?

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • 1. I could break the cascade path elsewhere. However this relationship would be my ideal place as it is of least importance. 2. This would work, but I think I'd prefer my similar SET NULL solution. 3. I don't think this would eliminate the multiple cascade paths. – Brett Postin Feb 06 '12 at 09:32
  • The 3. item depends on how you split the tables and the exact nature of your multiple constraints to the same column. So given what you've said it sounds like your idea about update to NULL would work. +1 for a good question. – ErikE Feb 06 '12 at 17:21