SQL - Best practice for a Friendship table

Question

Before you show me duplicates, please note that I've searched through the site an have found some examples but not quite specific to my problem :)

What's the best way to create a Friendship table in SQL, but making sure that each row is unique in the sense that the same UserID and FriendID will never be alowed regardless of which column they belong to?

I have this rough example

CREATE TABLE [dbo].[Friendship](
    [UserID] [uniqueidentifier] NOT NULL,
    [FriendID] [uniqueidentifier] NOT NULL,
    [FriendshipStatus] [int] NOT NULL
)

And there are 2 foreign keys to the Users table, both from UserID and FriendID.

At the moment though, I can insert a Friendship between users twice, thus creating a duplicate. Example

UserID    FriendID    FriendshipStatus
Guid 123   Guid 789    1
Guid 789   Guid 123    1

How do I ensure this integrity is enforced, perhaps 2 PKs? Some sort of a unique Index? Or would you suggest a better table design all together? Also, would you put an autoincrementing FriendshipID? If so, can you explain why?

Answer 1

Making the primary key for the FRIENDSHIP table to be:

• userid
• friendid

...will ensure that you can't have duplicates in order. Meaning, it will stop you from adding duplicates of userid "123" and friendid "789". If you include the status column, that's no longer the case, because a different status value will allow for duplicates of the userid and friendid column.

Stopping Reverse Pairs

In order to stop reverse pairs -- userid "789" and friendid "123" -- you need to either include the logic to check if the pair already exists in the table in a stored procedure, function, or trigger. A CHECK constraint of userid < friendid would stop a valid attempt to add userid "789" and friendid "123" if the reverse doesn't already exist.

INSERT INTO FRIENDSHIP
SELECT @userid, @friendid, 1
  FROM FRIENDSHIP f
 WHERE NOT EXISTS(SELECT NULL
                    FROM FRIENDSHIP t
                   WHERE (t.userid = @friendid AND t.friendid = @userid)
                      OR (t.userid = @userid AND t.friendid = @friendid)

Answer 2

Quassnoi wrote an article explaining why you might want to have a version of the table with two rows per pair, to make queries easier: http://explainextended.com/2009/03/07/selecting-friends/

(the article talks about MySQL but it is worth bearing in mind as a performance technique for any SQL database)

Answer 3

I did a small change to perform the internal query and returns only a record when exists (dual table) at the end finish parentheses

INSERT INTO FRIENDSHIP
SELECT @userid, @friendid, 1
  FROM DUAL f
 WHERE NOT EXISTS(SELECT NULL
                FROM FRIENDSHIP t
               WHERE (t.userid = @friendid AND t.friendid = @userid)
                  OR (t.userid = @userid AND t.friendid = @friendid) )