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@fusion-advertising.co.uk>