Re: uniquely indexing Celko's nested set model

On Oct 19, 2007, at 16:42 , Richard Broersma Jr wrote:

> Is it possible to constraint both the LEFT and RIGHT fields of a
> record to use the same index? I am looking for a way to ensure for
> all LEFTs and RIGHTs in a table, that is it is impossible for any
> LEFT or RIGHT to have to same value.

You can define a check constraint to handle this:

CREATE OR REPLACE FUNCTION strict_nested_set_node_check(INTEGER,
INTEGER)
RETURNS BOOLEAN
STRICT IMMUTABLE SECURITY DEFINER
LANGUAGE SQL AS $_$
SELECT ((abs($1) < abs($2))
AND ($2 - $1 - 1) % 2 = 0)
$_$;
COMMENT ON FUNCTION strict_nested_set_node_check(INTEGER, INTEGER) IS
'Convenience function to encapsulate the check conditions for the
lower and '
' upper bounds (often called ''left'' and ''right'') for strict
nested set '
'implementations.';

CREATE TABLE nodes
(
node_id SERIAL PRIMARY KEY
, node_lower INTEGER NOT NULL
, node_upper INTEGER NOT NULL
, UNIQUE (query_plan_id, node_lower)
, UNIQUE (query_plan_id, node_upper)
, CHECK (strict_nested_set_node_check(node_lower, node_upper))
);

To actually guarantee that each lower and upper value is only used
once, I think you'd need to write a trigger that checks that each
value is only used once. I haven't used such trigger when I've used
nested sets, however. If you handle your table modifications through
functions and test your functions thoroughly, you can be pretty sure
that your table updates aren't going to cause any duplication of this
time. Then again, maybe I should add the trigger to be on the safe
side :)

Michael Glaesemann
grzm seespotcode net