Does EF Core allow a filtered/partial unique index which does not allow multiple NULLs?

c# ef-code-first entity-framework entity-framework-core

Question

EF Core allows a unique (filtered) index to contain multiple NULLs.

Can I configure it to not allow multiple NULLs?

Suppose I've defined a unique index over properties Column1, Column2, Column3:

config.Entity<Product>()
    .HasIndex("Column1", "Column2", "Column3")
    .IsUnique();

An example:

Id   Column1   Column2   Column3
1    100       "foo"     "bar"    // unique
2    100       "foo"     "bar"    // not allowed (dupe)
3    100       NULL      "bar"    // allowed
4    100       NULL      "bar"    // allowed - but I want this to fail
5    100       NULL      "bar"    // allowed - but I want this to fail
6    100       NULL      "bar"    // allowed - but I want this to fail

This is what I want:

  • Row1 unique
  • Row2 fails (unique constraint violation) as it's a dupe of row1
  • Row3 allowed because it's different to row1
  • Row4/5/6 must fail, because they are dupes of row3

But row4/5/6 do not fail.

1
0
5/14/2017 10:09:41 AM

Accepted Answer

I'm using SQLite, which excludes NULLs from the index.

The migration generates this:

CREATE UNIQUE INDEX [IX_Product] ON [Product] 
([Column1Id] ASC, [Column2Id] ASC, [Column3Id] ASC);

So in the migration class, I removed that, and replaced it with a Sql("...") call with:

CREATE UNIQUE INDEX [IX_Product] ON [Product] 
(COALESCE([Column1Id],"") ASC, COALESCE([Column2Id],"") ASC, COALESCE([Column3Id],"") ASC);

And now it enforces uniqueness of NULLs.

(I've tested this on SQLite, not sure about SQL Server.)

0
5/14/2017 12:26:24 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow