数据库设计 - 链接相同实体的多对多表？

I have a table User with a column id. I want the user to have relationships with other users, so I create a table Relationships with columns user_id_1 and user_id_2.

我有一个用户列表ID。我希望用户与其他用户建立关系,因此我创建了一个表关系列user_id_1和user_id_2。

The questions is how to constrain the table so that

问题是如何约束表格

1) there are no entries where user_id_1 equals user_id_2.

1)没有user_id_1等于user_id_2的条目。

For example, entry (1, 1) is bad, because it indicates a relationship to oneself.

例如,条目(1,1)是坏的,因为它表明与自己的关系。

2) if there is an entry (user_id_1, user_id_2), the entry (user_id_2, user_id_1) is not allowed.

2)如果有条目(user_id_1,user_id_2),则不允许条目(user_id_2,user_id_1)。

For example, having entries (1, 2) and (2, 1) is bad, because it indicates the same relationship.

例如,具有条目(1,2)和(2,1)是不好的,因为它表示相同的关系。

I am using MySQL, although I think this is a general design issue. Thanks!

我正在使用MySQL,虽然我认为这是一个一般的设计问题。谢谢!

2 个解决方案

#1

You can write a INSERT and UPDATE triggers on the join table that check these conditions.

您可以在连接表上编写INSERT和UPDATE触发器来检查这些条件。

#2

You use a trigger:

你使用一个触发器:

DELIMITER $$

CREATE TRIGGER bi_relationschip_each BEFORE INSERT ON relationship FOR EACH ROW
BEGIN
  IF NEW.user1_id = NEW.user2_id THEN
    SELECT error_user1_cannot_be_equal_to_user2 FROM generate_error;
  END IF; 
END $$

DELIMITER ;

#1