Message-ID: <4530EB84.4050904@logix-tt.com>
Date: Sat, 14 Oct 2006 15:52:04 +0200
From: Markus Schaber <schabi@logix-tt.com>
Reply-To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Thunderbird 1.5.0.7 (X11/20060927)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Subject: Re: Foreign key reference counting strategy?
References: <1160828741.28838.9.camel@panoramix>
In-Reply-To: <1160828741.28838.9.camel@panoramix>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Hi, Joost,

Joost Kraaijeveld wrote:

> Is there a strategy to implement reference counting for foreign keys so
> that if the last reference to the key is deleted, the record is deleted
> also?

Create an "after delete" trigger on the referencing table that checks
whether there still are records with the same key (IF EXISTS()), and
deletes the referenced row otherwise.

It won't hurt to have an index on the referencing column, for speed reasons.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org