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