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

You may want to know that unique indexes in MGA
CAN contain duplicates.
The logic behind checking duplicates does not only look
at values, but also checks states of transactions the values
were created in. So simply allowing dirty reads in check
constraints is NOT enough to emulate that logic.

> The constraints don't ensure the committed data is correct! 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 is documented behaviour.

Ivan

----- Original Message -----
From: "johnsparrowuk" <jsparrow@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, June 14, 2004 9:18 PM
Subject: [firebird-support] Re: Checking periods don't overlap


> 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! 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!
>
> John