Hi John,

> Well, the thing about unique indexes and PK's is that they'll dirty
> read, and see uncommitted conficts. You'll get an exception and know
> something went wrong before you get anywhere near a 'commit'.
>
> The constraints don't ensure the committed data is correct!

Sure they do. That's the whole idea.

>They can
> ensure the record is internally consistent, and (thanks to you
> showing me how!) they can ensure the new record is valid against
> other committed records (as can a trigger for more complex
> situations). But they don't address the problem of other
> *uncommitted* records. Which will of course (usually) be committed
> later.
>
> Take that 'unique integer' check constraint I posted before:
>
> Transaction 1:
> insert into T values (1); /* no check violation */
>
> Transaction 2:
> insert into T values (1); /* can't see above, no violation */
>
> Trans 1:
> commit
>
> Trans 2:
> commit
>
> and you've violated your check constraint without an error!!
>
> Obviously if I wanted unique integers I'd use an index, but for the
> time period thing that's not easy.
>
> Unique Indexes work because they dirty-read. I just want check
> constraints to do the same!

Now, with this:


CREATE TABLE UN_TEST
(
PKCOL INTEGER NOT NULL,
UNIQUE_COL INTEGER NOT NULL,
CONSTRAINT PK_UN_TEST PRIMARY KEY (PKCOL)
);

ALTER TABLE UN_TEST ADD CONSTRAINT C_UN_TEST
CHECK (

not exists (select ut.* from UN_TEST ut where new.UNIQUE_COL = ut.UNIQUE_COL
and ut.PKCOL <> new.PKCOL)

);

Note: the "new.PKCOL" works because it's internally a trigger :-)

And this:
INSERT INTO UN_TEST
( PKCOL
, UNIQUE_COL )
VALUES
( 3
, 3)

And do the same in a second transaction, I get a:
lock conflict on no wait transaction violation of PRIMARY or UNIQUE KEY
constraint "PK_UN_TEST" on table "UN_TEST"

If I commit the first, I cannot commit the second:
Operation violates CHECK constraint C_UN_TEST on view or table UN_TEST


Hmmm wait - now I see what you mean... I can do a "3, 4" and a "4,4" --
that's wrong.

Guess the constraint is wrong then :-)

Or the transaction isolation is ... here, I'm using READ COMMITTED, NO WAIT.

Ann, Helen, can someone help? :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com