Subject Re: Transactions and triggers
Author Adam
The code that enforces them is in
> the index handling and is outside the current transaction context.
> So this problem is (somewhat) tied into the issue of creating foreign
> key constraints that don't require indexes. It's a major rethinking
> of constraints.

I suspected as much. That is why I avoid thinking of it in terms of
correct or incorrect, but rather as 'the way it behaves'. No-one I
have spoken to thinks that it is ideal, so I was sure if it was a 2
minute fix it would have been done a long time ago.

Everyone at some point has their hands tied by legacy implementation
decisions. There is a workaround using a trigger to insert a record
into another table with a unique constraint (suggested by Set) which
would allow this logic to work.