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(dot)l(dot)johnson(at)cox(dot)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. |
+---------------------------------------------------------------+