On Oct 19, 2007, at 7:37 PM, Scott Marlowe wrote:
On 10/19/07, Richard Broersma Jr <rabroersma@xxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
