Re: Many-to-many with both FK cascading deletes?
From: Clifford Heath <no_at_spam.please.net>
Date: Mon, 21 Jun 2010 17:22:27 +1000
Message-ID: <4c1f1333$0$1028$afc38c87_at_news.optusnet.com.au>
>>>> are there any complications in general with a many-to-many "join table" having cascading deletes on both foreign keys to the interconnected tables? After all, this means that the "link entities" get deleted whoever comes first... could this pose a problem? <<
>> The ANSI/ISO model is that first you mark all the rows in all the
>> tables with a delete flag. If no constraints are violated, then a
>> second pass does the actual removal. If a constaint is violated at any
>> point, then the engine does a rollback and sends an exception.
>>
>> The multiple passes can be expensive, but they are safe.
Date: Mon, 21 Jun 2010 17:22:27 +1000
Message-ID: <4c1f1333$0$1028$afc38c87_at_news.optusnet.com.au>
Erwin wrote:
> On 20 jun, 15:35, -CELKO- <jcelko..._at_earthlink.net> wrote:
>>>> are there any complications in general with a many-to-many "join table" having cascading deletes on both foreign keys to the interconnected tables? After all, this means that the "link entities" get deleted whoever comes first... could this pose a problem? <<
>> The ANSI/ISO model is that first you mark all the rows in all the
>> tables with a delete flag. If no constraints are violated, then a
>> second pass does the actual removal. If a constaint is violated at any
>> point, then the engine does a rollback and sends an exception.
>>
>> The multiple passes can be expensive, but they are safe.
> > Is the SQL standards crew now also prescribing how implementations > should be done ?
It's not unreasonable to describe a transitive behaviour like this using an algorithm as example, i.e, must behave like this algorithm behaves. I doubt they dictate the actual implementation... Received on Mon Jun 21 2010 - 09:22:27 CEST