Clever Geek Handbook

SQLite unique constraint on multiple columns without ordering

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

  • id

    , primary key
  • friend0_id

    , foreign key in table users

  • friend1_id

    , foreign key in table users

The problem is that I don't want to have a pair multiple times (friend0_id, friend1_id)

, no matter what order is in the table.

I tried to define a constraint UNIQUE

for the pair (friend0_id, friend1_id)

, but the column order is defined in the constraint (here friend0_id

, THEN friend1_id

). 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 id 2 and 3 to be disallowed in the example, but I cannot figure out how to do this. Do you have some tips for me?

Thanks naccyde

+3


source to share


1 answer


As @mu mentioned is too short , the way is to use (greatest(friend0_id, friend1_id), least(friend0_id, friend1_id))

, so now I have a valid 2 column free unique constraint. I did it in SQLite this way (which couldn't be better):

  • Create a trigger that sets min(friend0_id, friend1_id)

    in friend0

    and max(friend0_id, friend1_id)

    out friend1

    :

    CREATE TRIGGER friend_fixed_id_order_trigger
AFTER INSERT ON friends 
BEGIN UPDATE friends 
  SET 
    friend0_id = min(NEW.friend0_id, NEW.friend1_id), 
    friend1_id = max(NEW.friend0_id, NEW.friend1_id) 
  WHERE friends.id = NEW.id; 
END

  • Then set the only limit on the pair (friend0_id, friend1_id)

    :

    CREATE UNIQUE INDEX `friends_unique_relation_index` 
ON `contacts` (`friend0_id` ,`friend1_id`)


And it works!

EDIT . If anyone needs this advice, be sure to create an update trigger as well, otherwise the update request might break the mechanism.

+3


source






More articles:


All Articles