Interchangeable, composite primary keys

Question

I have a table foo that has only two fields: fooIdA and fooIdB (both of the same type). Those are composite primary keys, so:

primary key (fooIdA, fooIdB)...

Considering this, how can I make all permutations of the keys to be the same?
That is, (fooIdA, fooIdB) = (fooIdB, fooIdA).

Answer 1

Depending on your DBMS, you can create a unique index on an expression that prevents inserting (1,2) and (2,1)

In Postgres and Oracle you can do this:

create unique index unique_combinations 
    on the_table (least(fooida, fooidb), greatest(fooida, fooidb));

Answer 2

You don't specify your RDBMS. You can also add calculated fields with MAX, MIN values and add UNIQUE constraint on these calculated fields. Here is the MSSQL example:

CREATE TABLE ATest (id1 int, id2 int);

ALTER TABLE ATest ADD idMax AS (CASE WHEN id1>=id2 THEN id1 ELSE id2 END);  
ALTER TABLE ATest ADD idMin AS (CASE WHEN id1>=id2 THEN id2 ELSE id1 END);  
ALTER TABLE ATest ADD CONSTRAINT UniqueConstCalc UNIQUE(idMax,idMin);

insert into ATest values (1,1);
insert into ATest values (1,2);
insert into ATest values (2,1);

(1 row(s) affected)

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'UniqueConstCalc'. 
Cannot insert duplicate key in object 'dbo.ATest'. 
The duplicate key value is (2, 1).
The statement has been terminated.