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