Re: Foreign keys

Matt Browne wrote:
> Hello!
>
> I have a question regarding foreign keys and general garbage collection
> of data... If anyone could provide assistance, it'd be much appreciated!
>
> Basically, we have a fairly complex database, with many tables
> (customers, etc) that need to reference addresses that are contained in
> a generic address table.
>
> So:
> customer [table]
> --------
> id serial
> <other fields>
>
> customer_addresses [table]
> ------------------
> customer_id integer
> address_id integer
>
> supplier [table]
> --------
> id serial
> <other fields>
>
> supplier_addresses [table]
> ------------------
> supplier_id integer
> address_id integer
>
> address [table]
> -------
> id serial
> <other fields>
>
> Other tables also reference records in the address table, using a
> similar sort of scheme.
>
> I have foreign keys set up so that if, for example, a record in customer
> is deleted, the corresponding records in the customer_addresses table
> are also removed. However, I can't find a way of ensuring records in the
> address table are deleted too, given that lots of different tables will
> reference address.id.
>
> What I'd like is for records in the address table to be automatically
> deleted at the end of each transaction if nothing references them any
> more. Is there any way to achieve this?

User defined triggers.

I would set up a separate address-reference-count table, holding the
address_id and a refcount (since this will get updated quite often and
has a smaller footprint this way).

For each reference of address you setup a trigger that increases or
decreases the refcount for the address, and when it drops to zero,
object terminated.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #