MySQL uniqueness across multiple fields

Question

I am developing an app that needs to match people together. Each person can only match with one other person.. So, in the table below I am trying to make it so that values from user1 & user2 are unique across both fields:

CREATE TABLE `match_table` (
    `user1` int(11) NOT NULL,
    `user2` int(11) NOT NULL,
UNIQUE KEY `user2` (`user2`),
UNIQUE KEY `user1` (`user1`))

So for example the following INSERT statement should ignore rows 2 and 4. Or at a minimum I need to be able to flag those rows to be ignored. Note that line 5 is OK because of the fact that line 2 and 4 have been ignored.

INSERT IGNORE INTO match_table (user1, user2)
VALUES
    (1,2),
    (2,3),
    (4,5),
    (6,4),
    (3,6)

Is there any index that can accomplish this? .. Otherwise is there some UPDATE I could do after insertion that could flag the ones I want to ignore?

Answer 1

Assuming that the matching has no direction, you can use your existing table design but store the match both ways in the table:

INSERT IGNORE INTO match_table (user1, user2)
VALUES
(1,2),
(2,1),
(2,3),
(3,2),
(4,5),
(5,4),
(6,4),
(4,6),
(3,6),
(6,3)

If you just fetch all rows you will got each matching twice. You can avoid this as follows:

SELECT * FROM match_table WHERE user1 < user2
1, 2
3, 6
4, 5

One potential issue with this design is that it is possible to insert a row with a user who matches himself.

Answer 2

If you are just asking for a database constraint, you can get that with UNIQUE INDEX ('user1','user2')