Member Avatar for J.C. SolvoTerra

2680a256fcbf10c900499c36825b3e55

In the above model I have an Images table which holds all the filenames relevant to the image. I have two relationship tables (Image_Has_People and Image_Has_Event). Both of these tables have an FK ImageID linked to the Image table's PK idImage. I'm wondering, if I set both relationship table's ImageID FK to On Delete Cascade, If I delete an entry in one of the relationship tables will that not also delete the image table entry too?

What would I need to do in order that when I delete an Image entry from the Image table it also removes relevant entries in the two relational tables but not vice-versa?

  • 2 Contributors
  • 3 Replies
  • 326 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by David_50
Member Avatar for David_50

Delete cascade prevents orphans, not childless parents. If you delete the parent, all children get deleted. Deleting children is just life. You can develop a trigger that for child deletes, deletes the parent if no other children exist. I am not a fan of triggers and cascades, preferring trusted code and relational integrity checks, so the church overhead is minimal. http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/X15877.htm

commented: Understood, Thanks +3
Member Avatar for J.C. SolvoTerra

So am I right in assuming, you preferance is:

  • Delete Image Entry from Images
  • Check Image_Has_People for entries with the deleted idImage
    -- Delete Relevant Images
  • Check Image_Has_Event for entries with the deleted idImage
    -- Delete Relevant Images

or even

  • Check Image_Has_People for entries with the proposed deleted idImage
    -- Delete Relevant Images
  • Check Image_Has_Event for entries with the proposed deleted idImage
    -- Delete Relevant Images
  • Delete Image Entry from Images
Member Avatar for David_50

You can go farther, and have a purge process remove orphans in batch, in bulk, leaving the interactive operations less loaded and reducing the total number of churn instructions.

However, the clean freaks never like that. :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.