> > > 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.

Actually, there's more going on.

It's is documented behaviour that a CHECK constraint will only
check upon INSERT or UPDATE.

The SQL Standard also defines, I believe, "ASSERTIONS", which
are also checked after a DELETE or pretty much any change.

This allows for more checking inside your database.

That being said, yes, having CHECKs in the "system" transaction
would make sense to me as well.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com