From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
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 11:55:16 |
Message-ID: | 20030626115516.GC6568@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Benjamin Jury | 2003-06-26 11:58:59 | FW: Foreign keys |
Previous Message | Bruno Wolff III | 2003-06-26 11:37:52 | Re: Question regarding performance (large objects involved) |