没有排序的SQL组合主键（任何顺序的整数对都必须是唯一的）

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:

我有一个带有主键_id的用户的MySQL表，我希望将友谊（在其他用户的朋友列表上的可见性）表示为具有成对userId外键的表。我想的是：

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.

据我所知，这将允许（userIdA = 2，userIdB = 7）和（userIdA = 7，userIdB = 2）作为不同的行插入。我想要每个友谊一行，也就是说每对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?

有谁知道我怎么能改善这个？即使满足上述约束，要获得用户foo的所有朋友的列表，我必须进行联合，例如：SELECT userIdB AS friendUserId WHERE userIdA = foo UNION SELECT userIdA WHERE userIdB = foo。这是完成该查询的最佳方式，还是应该考虑更改我的架构？

1 个解决方案

#1

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

您可以使用TRIGGER BEFORE INSERT来制定业务规则：

userIdA is always the user with the lower ID and userIdB always the user with the higher ID
userIdA始终是ID较低的用户，userIdB始终是ID较高的用户

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

这样，组合（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 ;

#1