Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
The pair (UserId 1, FriendId 2) is completely different than the pair (UserId 2, FriendId 1), you realize that? So there's no reason it should fail in your current setup. Is this the real schema or only a demo for your idea? –  Marian Mar 23 '13 at 12:54
add comment

1 Answer

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'
share|improve this answer
    
I posted a answer a lot like this in StackOverflow ( stackoverflow.com/a/15625521/491757 ) . Your trigger is much more concise. One warning about your answer: The trigger will not fire on an empty table. So, userid,friendid would let (5,3) or (3,3) as the very first row (Please test this out). Otherwise, +1 !!! (I guess this is why a Stored Procedure would be better for cover such a case, but most would rather set the trigger and forget it in favor of just using SQL INSERT) –  RolandoMySQLDBA Mar 26 '13 at 21:09
    
@RolandoMySQLDBA Thanks very much for taking the time to comment on my answer. I have edited it to give more complete test results. For some reason (beginner's luck, perhaps), my trigger does fire and prevent a "bad" INSERT when the table is empty. –  Gord Thompson Mar 26 '13 at 22:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.