From: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
---|---|
To: | Matt Browne <mattb(at)fusion-advertising(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Foreign keys |
Date: | 2003-06-26 12:03:22 |
Message-ID: | 3EFAE10A.2020608@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 #
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Browne | 2003-06-26 12:34:49 | Re: Foreign keys |
Previous Message | Benjamin Jury | 2003-06-26 11:58:59 | FW: Foreign keys |