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.
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.
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...
and allow ON UPDATE CASCADE to take care of the children
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?