On 2005-02-28, "Casey T. Deccio" <ctdecci@sandia.gov> wrote:
> However, the example I provided was contrived and was used merely to
> show the discrepancy that I'm finding with using the function as a
> constraint. In the larger example, things are a bit more complex, and
> I've found using such a constraint a better fit for now for the problem
> I'm working with.
>
> That said, I'd like to know why the constraint I provided isn't working
> with the corresponding example.
The CHECK is obviously being evaluated prior to the actual insertion of
the record, whereas the logic of your function clearly expects to be
evaluated after the insertion.
Allowing non-immutable functions in CHECK is probably an error, since it
can lead to tables which can not be dumped+restored (consider in your
example what happens when the constraint becomes false as a result of
deleting a row - at that point, a dump and restore of the table will fail,
since the constraint can not be defined after loading the data if it is
violated by that data).
Using triggers is a more reliable way to do this sort of thing - at least
then it is clear that you are checking the data only at the time of
modification, whereas CHECK constraints are declarative constraints which
are expected to be true at all times.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services