From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: rdkennedy@acm.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Constraint CHECK(SELECT()) alternative?
Date: Mon, 5 Sep 2005 21:25:08 -0700 (PDT)
Message-ID: <20050905211839.U4939@megazone.bigpanda.com> (raw)
In-Reply-To: <4319F05A.30300@fnal.gov>
References: <4319F05A.30300@fnal.gov>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, rdkennedy@acm.org
Subject: Re: Table Constraint CHECK(SELECT()) alternative?
In-Reply-To: <20050905211839.U4939@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox