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?
Making the primary key for the FRIENDSHIP
table to be:
...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.
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)
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)
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) )
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With