Foreign keys

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?

Thanks very much for any assistance!

--
Matt Browne <mattb(at)fusion-advertising(dot)co(dot)uk>

On Thu, Jun 26, 2003 at 12:00:07 +0100,
Matt Browne <mattb(at)fusion-advertising(dot)co(dot)uk> wrote:
>
> 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?

You need to write custom triggers. Any time you delete an address id from
a referencing table you need to check if the referenced id no longer
has any references. Any time you insert (or update the primary key)
a record in the address table you need to check that it is referenced.
You will want this latter check to be deferable.

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 #

> Matt Browne wrote:

> 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_addresses [table]
> supplier_addresses [table]
> address [table]

I've stumbled late onto this thread so I may have missed something
important. However, I need to ask: are you keeping the same address in two
different tables? That is, are customer_addresses records duplicated in
address, and the same for supplier_addresses?

If so, you've violated a normalization rule in your schema and it's no
wonder that you can't delete all the addresses you want.

How do you synchronize addresses in multiple tables and, much more
importantly, why do you have multiple records?

In every database I've designed, the address is with the name record. If a
customer, supplier or whatever had multiple addresses, then I'd put _all_
addresses in a single table and reference each one to the name record in the
appropriate table.

What have I missed here?

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard&#64;appl-ecosys.com
http://www.appl-ecosys.com/

On Thursday 26 Jun 2003 1:40 pm, Rich Shepard wrote:
> > Matt Browne wrote:
> >
> > 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_addresses [table]
> > supplier_addresses [table]
> > address [table]
>
> I've stumbled late onto this thread so I may have missed something
> important. However, I need to ask: are you keeping the same address in two
> different tables? That is, are customer_addresses records duplicated in
> address, and the same for supplier_addresses?
>
> If so, you've violated a normalization rule in your schema and it's no
> wonder that you can't delete all the addresses you want.

It looks like he has a centralised "address" table with "customer_addresses"
linking "customer" to "address". Likewise for "supplier".

His problem was he wanted to remove address details when nothing referred to
them any more.

--
Richard Huxton

On Thu, Jun 26, 2003 at 02:12:22PM +0100, Richard Huxton wrote:

<snip>

> It looks like he has a centralised "address" table with "customer_addresses"
> linking "customer" to "address". Likewise for "supplier".
>
> His problem was he wanted to remove address details when nothing referred to
> them any more.

I'd run an garbage collection over the db from time to time.
It should be an easy test to select (or delete) all address rows which
ID doesnt exist anywhere else.

You can easily put this in an function.

cu

--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481 smsgate: sms(dot)weigelt(at)metux(dot)de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/