pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Constraint CHECK(SELECT()) alternative?
3+ messages / 3 participants
[nested] [flat]
* Table Constraint CHECK(SELECT()) alternative?
@ 2005-09-03 18:50 Robert D. Kennedy <kennedy@fnal.gov>
0 siblings, 2 replies; 3+ messages in thread
From: Robert D. Kennedy @ 2005-09-03 18:50 UTC (permalink / raw)
To: pgsql-sql; +Cc: rdkennedy@acm.org
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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.7K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Constraint CHECK(SELECT()) alternative?
@ 2005-09-06 04:18 Dinesh Pandey <dpandey@secf.com>
parent: Robert D. Kennedy <kennedy@fnal.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Dinesh Pandey @ 2005-09-06 04:18 UTC (permalink / raw)
To: rdkennedy@acm.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Constraint CHECK(SELECT()) alternative?
@ 2005-09-06 04:25 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Robert D. Kennedy <kennedy@fnal.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2005-09-06 04:25 UTC (permalink / raw)
To: rdkennedy@acm.org; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-06 04:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-03 18:50 Table Constraint CHECK(SELECT()) alternative? Robert D. Kennedy <kennedy@fnal.gov>
2005-09-06 04:18 ` Dinesh Pandey <dpandey@secf.com>
2005-09-06 04:25 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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