在没有顺序的情况下，对多个列施加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，但我不知道如何操作。您有什么建议吗？

谢谢， naccyde

- Quentin

---

通常的解决方案是在(最大(friend0_id, friend1_id), 最小(friend0_id, friend1_id))上创建一个约束条件，但我不知道如何在SQLite中实现这个。SQLite有“max”和“min”函数可以替换“greatest”和“least”，但我认为它不允许对表达式进行索引。 - mu is too short

@muistooshort 自版本3.9.0起支持表达式索引。 - CL.

Answer 1

如@mu too short所提到的，方法是使用(greatest(friend0_id, friend1_id), least(friend0_id, friend1_id))，现在我有一个可以正常工作的两列无序唯一约束条件。我是这样在SQLite中实现的（可能不是最好的方式）：

• Create a trigger which set min(friend0_id, friend1_id) to friend0 and max(friend0_id, friend1_id) to 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 a unique constraint on the couple (friend0_id, friend1_id) :

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

它可以正常工作！

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

- Quentin