You can create a function and call it from a trigger on that column insert event Thanks Dinesh Pandey
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert D. Kennedy Sent: Sunday, September 04, 2005 12:20 AM To: pgsql-sql@postgresql.org Cc: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match