Обсуждение: Constraint for two fields unique any order

> 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

CREATE TABLE junction
(

);

On Wed, 19 Jul 2006 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 think the following does what you need, though my "foo_table" won't be
the same as your junction table.  If you can't put a UNIQUE constraint in
your table, add to the PERFORM in the function.


CREATE TABLE foo_table
(
  id    SERIAL    PRIMARY KEY,
  fkey1    INTEGER    NOT NULL,
  fkey2    INTEGER    NOT NULL,
    UNIQUE(fkey1, fkey2)
);

CREATE FUNCTION other_way_test()
RETURNS TRIGGER
AS $$
BEGIN
PERFORM id FROM foo_table WHERE foo_table.fkey1=NEW.fkey2 AND foo_table.fkey2=NEW.fkey1;
IF FOUND
THEN
  RAISE NOTICE 'pair of numbers violates reverse order uniqueness';
  RETURN NULL;
ELSE
  RETURN NEW;
END IF;
END
$$
LANGUAGE 'plpgsql'
;

CREATE TRIGGER other_way_test
BEFORE INSERT
ON foo_table
FOR EACH ROW
EXECUTE PROCEDURE other_way_test()
;


--
Bob Dowling:   Head of Unix Systems Division, University Computing Service
rjd4@cam.ac.uk New Museums Site, Pembroke Street, Cambridge.  CB2 3QH
01223 334710   http://www-uxsup.csx.cam.ac.uk/~rjd4/
   --- Those who do not learn from Dilbert are doomed to repeat it. ---

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