Re: Foreign keys

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.