You seems to believe that e.g. unique constraint could be written
as check constraint that can read uncommitted data ? NO way.
Dirty-read would read just last (be it committed or uncommitted) record version,
whereas unique constraint will check whole chain of versions.

Example:
transaction A deletes row,
transaction B tries to insert new row with the same pk, and it will
succeed, since its dirty-reading check constraint thinks the row is deleted
transaction A rollsback, thus you have duplicate keys.

And even if your check constraint could read all versions of single record
(which is nonsense just by itself), imagine this example:

transaction A inserts row (e.g. 10), starts savepoint, and update that row (e.g. to 20)
transaction B now can insert value 10, because its dirty-reading-and-all-versions-seeing
check constraint will not find value 10 in ANY existing version !
transaction A just rollback to its savepoint, and you have duplicates again.


Ivan

> From: "johnsparrowuk" <jsparrow@...>
> I disagree strongly. inter-row dependances are *not* necessarily a
> sign of poor design. PK's / FK's are an example. You *cannot* insert
> two rows with a PK of 1, that's inter-row dependancy.
>
> I am only suggesting dirty-read be used for check constraints (and
> possibly read-only exception-raising triggers). I don't see how this
> breaks atomicity any more that the operation of Unique Indexes
> (including PK's) which do this anyway.
>
> A unique index can see values in other transactions that havent been
> committed. Period.
>
> According to the quote from Ann (peace be upon her, grin) she also
> believes check constraints should operate in the system transaction
> (is my jargon right? Basically exhibit consistent behaviour with
> indexes, yeah?)
>
> Without that, there is little point in having a select check
> constraint. There is so much it could miss!
>
> There seems no logical reason for having this inconsistency between
> unique indexes and check constraints.