Forum OpenACS Q&A: copletely unique index on foo_map

Collapse
Posted by David Kuczek on
I want to create an index or constraint on a mapping table (foo_map) with two columns (foo_id_i integer on table1(foo_id), foo_id_ii integer on table1(foo_id)). The index should work in both directions. Inserts like 1,10 and 10,1 should be turned down because the same correlation would be mapped. How can I do this?
Collapse
Posted by Tilmann Singer on
There might be a better way, but in this case you could define that one field of the mapping table always contains the lower key and the other the higher:

(foo_id_lower integer references table1(foo_id) not null, foo_id_higher integer references table1(foo_id) not null, constraint  foo_id_lower < foo_id_higher)

(not sure about the constraint syntax)

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