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@Yahoo.com #