> On Wed, 2006-07-19 at 09:01 -0700, MargaretGillon@chromalloy.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.