If you delete a parent record that has children fields with foreign keys pointing to it, I see some problems. (When I say delete, I mean a logical delete where the record is not removed, but is moved to a mirroring table of deleted records.) These problems seem common enough that many people must have them, so I imagine you might have some best practices on this issue.

What confuses me is there are cases when the children must remain in spite of the parent being gone, and other cases where the parent's deletion cascades to the child. (Child is just any record with a foreign key to another record, for the context of this discussion.)

In the case where the children must be delete-cascaded with their parent, how would you do this? Cascading deletes is a standard concept, but what about cascading "logical deletes"...how would this work? Would it just be a really ugly recursive query? (Recursive because children have other children which have other children.)

In the case where the children must remain in spite of their parent being deleted, how would that work? You may be thinking, "Why would you have a child that remains when the parent is gone? You're doing something wrong." Here's an example: A Member can create many Product Types. (i.e. Product Type has a foreign key to Member.) The Member is logically deleted (moved to a mirroring table), but you don't want to remove all Product Types that were defined by that Member! You also don't want to lose the information of which Member defined a given Product Type, even if that Member has now been deleted. How could this be accomplished?!

Your insights on this issue are highly valued. Thanks.

Moving the deleted parent record screws up relational integrity as much as deleting it. You end up with child records containing a single foreign key to multiple parent tables.

Blech.

Instead of deleting or moving the parent record, just add a status column to the table and mark it "Deleted", or "Expired", or whatever term you want.

for cascading "logical deletes," i would declare the parent primary key as a composite of id and status, then just update the parent status, and allow ON UPDATE CASCADE to take care of the children

when children must remain after the parent is deleted, ON DELETE SET NULL is what you are looking for

Edit: Started writing and posted before reading r937's post directly above.

That's a good point, about a foreign key to multiple tables. (I also just realized that the mirroring table would need a different primary key, because the primary key in the original table would be repeated each time the original record is updated. And it'd be convoluted to ensure not recycling a primary key in a logically deleted record for new records in the original table.)

So your suggestion solves several problems. However, the questions in my original post are still relevant when I use your technique.

Thanks.

please explain why

r937, thanks for your response. That's an interesting technique. ON UPDATE CASCADE looks like a good solution and seems to take care of the recursive issue.

For ON DELETE SET NULL, how would I be able to retain the information of which Member defined a given Product Type if that Product Type's compound foreign key of (ID, Status) is now set to (NULL, NULL)?

Also, wouldn't ON DELETE SET NULL only work when a record is deleted? Would that work when I'm doing "logical deletes"? Did you happen to mean ON UPDATE SET NULL? I guess not, because that would set it to NULL even if you updated to something other than an "Expired" status...

Thanks for your reponse.

i suppose it is not possible in db2 (without manually written triggers). or is it?
if not, why would that be? what is the reason why they havent implemented on update cascade in db2? is it coz of any particular reason as such?