MySQL provides cascading updates / deletes with the relationships, but I tend not to use them, specifically because I want to control just how far these updates and deletes cascade!  But, given a situation where I have a design similar to this:


I would like to be able to remove a single Foo, without having to first remove all the associated data from the other 3 tables.  Or, I know the ID of the Foo I want to remove, so instead of running multiple queries to find the associated rows, lets just knock it out with a single, multi-table delete!

  db.zap as z,
  db.baz as bz, as z, as f
  db.zap as b,
  db.baz as bz, as z, as f
  b.baz_id = AND
  b.zap_id = AND
  bz.foo_id = AND
  z.foo_id = AND = ?

This will then remove the rows associated with the single Foo record I’ve referenced, in one fell swoop.

Leave a Reply