> > I doubt it. I'd simply add another table and let Firebird handle
> duplicates:
>
> 1- Let's go back to a more primitive case:
>
> CREATE TABLE TABLE1 (
> PK BIGINT PRIMARY KEY,
> F1 INTEGER NOT NULL UNIQUE);
>
> 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 fail although transaction2 is not supposed to see
> the record of transaction1 since transaction1 is not committed).
>
> 2- Let's try a different constraint:
>
> 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.

Adam