Geek asks and answers

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

I have a MySQL table for users with the primary key _id, and I want to present friendship (visibility in the friends lists of other users) as a table with userId foreign key pairs. I think 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)
)

I understand that this will allow you to insert both (userIdA = 2, userIdB = 7), and (userIdA = 7, userIdB = 2)as separate lines. I want one line per friendship, that is, one line per userIds pair.

Does anyone know how I can improve this? Even if the above limitation is performed to obtain a list of all the user's friends foo, I have to make the union, something like: SELECT userIdB AS friendUserId WHERE userIdA = foo UNION SELECT userIdA WHERE userIdB = foo. Is this the best way to fulfill this request, or should I think about changing my schema?

+5
1

TRIGGER BEFORE INSERT -:

  • userIdA userIdB

, (A, B) (B, A) .

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 ;
+4

More articles:


All Articles