SQLite unique constraint on multiple column without order

Viewed 662

I need help on a problem in my table definitions : I have a table which will be defined, for example, like this :

  • id, primary key
  • friend0_id, foreign key on table users
  • friend1_id, foreign key on table users

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

1 Answers
Related