Temporary enabling of ON DELETE CASCADE

Question

I'm using SQL Server 2005.

Our application almost never deletes without it being a logical delete and therefore we have no need for cascading deletes.

In fact, its quite a comfort knowing that the foreign key contraints give us some protection against an accidental delete statement.

However, very occasionally I need to delete a top level table and all of its children. At the moment I do this with multiple DELETE statements in the write order and it becomes a very large, complex and impossible to keep up to date script.

I'm wondering if there is a way of automatically turning cascading deletes on for all foreign keys in the database, performing my top level delete, and then turning them all back on again?

Answer 1

How about writing pair of scripts -- one you manually run when you're about to delete records that enables the on delete cascade appropriately for the constraints, and then have another one other that you run once finished deleting to disable them and get things back to normal?

Answer 2

For another question here, I wrote a script which should generate the deletes automatically.

Answer 3

Just a thought: use an INSTEAD OF TRIGGER on the parent table, for deletes, so that all the child-record-delete logic is in one place