Reply-To: <dpandey@secf.com>
From: "Dinesh Pandey" <dpandey@secf.com>
To: <rdkennedy@acm.org>, <pgsql-sql@postgresql.org>
Subject: Re: Table Constraint CHECK(SELECT()) alternative?
Date: Tue, 6 Sep 2005 09:48:14 +0530
Organization: Second Foundation India P Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <4319F05A.30300@fnal.gov>
Thread-Index: AcWymWvng0Y+zkM4RrCsk7jx8IbAeQAAE3xA
Message-Id: <20050906042222.9C919D7FBD@svr1.postgresql.org>

You can create a function and call it from a trigger on that column insert
event
 
Thanks
Dinesh Pandey

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Robert D. Kennedy
Sent: Sunday, September 04, 2005 12:20 AM
To: pgsql-sql@postgresql.org
Cc: rdkennedy@acm.org
Subject: [SQL] Table Constraint CHECK(SELECT()) alternative?

Hello,

	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?

	I am implementing a basic tree of nodes. There are leafs and
directories... node types. Each node in a directory has a unique name, and
the node type ids point to a table with node type names. Each node points to
a valid node (its parent). There is more... but now to my question. I wish
to constrain parent nodes to only be directories type nodes. Leafs cannot be
parents.

                                        Table "public.raw_pnfs_nodes"
     Column     |         Type          |
Modifiers
----------------+-----------------------+-----------------------------------
----------------------------------
 node_id        | integer               | not null default
nextval('public.raw_pnfs_nodes_node_id_seq'::text)
 parent_node_id | integer               | not null
 node_name      | character varying(80) | not null
 node_type_id   | smallint              | not null
Indexes:
    "raw_pnfs_nodes_pkey" primary key, btree (node_id)
    "raw_pnfs_nodes_node_name_key" unique, btree (node_name, parent_node_id)
Foreign-key constraints:
    "parent_refersto_node" FOREIGN KEY (parent_node_id) REFERENCES
raw_pnfs_nodes(node_id) ON UPDATE RESTRICT ON DELETE RESTRICT
    "raw_pnfs_nodes" FOREIGN KEY (node_type_id) REFERENCES
node_types(node_type_id) ON UPDATE RESTRICT ON DELETE RESTRICT


What I WANT to write is the table constraint:

  CONSTRAINT parents_are_dirs_check CHECK( (SELECT A.node_type_id FROM
raw_pnfs_nodes A, raw_pnfs_nodes B WHERE A.node_id = B.parent_node_id) = 1)

where "1" is the id for directory node types (ok, this is not polished yet)
Hopefully this illustrates the idea. The node type of a node that is a
parent to another node must be the directory node type. Is there another way
to express this in SQL? I would like to avoid putting this into the business
logic layer.

Thanks much,
RDK