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
