I need help on a problem in my table definitions : I have a table which will be defined, for example, like this :
id, primary keyfriend0_id, foreign key on tableusersfriend1_id, foreign key on tableusers
The problem is I do not want to have multiple times the couple (friend0_id, friend1_id), whatever the order the are in the table.
I tried to define an UNIQUE constraint on the couple (friend0_id, friend1_id), but the columns order defined in the constraint (here friend0_id, THEN friend1_id) matters. So :
| id | friend0_id | friend1_id |
|----|------------|------------|
| 1 | 3 | 4 | -> OK
| 2 | 4 | 3 | -> OK, as the columns order in index matters
| 3 | 3 | 4 | -> Not OK, constraint prevent this
I would like the id 2 and 3 in the example to be disallowed, but I can't figure how. Do you have some tips for me ?
Thank you, naccyde