unique pair in a "friendship" database

Question

I'm posting this question which is somewhat a summary of my other question.

I have two databases:
1) db_users.
2) db_friends.

I stress that they're stored in separate databases on different servers and therefore no foreign keys can be used.

In 'db_friends' I have the table 'tbl_friends' which has the following columns:
- id_user
- id_friend

Now how do I make sure that each pair is unique at this table ('tbl_friends')?
I'd like to enfore that at the table level, and not through a query.

For example these are invalid rows:
1 - 2
2 - 1

I'd like this to be impossible to add.

Additionally - how would I seach for all of the friends of user 713 while he could be mentioned, on some friendship rows, at the second column ('id_friend')?

Answer 1

You're probably not going to be able to do this at the database level -- your application code is going to have to do this. If you make sure that your tbl_friends records always go in with (lowId, highId), then a typical PK/Unique Index will solve the duplicate problem. In fact, I'd go so far to rename the columns in your tbl_friends to (id_low, id_high) just to reinforce this.

Your query to find anything with user 713 would then be something like

SELECT id_low AS friend FROM tbl_friends WHERE (id_high = ?)
UNION ALL
SELECT id_high AS friend FROM tbl_friends WHERE (id_low = ?)

For efficiency, you'd probably want to index it forward and backward -- that is by (id_user, id_friend) and (id_friend, id_user).

If you must do this at a DB level, then a stored procedure to swap arguments to (low,high) before inserting would work.

Answer 2

You'd have to use a trigger to enforce that business rule.
Making the two columns in tbl_friends the primary key (unique constraint failing that) would only ensure there can't be duplicates of the same set: 1, 2 can only appear once but 2, 1 would be valid.

how would I seach for all of the friends of user 713 while he could be mentioned, on some friendship rows, at the second column ('id_friend')?

You could use an IN:

WHERE 713 IN (id_user, id_friend)

..or a UNION:

JOIN (SELECT id_user AS user
        FROM TBL_FRIENDS
      UNION ALL
      SELECT id_friend
        FROM TBL_FRIENDS) x ON x.user = u.user