Unique constraint across two columns

Question

I have a table called friends with the following structure:

id
user_id
friend_id

and I want the rows to be unique so for example:

1, 6, 12
2, 6, 12

would break! I've achieved this with: ALTER TABLE friends ADD UNIQUE INDEX user_id_friend_id (user_id, friend_id);

BUT I also want the following to not be possible

1, 6, 12
2, 12, 6

How do I do that? I tried doing the same statement but with the fields reversed but it didn't work... Any ideas?

What database are you using? Sometimes this is easy, sometimes it's not. Depends on the database. — Mark Byers, Dec 16 '12 at 22:56
I have the constraint in the code for my app. But I'd like to do it in the DB as well. Take it MySQL is rubbish for this? — Cameron, Dec 16 '12 at 22:58

score 2 · Answer 1 · answered Dec 16 '12 at 23:17

2

You could use triggers to ensure that user_id <= friend_id, this with the unique constraint will achieve what you want. You'll need an insert and update trigger like this:

Create Trigger
  Friends_ins_check 
Before Insert On
  Friends
For Each Row
Begin
  If new.user_id > new.friend_id Then
    Set @temp = new.user_id;
    Set new.user_id = new.friend_id;
    Set new.friend_id = @temp;
  End If;
End

Example

answered Dec 16 '12 at 23:17

Laurence

10,896
1
25
34

if someone is confused by `For Each Row` like i was, here is a post clarifying the behaviour: https://stackoverflow.com/questions/23374151/how-does-for-each-row-work-in-triggers-in-mysql? – LeonTheProfessional Jun 22 '20 at 05:14

Unique constraint across two columns

1 Answers1