Re: Constraint for two fields unique any order

On Wed, 2006-07-19 at 09:01 -0700, MargaretGillon(at)chromalloy(dot)com wrote:
> I have a junction table that is recording relationships between two
> records in another table. Is there a way that I can create a
> constraint so that the values are not repeated in any order? I want to
> make sure that rows such as 2 and 4 in the example below cannot
> happen. This is a very small table that is meta data for an
> application. It is only 41 rows now and probably won't grow beyond 200
> rows. I am on Postgresql ver 7.3.4 .
>
> id fkey1 fkey2
> 1 3 4
> 2 10 4
> 3 2 7
> 4 4 10
> 5 15 8

I can think of two solutions with slightly different semantics.

1) If the directionality of the association is immaterial, then the
easiest approach is to impose the convention that rows always satisfy
fkey1<fkey2 and then create a unique index on (fkey1,fkey2). At a
minimum, you should have a check constraint verify this condition. You
might consider writing a trigger for insert and update to swap fkey1 and
fkey2 when necessary.

For example:
create table jx1 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null,
constraint jx1_invalid_key_order check (fkey1<fkey2),
constraint jx1_unique_association unique (fkey1,fkey2)
);

2) If you care about directionality and really seek to preclude
symmetric relationships (as in a family tree), then create a unique
index on the reordered pairs, like this:

create table jx2 (
id serial primary key,
fkey1 integer not null,
fkey2 integer not null
);
create or replace function jx_reorder(integer,integer) returns text
strict immutable language sql as
'SELECT CASE WHEN $1<$2 THEN $1||''-''||$2 ELSE $2||''-''||$1 END';
create unique index jx2_no_symmetric_reln on jx2 (jx_reorder(fkey1,fkey2));

These should work fine on 7.3.4, but I didn't verify that. You should
consider upgrading.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0