At 07:42 AM 3/10/2004 +0200, you wrote:

>Hi
>
> > Yes - the check is done before the insert. It should succeed with this
>script and fail next time you insert any *value* at all into id.
>
>I would consider this a bug.
>What is the sense of having CHECK-constraints when I can create data, which
>fails that check?

With Roman's example, it doesn't fail the check that's defined. The
incoming value isn't written anywhere yet, so how could it possibly
participate in the Max() aggregation? At the point the check is done, the
check constraint is entirely met.

>Exspecially since vialoations are thrown at points, where the data is
>already invalid.
>I expected it to fail on insert to allow exception-handlers to take care of
>it or at least to auto-rollback these changes.
>How shall I know, what to correct, if I get that error on the next
>(unrelated) insert?

Precisely. Don't write check constraints with logic that doesn't provide
the check you require. In simple terms: garbage in, garbage out.

I could go on to say NEVER define a check constraint based an
aggregation. Think about it a little....

./heLen