SQL composite primary key without ordering (pair of integers in either order must be unique)

Question

I have a MySQL table for Users with the primary key _id, and I want to represent friendships (visibility on friends lists of other users) as a table with pairs of userId foreign keys. I'm thinking something like:

CREATE TABLE UserFriendships (
  userIdA INT NOT NULL,
  userIdB INT NOT NULL,
  PRIMARY KEY (userIdA, userIdB),
  FOREIGN KEY (userIdA) REFERENCES Users(_id)
  FOREIGN KEY (userIdB) REFERENCES Users(_id)
)

It's my understanding that this will allow both (userIdA = 2, userIdB = 7) and (userIdA = 7, userIdB = 2) to be inserted as distinct rows. I want one row per friendship, that is to say one row per pair of userIds.

Does anyone know how I could improve this? Even if the above constraint is met, to get a list of all the friends of user foo I have to do a union, something like: SELECT userIdB AS friendUserId WHERE userIdA = foo UNION SELECT userIdA WHERE userIdB = foo. Is this the best way to accomplish that query, or should I think about changing my schema?

Answer 1

You can use a TRIGGER BEFORE INSERT to enfore a business rule:

• userIdA is always the user with the lower ID and userIdB always the user with the higher ID

This way both combinations (A,B) and (B,A) result in the same column order with the same primary key.

DELIMITER |
CREATE TRIGGER enforce_friendship_id_order BEFORE INSERT ON UserFriendships
  FOR EACH ROW BEGIN
    SET @lowerId := IF(NEW.userIdA < NEW.userIdB, NEW.userIdA, NEW.userIdB);
    SET @higherId := IF(NEW.userIdA > NEW.userIdB, NEW.userIdA, NEW.userIdB);
    SET NEW.userIdA = @lowerId;
    SET NEW.userIdB = @higherId;
  END;
|
DELIMITER ;