具有空列的唯一约束(Hibernate，PostgreSQL)

Question

我有像Clazz这样的类

@Table(
name="tablename", 
uniqueConstraints=
@UniqueConstraint(
name= "uniqueColumn_deleted_uk",
columnNames={"myuniquecolumn", "deleted"}
)
)
public class Clazz {
@Column(name = "deleted")
private LocalDateTime deleted;
}

删除是可为空的，PosgreSQL创建唯一的索引，如

CREATE UNIQUE INDEX uniqueColumn_date_uk ON public.tablename (short_code_3, deleted);

并且它允许在删除为空时插入重复的 myunique列。

如何防止这种情况发生？

我希望删除时有非重复项为空。

Answer 1

您应该创建两个部分唯一索引

create unique index on public.tablename (short_code_3, deleted) where deleted is not null;
create unique index on public.tablename (short_code_3) where deleted is null;

(我不知道如何在您的ORM中执行此操作)。

Answer 2

这是不可能的，因为 null 永远不会 = null。

阅读有关 SQL https://en.wikipedia.org/wiki/Null_(SQL) 中的空值的更多信息

如果要将已删除的列包含在唯一索引中，则必须为该列提供默认值。

Answer 3

像 klin 这样的拖曳部分索引是 Postgres 14 之前的最佳实践。

Postgres 15为此添加了NULLS NOT DISTINCT：

CREATE UNIQUE INDEX foo_idx ON public.tbl (short_code_3, deleted) NULLS NOT DISTINCT;

看：

• 使用空列创建唯一约束