Re: Checking number of entries

On Sat, 30 Sep 2000, Philip Warner wrote:

> At 11:45 29/09/00 -0700, Stephan Szabo wrote:
> >On Fri, 29 Sep 2000, Philip Warner wrote:
> >
> >> At 01:53 29/09/00 -0400, Tom Lane wrote:
> >> >
> >> >The canonical example is a check like
> >> >    CHECK (mycol > (SELECT max(othertab.othercol) FROM othertab))
> >> >declared as a constraint on mytab.
> >>
> >> I would argue that a CHECK constraint only applies to the table on which it
> >> is defined - basically a contract to ensure that certain conditions apply
> >> to changes on that table.
> >
> >The problem is that the spec seems to say that constraints
> >are checked either at end of transaction or statement
> >and doesn't seem to say anything about limiting which statements
>
> You're quite right; my (weak) argument was that since we are already
> breaking the spec by allowing arbitrary functions, we should either
> strengthen our conformance to the spec (by disallowing arbitrary
> functions), or allow statements inside CHECK constraints (since they are
> there already via functions), and ignore the consequences in other tables
> (as we currently do with functions).

I'd be worried about doing the latter only because it will definately
cause problems for people who are actually expecting the behavior
to follow the spec behavior, plus it would make it much harder for
us to move to the correct behavior later.
And, the former can still be basically done via triggers whether or not we
were to limit them in check constraints that might be a safer route,
except for the fact it breaks compatibility with existing versions.
Finally, there is the possibility of having the class of constraints
checked after any sql statement which is going to suck for performance
but actually probably be "correct" in a strict spec sense.

How do other systems (Oracle, etc...) handle the subselect and arbitrary
function cases?

And I haven't given much thought to it really, but has anyone thought
about deferred check constraints?