How to implement a bidirectional unique index across multiple columns

Question

Okay i know i can do this in the application layer, which is probably the easiest thing to do, but just to make sure that no errors water down to the DB, i have a serious question

I have two columns X and Y, each to store two integers (either A or B in any of the columns). Is it possible to have a unique index constraint such that, in no instance should we have

1. Column X with A and Column Y with B
2. Column X with B and Column Y with A

I'll give a scenario

I have two users, userA has id 678498 and userB has id 679879. Both users are about to play a 2 player game which requires that a new record for this session be stored in a table (tbl_chalenger). To do so, i have a table with columns "host" and "challenger".

I have a unique constrain added to tbl_challenger as

UNIQUE KEY `UNIQUE_PARTICIPANTS` (`host`,`challenger`)

Branding users a host or challenger is basically dependent on who initiated the game. So if userA initiates the game we have an query as follows

INSERT INTO `tbl_challenger` VALUES(678498 , 679879);

which creates a new record, Sadly though, if at the same time userB attempts to initiate a game with user A, we get

INSERT INTO `tbl_challenger` VALUES(679879, 678498 );

Which creates a new unwanted row, of the same participants. This irrespective of the UNIQUE key constraint.

So my question is how to have a constraint that is bidirectional?, such that "host-challenger" as well as "challenger-host" cannot have the same data pair

Answer 1

In mysql the only way I can think of is to add a couple of utility columns like

CREATE TABLE tbl_challenger (
  host int,
  challenger int,
  u0 int, u1 int
);

and add a couple of triggers that set u0 and u1 to the least and greatest of the two:

CREATE TRIGGER uinsert BEFORE INSERT ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);
CREATE TRIGGER uupdate BEFORE UPDATE ON tbl_challenger
 FOR EACH ROW SET NEW.u0 = LEAST(NEW.host,NEW.challenger),
  NEW.u1 = GREATEST(NEW.host,NEW.challenger);

then you add a unique index on (u0,u1)

CREATE UNIQUE INDEX uniqueness ON tbl_challenger(u0,u1);

And now you will get an error trying to insert duplicate pair regardless of the order.

On a decent RDBMS like PostgreSQL you would be able to use index on expression:

CREATE UNIQUE INDEX uniqueness ON tbl_challenger
    ( LEAST(host,challenger), GREATEST( host,challenger) );

So, switch before it's too late ;-)

Answer 2

Hi,

I don't think you can do that "out of the box", like using a UNIQUE index or whatever stuff like this.

Still, you might probably be able to implement that kind of check using a trigger, triggered before inserts and updates.

I've not worked with triggers for quite sometime, and it wasn't on MySQL, so I won't be able to give you any example, but here's the relevant page of the manual : 12.1.19. CREATE TRIGGER Syntax

As a sidenote : this kind of constraint is, like you said, generally implemented on the application side.