Reputation: 921
I have 3 tables:
Users: _id_, username
Movies: _id_, title
Friends: _#id1, #id2_, accepted
I'm with InnoDB, everything is working like that. But, even if friends' primary-key is (users.id, users.id), 2 rows like that are accepted by MySQL:
1) (1, 2)
2) (2, 1)
Is there a way to consider these 2 rows as a duplicate key for MySQL ?
Thanks for your help.
Upvotes: 1
Views: 144
Reputation: 3771
I cannot think of any way to let MySQL alone solve this problem. I would use GREATEST ( http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest ) and LEAST ( http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least ) for any insert/update on this table:
INSERT INTO friends (LEAST(_#id1, _#id2), GREATEST(_#id1, _#id2));
So you will never get (2, 1) and always have (1, 2).
Upvotes: 1