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
>

Thanks for the suggestions Reece. Some of the pairs are aleady being used
in code so I don't know if I can reverse the order to create the
fkey1<fkey2 condition. I'm going to check my code tomorrow and see if I
can rearrange the keys without too much impact on the software. I also
need to verify that I'll never have a pair where fkey1 = fkey2.

Margaret Gillon