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

Question

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?

Assuming mysql since you list phpmyadmin, You can use and insert/update trigger to enforce uniqueness. One such example can be found at https://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events. — SteveB, Jan 01 '18 at 15:42
https://stackoverflow.com/questions/4219979/sql-best-practice-for-a-friendship-table checkout this — jasinth premkumar, Jan 01 '18 at 15:43
Why do you suppose the 1 has 2 as friend if 2 lists 1 as fried - is there some kind of opt-in to friend-requests? — Patrick Artner, Jan 01 '18 at 15:43
I would create an unique index and then save frienship always in a specific order (first the lower account id for example) this way an unique index should work. — jeprubio, Jan 01 '18 at 15:44
Trigger on insert: put the lower id in acountID1, the higher id in accountID2, switch if nessessary when ids given the other way around. — Patrick Artner, Jan 01 '18 at 15:44
Thanks Patrick & jeprubio, thats probably the best way to do it XD — Mercury, Jan 01 '18 at 15:48
No, I just forgot to change the number after copy and pasting the first row, sorry — Mercury, Jan 01 '18 at 17:21
Could someone give an example for how the triggers content would look because I don't know how to switch two variable ? — Mercury, Jan 04 '18 at 10:01

score 0 · Accepted Answer · answered Feb 02 '18 at 12:22

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.

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

1 Answers1