Rick Debay wrote:

>>>Is there a reason that CHECK constraints run in the user context?
>>
> ...Bonus points for including relevant
> portions of the SQL specification, and then shredding the logic behind
> said spec(s).
>

The only shredding to be done is in the interpretation of the spec, I
think. If constraints are restricted to the current row and constant
values, the transaction context is irrelevant. If, on the other hand,
a constraint can reference other rows in the current table or rows from
other tables, the transaction context is critical. My guess is that
the designer of the constraint implementation decided that the standard
restricted constraints to the current row, but chose not to enforce
that interpretation in the code. So, if you write a non-standard
compliant constraint you get unreliable behavior rather than an error
when you define the constraint. The decision would have been based on
SQL-92. More recent versions of the spec seem to allow references
outside the current row - a useful feature, one might think.

Regards,

Ann