如何为包含可空值的字段添加正确的唯一约束？

实体模型类 #

public class RoutePermission
{
    public Guid? UserId { get; set; }
    public Guid? DistrictId { get; set; }
    public Guid RouteId { get; set; }
    public bool Admin { get; set; }
}

需求场景 #

需要实现以下唯一约束逻辑：

{ routeId: 1, userId: 1, districtId: null, admin: true } // 允许
{ routeId: 1, userId: null, districtId: 1, admin: true } // 允许
{ routeId: 2, userId: 1, districtId: null, admin: true } // 允许
{ routeId: 2, userId: 1, districtId: null, admin: true } // 不允许（重复记录，需触发约束）

最初无效的配置 #

使用常规唯一索引配置无法生效：

modelBuilder.Entity<RoutePermission>()
   .HasIndex(rp =>
      new { rp.RouteId, rp.DistrictId, rp.UserId }
   )
   .IsUnique();

问题根源 #

PostgreSQL的唯一索引将NULL值视为彼此不同（即NULL != NULL），因此两条仅NULL字段相同的记录会被索引判定为不同条目，不会触发约束。

解决方案 #

方法一：用COALESCE替换NULL值创建唯一索引 #

将NULL替换为一个业务中不会使用的占位值（比如Guid.Empty），让相同的NULL组合被视为相等：

modelBuilder.Entity<RoutePermission>()
    .HasIndex(rp => new 
    { 
        rp.RouteId, 
        DistrictId = rp.DistrictId ?? Guid.Empty, 
        UserId = rp.UserId ?? Guid.Empty 
    })
    .IsUnique();

注意：需确保占位值（如Guid.Empty）不会出现在实际业务数据中，避免误触发约束。

方法二：使用PostgreSQL排除约束（EXCLUDE） #

利用PostgreSQL的排除约束，通过数据库原生逻辑自动将NULL视为相等，实现需求：

modelBuilder.Entity<RoutePermission>()
    .ToTable(t => t.HasConstraint("UQ_RoutePermission_UniqueCombination", @"
        EXCLUDE USING btree (
            ""RouteId"" WITH =,
            ""DistrictId"" WITH =,
            ""UserId"" WITH =
        )
    "));

该方法无需修改数据逻辑，不存在占位值冲突问题，但需要编写原生SQL语句。

内容的提问来源于stack exchange，提问作者Shmuli Adelman