On Wed, 2006-07-19 at 09:01 -0700, [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq