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:
But row4/5/6 do not fail.
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.)