Re: cascading backwards

On Thu, 2003-05-08 at 16:55, Paul Makepeace wrote:
> If a table A contains a foreign key to table B is it possible to have
> a DELETE remove the targetted row in B when there are no longer any
> other references in A to it? Either with SQL or using some other
> PostgreSQL facility.
>
> As an example,
>
> Body:
> body_id data
> 1       "some news"
>
> Page:
> page_id body_id name
> 10      1       "news"
> 11      1       "recent news"
>
> If page_id=10 is deleted, nothing extra happens. If page_id=11 is
> deleted, body_id=1 goes with it.

How about an AFTER DELETE trigger on Page?

pseudo-code:
  tmp = old.body_id;
  If count(*) = 0 where body_id=tmp then
    delete from Body where body_id=tmp;
  end if;

--
+---------------------------------------------------------------+
| Ron Johnson, Jr.        mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA      http://members.cox.net/ron.l.johnson  |
|                                                               |
| The purpose of the military isn't to pay your college tuition |
| or give you a little extra income; it's to "kill people and   |
| break things".  Surprisingly, not everyone understands that.  |
+---------------------------------------------------------------+