后唯一约束不强制唯一性

Question

这是我的限制：

CREATE UNIQUE INDEX index_subscriptions_on_user_id_and_class_type_id_and_deleted_at
  ON subscriptions
  USING btree
  (user_id, class_type_id, deleted_at);

该查询证明约束实际上不起作用:

SELECT id, user_id, class_type_id,deleted_at
FROM subscriptions;

下面是输出：

为什么唯一性没有被强制执行？

Answer 1

这是因为created_at列中的NULLvalue。唯一索引（或约束）允许多行中包含＜code＞NULL＜/code＞。

防止这种情况的唯一方法是将列声明为< code>NOT NULL以强制为其赋值，或者将唯一列减少到< code>(user_id，class_type_id)

Answer 2

Postgres中的唯一索引基于值相等，但NULL从不等于任何值，包括其他NULL。因此，任何具有空deleted_at值的行都不同于任何其他可能的行——因此可以插入任意数量的行。

解决这个问题的一种方法是创建部分索引，对有和没有NULL的行应用不同的规则：

 CREATE UNIQUE INDEX ... ON subscriptions
 (user_id, class_type_id) WHERE deleted_at IS NULL;

 CREATE UNIQUE INDEX ... ON subscriptions
 (user_id, class_type_id, deleted_at) WHERE deleted_at IS NOT NULL;