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
smime.p7s
Description: S/MIME Cryptographic Signature
