Cascading Soft Delete

views:

519

answers:

+1 Q:

Cascading Soft Delete

SQL has always had a great feature: cascading deletes. You plan it in advance and when it's time to delete something, BAM! No need to worry about all those dependent records.

However, nowadays it's almost taboo to actually DELETE anything. You flag it as deleted and stop showing it. Unfortunately, I haven't been able to find a solid solution to doing this when there are dependent records. I've always manually coded the complicated web of soft deletes.

Is there a better solution out there that I have completely missed?

+6 A:

I hate to say it but triggers are designed specifically for this kind of thing.

(The hate part is because good triggers are very hard to write and , of course , cannot be debugged)

Learning 2009-02-03 09:08:46

Not sure what backend you're talking about, but you could pickup on your "delete flag" changing and cascade the change down using a trigger.

Steven Robbins 2009-02-03 09:09:49

+2 A:

Foreign key constraints can do cascade updates. If you linked your tables on both the key and the delete flag, then when the delete flag in the master table changed, that change would propagate down to the detail table. I haven't tried that, but it should work.

Matt Hamilton 2009-02-03 09:12:59

This is insanely elegant IMO. The only problem with doing it is you can't use a NULL Delete_Date but instead have to use some arbitrary date like '9999-12-31'.

HaxElit 2010-09-01 16:57:34

After thinking a bit more it doesn't work because if you soft delete a dependent record you get a key constraint error because the parent's delete date is different. To good to be true I guess ;)

HaxElit 2010-09-01 17:00:34

+1 A:

I think a benefit of the soft deletes is usually that not every table has a soft-delete flag, so the number of things needed to be cascaded is few. The rows are simply unused in the database, but not orphaned - they are simply only referred to by deleted rows.

Like everything, though, it depends on your model.

Cade Roux 2009-02-03 09:13:29

Hmm, but then you have a database full of rows without seeing immediately which is used - right? You could join on the primary table, but if the cascade goes several levels deep, this could get messy. Or am I missing something?

Piskvor 2009-02-03 11:08:41

It depends on the model. In a relational design, if a deleted flag does not belong in the relation/tuple/table - i.e. it's not an attribute of the key, I would not put one. In a star schema - you would put them only on the core tables.

Cade Roux 2009-02-03 12:28:28

If you give an example of a schema of a subsystem, I will show you which ones where I would put a deleted flag. I would not, for instance ever put them on a many-to-many table unless you are keeping linkage change history, in which case, you would need to add effective dates as well.

Cade Roux 2009-02-03 12:30:00

ansaurus

tags:

views:

answers:

Cascading Soft Delete

related questions