Re: Table Constraint CHECK(SELECT()) alternative?

On Sat, 3 Sep 2005, Robert D. Kennedy wrote:

>     I have seen in another thread that sub-queries in a CHECK
> constraint have implementation ramifications that make them awkward to
> implement and support. OK, fair enough, c'est la vie.
>
> ERROR:  cannot use subquery in check constraint
>
> is the result. I have a model which seems to BEG for just such a feature
> though. What alternatives are there to the use of CHECK(SELECT()) in
> this model?

The simplest thing is to place the select into a function and use a check
constraint on that (with appropriate row locks).  However, that won't
prevent removal or change of a referenced row.

You can use triggers to prevent that (in your case to prevent removal or
change of id of a referenced node or the change of type of a referenced
mode away from directory).  At that point, you might be just as well off
using a trigger to do the initial check as well.