Forum OpenACS Q&A: Re: copletely unique index on foo_map

Collapse
Posted by Ben Adida on
Tilmann's solution is good, although it may require changing your queries around to make sure they always specify field_1 as the lower of the two values.

You have to ask yourself if the semantics of your application are such that 1,10 is really the same thing as 10,1. If so, then the worry is what your queries throughout the system will look like. My guess is that you want to impact your queries as little as possible.

Thus, if 1,10 is really the same thing as 10,1, I would recommend making your mapping table a double mapping table. Set up a trigger so that whenever a pair is inserted, the reverse pair is also inserted (and you'll need the same delete trigger). When 1,10 is inserted, your trigger also inserts 10,1. This will do two things: (1) you will get a constraint violation if you insert 1,10 and then 10,1 explicitly, since 10,1 will already have been inserted implicitly by the first insert. (2) your queries can assume both mappings are present, which will probably simplify your other work if the two fields are truly semantically identical.

(Note that if you don't like triggers, you can do this using stored procs like map_insert(integer,integer) and map_delete(integer,integer) which do the double inserts and deletes for you - then your app must always call these APIs instead of doing direct inserts and deletes).