Search code examples
mysqlsqlconstraintsdata-integrity

How do I make a combination of values unique in a mySQL table no mater what order they are in?

I am creating a application that involves a friend system such as the one in facebook. The way I structured this in my SQL database is by having a friend table which has the columns ID, accountID1, accountID2 so that the each of the two accounts involved in the friendship is noted. The problem is that a friendship can be noted in two different ways for example:

ID | accountID1 | accountID2
 1 |      1     |      2 
 2 |      2     |      1

If I make the combination unique it does not protect against this from occurring. How can I create a constraint in MySQL to prevent a friendship to be present in two different ways to ensure data integrity? or is there a different way of storing this information to prevent such problems in the first place?

Solution

  • The final solution I used is to first of all get rid of the ID for the friends table and make a composite primary key out of the two account ID's PrimaryKey(accountID0, accountID1). This ensures that the combination of them are unique. Then I created a "before Insert trigger" to switch the values so that the smaller accountID is always in accountID0. This method worked perfectly and made no problems so far.