当两个列中的一个为空时，如何创建唯一索引？

Question

（Rails 4.2.1，Sqlite3）我有三个模型 - M1，M2，M3。

M1属于M2

M1还属于M3

M1具有一个:name (string)字段。

我有以下必须验证的约束条件：

1）M1记录可以关联M2或M3，但不能两者兼有。

2）无论指定M2还是M3，M1名称必须是唯一的。

我已在模型中实现了约束条件（1），并且它按预期工作。（我之所以提到它，仅因为它可能与场景相关）。

对于约束条件（2），我在迁移中添加了一个索引，如下所示：

add_index :m1s, [:name, :m2_id, :m3_id], unique: true, name: "idx_m1_name"

然后我调用：

> m2 = M2.create! # success
> m1_1 = M1.create!(name: 'm1_1', m2: m2) #success
> m1_2 = M1.create!(name: 'm1_1', m2: m2) # this line should fail, but doesn't

m1_1和m1_2被创建了 - 我期望由于唯一性约束，m1_2应该失败。

我检查了索引是否按预期添加。 此外，根据约束条件1，在m1_1和m1_2中，m3_id都为nil，不确定它是否相关。

为什么约束没有被检查？

- Anand

Answer 1

所以在这两种情况下，m3_id 都是 NULL 吗？在 Sqlite3 中，对于唯一索引的上下文环境中，空值被认为与另一个空值不同。

就唯一性索引而言，所有 NULL 值都被视为与其他所有 NULL 值不同，因此是唯一的。

请参见 https://sqlite.org/lang_createindex.html 我认为 MySQL 和 Postgres 也是一样的。

- Thong Kuah

---

谢谢，我怀疑是空值，但是你的回复和链接证实了这一点:-)！那么我该如何在数据库中验证上述的唯一性约束（2）呢？ - Anand

嗯，所以你可以有M2或M3，但不能同时拥有两者。你考虑过使用多态关系吗？http://guides.rubyonrails.org/association_basics.html#polymorphic-associations - Thong Kuah

如何在任意一列可以为空的情况下，对多个列设置唯一约束？这是可能的，但有点hacky。 - Thong Kuah

多态对我的情况不起作用。我有一个类似于嵌套文章的博客。因此，M1就像一篇文章，M2就像一个博客，而M3实际上是一个父级文章。因此，一篇文章可以是博客中的根文章，也可以是另一篇文章的子文章，但不能同时具备两者身份。 - Anand

是的，我同意另一种方法很不专业。我可能会在Rails模型中创建一个验证而不是更改默认的空值。我不能将它们设置为其他值，因为两者都是外键ID，将它们视为非空将触发各种问题。这应该是一个相当常见的问题。我想知道是否有一些优雅的解决方法。 - Anand

Answer 2

你可以用两个索引来表示它。

add_index :m1s, [:name, :m2_id], unique: true
add_index :m1s, [:name, :m3_id], unique: true

由于空值在唯一性方面被视为不同，因此第一个索引将不会限制仅设置了m3_id的行，反之亦然。

（您可能还对CHECK约束感兴趣，该约束检查m2_id或m3_id中恰好有一个为空。在Rails中，CHECK约束有一个小缺点，即需要用SQL表达模式文件。有关“仅适用于某些行的索引”的更通用解决方案，请参见部分索引。）

- mpartel

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Anand

你可以在Rails验证中检查两者是否都不为空。这种验证不容易出现竞争问题（就像唯一性验证那样）。我已经开了一个关于CHECK约束的问题。 - mpartel