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
– Mark Byers, Commented Dec 16, 2012 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
– Cameron, Commented Dec 16, 2012 at 22:58

Laurence · Accepted Answer · 2012-12-16 23:17:59Z

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, 2012 at 23:17

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LeonTheProfessional Over a year ago

if someone is confused by For Each Row like i was, here is a post clarifying the behaviour: stackoverflow.com/questions/23374151/…?

Collectives™ on Stack Overflow

Unique constraint across two columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related