SQLite对多列无顺序的唯一约束

Question

我需要有关表定义中的问题的帮助:我有一个将被定义的表，例如，如下所示:

• id，主键
• friend0_id，表users上的外键
• friend1_id，表users上的外键

问题是我不想有多次(friend0_id,friend1_id)，无论表中的顺序如何。

我试图在这对(friend0_id,friend1_id)上定义一个UNIQUE约束，但是约束中定义的列顺序(这里是friend0_id，然后 friend1_id) 很重要。所以:

| 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

我希望示例中的 id 2 和 3 被禁止，但我不知道如何实现。你有什么建议给我吗？

谢谢你，纳西德

Answer 1

如@mu too short提到的，方法是使用 (greatest(friend0_id,friend1_id), less(friend0_id,friend1_id))，所以，现在我有一个有效的 2 列无序唯一约束。我在 SQLite 中这样做了(这再好不过了):

• 创建一个触发器，将 min(friend0_id,friend1_id) 设置为 friend0，将 max(friend0_id,friend1_id) 设置为 friend 1:

  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
  

• 然后，对这对夫妇设置唯一约束(friend0_id,friend1_id):

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

而且它有效!

编辑:如果有人需要此提示，请不要忘记也创建更新触发器，否则更新请求可能会破坏该机制。