Unique Constraint on two fields and their reverse

Question

Hello,

The problem is the following,

I have a table of friendships in my database, the friendship table has two foreign keys to the users table.

I want to set a unique key on the two foreign keys so that I will have no duplicates, but I want more, I want that if user A added user B, then B won't be able to add A, and I want to have only one row in the table that represents the friendship between A and B.

Is there any way to add such a constraint from the database server?

Until now I have maintained this constraint in my code.

Answer 1

You can add a unique constraint on ( LEAST(friend1,friend2), GREATEST(friend1,friend2) ). The exact wording may depend on the RDBMS being used.

Answer 2

Yes you need to add a trigger that checks the business rule you have described and rolls back the transaction if it finds a conflict. The syntax for this might be different in different databases, but in SQL server it would be

  Create Trigger trig_StopRecipFriendships
  for Insert, Update On Friendships
  As
    If Exists (Select * From Friendships F1
                 Join Friendships F2  
                   On F1.UserA = F2.UserB
                      And F1.UserB = F2.UserA)
     Begin
         Rollback Transaction
         Raiserror ('These Users are already friends', 16,1)
     End