Sql-server – enforce cascade delete in a delete from statement

cascadedeletesql servert-sql

I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case.

However, is there a way to write a DELETE FROM query so that for every constraint that would normally block the removal of record/s, cascade delete will be used?

Best Answer

As you probably know, the way to do this is a foreign key whith ON DELETE CASCADE. But you don't want this, so I see 2 options (there may be more as I'm not a SQL Server expert):

  • Make this in the hard way: write multi-table DELETE statements. As you can see in the documentation, it is very similar to a JOIN.
  • If you want to do this easy (for example to hide information about database structure to developers), you'll need a BEFORE DELETE trigger. This trigger can delete rows from other tables. I understand that you want this to happen only in certain cases; this is doable by setting (before your query) and checking (in the trigger) SESSION_CONTEXT. Rows will only be deleted if the variable is set. It should be unset at the end of the trigger itself, so developers won't need to take care of this (they could forget). Since the trigger will delete relevant rows before the query is executed, the constraint will not make it fail.
  • A very similar approach, but maybe cleaner, would be writing a stored procedure.
Related Question