Adam wrote:

>>
>> CREATE TABLE TABLE1 (
>> PK BIGINT PRIMARY KEY,
>> F1 INTEGER NOT NULL CHECK(NOT EXISTS(SELECT PK FROM TABLE1 WHERE
>> F1=F1)));
>>
>> Start transaction1, insert a new record with F1=1 (do not commit)
>> Start transaction2, insert a new record with F1=1. (do not commit)
>> The operation will succeed.
>> Commit both transactions. The check constraint will be ignored.
>>
>> Is this the correct behaviour?
>
> The check clause obeys transaction isolation. Pretty much the only
> things that do not are primary keys, foreign keys and unique
> constraints. To me it would be more logical for allow the check
> constraint to ignore isolation just like other constraints, but it
> doesn't.

It would be more correct as well as more logical, but also a lot more
work given the architecture that was originally set in place to handle
constraints. As you noticed, primary key, unique, and foreign key
constraints are handled differently from others. They are also the
constraints that create indexes. 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.

Regards,


Ann