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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
