Re: uniquely indexing Celko's nested set model

On Oct 19, 2007, at 7:37 PM, Scott Marlowe wrote:

> On 10/19/07, Richard Broersma Jr <rabroersma@yahoo.com> 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.
>
> a check constraint ought to do it
>
> check (field1<>field2)

That won't catch {1,2} {3,1}.

I don't think there's any way to have an index cover two fields in
that way. The only way I can see to do it with an index would be to
have each row of the OPs mental model to map onto two rows of the
table, along with a boolean saying whether the value was for a "left"
or a "right".

There's probably a much, much more elegant way to do it, but this
might work in an existence proof sort of way:

create table moststuff {
   id integer primary key,
   whatever text
};

create table leftright {
   a integer primary key,
   b integer references moststuff(id),
   lr text unique,
   constraint foo check (b = abs(a))
};

Cheers,
   Steve