Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    43

    Cascading Parent Deletes vs. Not Cascading

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2006
    Posts
    43
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vk101
    However, the questions in my original post are still relevant when I use your technique.
    please explain why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2006
    Posts
    43
    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.

  7. #7
    Join Date
    Sep 2006
    Posts
    20

    Angry hi

    Quote Originally Posted by r937
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •