ON DELETE CASCADE

[J.C. SolvoTerra]

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?

[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

[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

[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