CODEWITHSUNDEEP
c#postgresqlentity-framework-core
phwt
phwt

Reputation: 1404

EF Core with PostgreSQL 16 - Create a Unique Index with `NULLS NOT DISTINCT` for a GUID

I have one property in an entity that is the type of Guid it's unique but nullable.

Since null GUID is equal to 00000000-0000-0000-0000-000000000000, the PostgreSQL disallows me from inserting a second record where the Guid is null (all zeroes) as it violates the index constraint.

Because of this I've tried to configure NULLS NOT DISTINCT by adding AreNullsDistinct(false) to the index.

public class Employee
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public required Guid Id { get; set; }
    public required string FirstName { get; set; }
    public required string LastName { get; set; }
    public Guid AdObjectId { get; set; } // Link employee back to identity in AD, can be null as some employee might not present in the AD
}

public class DatabaseContext : DbContext
{
    public DbSet<Employee> Employee { get; set; }

    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().HasIndex(e => e.AdObjectId).IsUnique().AreNullsDistinct(false);
    }
}

However, when I inspect the DDL, the NULLS NOT DISTINCT are absent from the index:

create table "Employee"
(
    "Id"         uuid                           not null
        constraint "PK_Employee" primary key,
    "FirstName"  text                           not null,
    "LastName"   text                           not null,
    "AdObjectId" uuid                           not null
);

create unique index "IX_Employee_AdObjectId"
    on "Employee" ("AdObjectId");

Below is the migration (only the index creation part) for reference:

migrationBuilder.CreateIndex(
    name: "IX_Employee_AdObjectId",
    table: "Employee",
    column: "AdObjectId",
    unique: true)
    .Annotation("Npgsql:NullsDistinct", false);

I am wondering, do I miss any additional configuration for the NULLS NOT DISTINCT to be present on the index?

Environment details:

Upvotes: 0

Views: 511

Answers (1)

sa-es-ir
sa-es-ir

Reputation: 5082

Here is the Npgsql document:

By default, when you create a unique index, PostgreSQL treats null values as distinct; this means that a unique index can contain multiple null values in a column. When creating an index, you can also instruct PostgreSQL that nulls should be treated as non-distinct; this causes a unique constraint violation to be raised if a column contains multiple null values.

Seems what you want is the default behaivor of npgsql and you shouldn't use AreNullsDistinct to suppress it!

I'd suggest to remove the AreNullsDistinct and create new migration but check the generated code before updating database.

The npgsql link: See

Upvotes: 2

Related Questions