如何创建具有可为空的列的复合唯一约束？ [英] How to create composite UNIQUE constraint with nullable columns?

问题描述

假设我有一个表，其中有几列[a, b, c, d]，这些列都可以为空。此表使用Typeorm进行管理。 我要在[a, b, c]上创建唯一约束。但是，如果这些列中有一列为空，则该约束不起作用。例如，我可以插入[a=0, b= 1, c=NULL, d=0]和[a=0, b= 1, c=NULL, d=1]，其中d具有不同的值。

使用原始SQL，我可以设置多个部分约束(Create unique constraint with null columns)，但是在我的例子中，唯一约束是在10列上。为每种可能的组合设置限制似乎很荒谬...

我也可以创建一种哈希函数，但此方法似乎不适合我？

Typeorm是否为此类情况提供解决方案？

推荐答案

如果您的值永远不会出现在这些列中，则可以将它们用作索引中的替代项：

create unique index on the_table (coalesce(a,-1), coalesce(b, -1), coalesce(c, -1));

这样，NULL值在索引中得到相同的处理，而不需要在表中使用它们。

如果这些列是numeric或float(而不是integer或bigint)，则使用'-Infinity'可能是更好的替换值。

---

这有一个缺点：

但是，除非您还使用coalesce()表达式，否则该索引将不能用于对这些列的查询。因此，有了上面的索引，查询如下：

select *
from the_table
where a = 10
  and b = 100;

将不使用索引。您需要使用与索引本身相同的表达式：

select *
from the_table
where coalesce(a, -1) = 10
  and coalesce(b, -1) = 100;

这篇关于如何创建具有可为空的列的复合唯一约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！