I am wondering if it is possible in MySQL to make sure double rows are impossible if two columns match. I am working on a table which represents followers, so it looks like this:
+----------+------------+-------------+
| followID | followerID | followingID |
+----------+------------+-------------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
+----------+------------+-------------+
Basically means user 1 is following user 2 and 3 and user 2 and 3 follow user 1 back. Now I want to avoid double results in here, so I want uniqueness based on followerID and followingID. Currently I run two queries to check if the row already exists (with a SELECT) and only if it doesn't I allow a new INSERT.
However, I would like a second security measure where it is impossible to INSERT if it already exists. So I want to avoid this:
+----------+------------+-------------+
| followID | followerID | followingID |
+----------+------------+-------------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 2 | 1 | *
| 6 | 1 | 3 | *
+----------+------------+-------------+
Row 5 and 6 should never be possible to INSERT because they already are in the table. Can this be achieved in MySQL or do I have to check it on the server?
