Re: Constraint for two fields unique any order

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. ---