title: Reverse foreign key cascading (or how to collect database garbage)
body: <p>This is similar to <a href="https://stackoverflow.com/questions/21540007/reverse-foreign-key-constraint">this question</a> albeit with a twist (namely, I need what basically is reference counting behavior here).</p>
  
  <p>We have several tables (Foo, Bar, Baz) where the things stored in them all reference zero or more of another kind of thing (a Blah).  We sometimes make new Blahs and attach them to a Foo, a Bar, or a Baz; however, we don't edit existing Blahs as they may be pointed-to by multiple things at once.  Currently, we have a batch process that sweeps through all the Foos, Bars, and Bazes in the database, marking Blahs as used, and then deleting all unmarked Blahs, but this is quite costly, so we're looking for a way to do it online, ideally through the database itself.  We can research trigger-based approaches, but would rather treat those as a last resort.</p>
  
  <p>Specifically, referring to the schema and data in <a href="http://sqlfiddle.com/#!4/21063" rel="nofollow noreferrer">this SQLFiddle</a>:</p>
  
  <ul>
  <li>If I insert a new Bar 'some_bar' and have it point at a new Blah with PK 'r', only the already-orphaned Blah 'x' should be deleted.</li>
  <li>If I delete 'a_foo', Blahs 'a' and 'x' should be the only ones that are deleted -- 'b' and 'c' in particular need to stay put</li>
  <li>If I delete 'another_foo', Blahs 'x' and 'y' need to be deleted</li>
  <li>If I delete 'a_bar', only Blah 'x' can be deleted -- Blah 'c' in particular needs to stay</li>
  <li>If I delete 'another_bar', again, only Blah 'x' can be deleted -- Blah 'q' needs to stick around</li>
  <li>If I delete 'a_baz', all of Blahs 'p', 'x', and 'z' need to be deleted</li>
  <li>If I delete 'another_baz', only Blah 'x' can be deleted -- Blahs 'b' and 'q' need to stay around</li>
  </ul>
  
  <p>Am I stuck with triggers or the existing mark-sweep batch processing for this job, or is there a better way to do it?</p>