Unique index on 2 columns in mysql

Question

I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details.

here is the schema of the table (UserFriends)

id  int,
Userid int,
friendid int,
createdate timespan

now, I want to create unique index on userid & friendid. that i have created unique index well. so, right now i am not able to insert same userid and friendid as duplicate. but if i am inserting same value in opposite field it accept without generating error.

example :

insert into userfriends ( userid, friendid )
select 1, 2  --- insert perfect
insert into userfriends ( userid, friendid )
select 1, 2  --- show error because unique index comes in a picture

now i am inserting

insert into userfriends ( userid, friendid )
select 2, 1  --- records insert here (i don't want this)

How do i prevent this?

Answer 1

One way to avoid your issue is to make it a business rule that the userid and friendid values be inserted in a specific order. You could add a row constraint (either by adding INSERT/UPDATE triggers, or by applying your changes via a Stored Procedure) to ensure that userid<friendid so it would be okay to insert (userid, friendid) VALUES (1, 2) but any attempt to add (userid, friendid) VALUES (2, 1) would fail.

Example using TRIGGER:

DELIMITER $$
DROP TRIGGER IF EXISTS `userfriends_INSERT` $$
CREATE TRIGGER `userfriends_INSERT` 
BEFORE INSERT ON `userfriends`
FOR EACH ROW
BEGIN
    IF new.`userid` >= new.`friendid` THEN
        SIGNAL SQLSTATE '45000';  # unhandled user-defined exception
    END IF;
END $$
DELIMITER ;

Test:

mysql> SHOW VARIABLES LIKE "version";
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| version       | 5.5.29-0ubuntu0.12.04.1 |
+---------------+-------------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE userfriends;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| userid   | int(11) | NO   | PRI | NULL    |       |
| friendid | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = "userfriends";
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

mysql> SELECT trigger_name FROM information_schema.triggers;
+--------------------+
| trigger_name       |
+--------------------+
| userfriends_INSERT |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM userfriends;
Empty set (0.00 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO userfriends (userid, friendid) VALUES (2, 1);
ERROR 1644 (45000): Unhandled user-defined exception condition
mysql> INSERT INTO userfriends (userid, friendid) VALUES (1, 2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'