Re: Constraint for two fields unique any order

On Wed, 19 Jul 2006 MargaretGillon(at)chromalloy(dot)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(at)cam(dot)ac(dot)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. ---