Date: Mon, 5 Sep 2005 21:25:08 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: rdkennedy@acm.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Constraint CHECK(SELECT()) alternative?
In-Reply-To: <4319F05A.30300@fnal.gov>
Message-ID: <20050905211839.U4939@megazone.bigpanda.com>
References: <4319F05A.30300@fnal.gov>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.