INSERTing into mysql table for user relationships

Question

I have a table for many-to-many relationship of users (three columns: relationship_id, user_id, user_id). How can I keep the relationships unique when the table accepts any entry? When I have a row of 22 11 43 How can I prevent INSERT of next_id 11 43 and more importantly next_id 43 11? When user 11 requested relationship with user 43, user 43 must not be able to request relationship with user 11.

How can I check two columns before INSERT?

And my problem is even more serious as I have two tables (request and relationships). A row from request table will be deleted and inserted into relationships upon user approval. The reason for using two tables is that many pending requests make the table so long, which should be used regularly for displaying user friends.

When INSERTing request from user 11 to user 43, what is the fastest and efficient method to check for possible existence of 11 43 and 43 11 rows in tables: requests and relationships?

Answer 1

Anytime I have used a "Linking Table" to keep track of many to many relationships I always DELETE any existing relationships before INSERTING the new relationships. However, this may not work in your case because your linking table contains the surrogate key relationship_id. If you drop the surrogate key from the linking table and use a stored procedure you can do every thing you listed.

Identifying Duplicates

Create a View using CASE logic

CREATE VIEW vFriendRequests
AS
SELECT
CASE
  WHEN ID_1 < ID_2 THEN ID_1
  ELSE ID_2
END CASE as RequestId_1,
CASE
  WHEN ID_1 < ID_2 THEN ID_2
  ELSE ID_1
END CASE as RequestId_2
FROM Friend_Requests_Table

Then you can do a select distinct from this view to get only the unique sets of requests.