Setting a column unique null without being an index in .NET EF Core

Question

I would like to add a field to a table that should be unique, but is not required (so it can be null) and it is not an index. This is doable with SQL, something like this in mysql:

CREATE TABLE MyTable (
  ...
  field VARCHAR(255) UNIQUE
  ...
)

I tried overriding DbContext.OnModelCreating and adding these:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // this creates an index, which I don't need/want for this field
    modelBuilder.Entity<User>()
        .HasIndex(x => x.Field)
        .IsUnique();

}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // This creates a NOT NULL Constraint
    modelBuilder.Entity<User>()
        .HasAlternateKey(x => x.Field);
}

Thes code below gives me an error message saying The property 'Field' on entity type 'User' cannot be marked as nullable/optional because it has been included in a key {'Field'}.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // This gives me an error
    modelBuilder.Entity<User>()
        .HasAlternateKey(x => x.Field);
    modelBuilder.Entity<User>()
        .Property(x => x.Field)
        .IsRequired(false);
}

If there is a way for me to just add the SQL by hand, I would be ok with that. I don't know if that is possible if I edit the migration file by hand, but the migration.Design.cs files use the same ModelBuilder class, so I only have those same methods to work with.

I don't know about MySql, but in SQL Server unique constraints are implemented using unique indexes under the hood. I suspect MySql probably does it the same, as that's an optimal way to do this - therefor if your worried about the extra storage an index will have - don't. — Zohar Peled, Mar 30 '20 at 09:23
Unique constraints create/use unique indexes. For instance, since you are providing MySQL example, see [MySQL UNIQUE constraints and indexes](https://www.mysqltutorial.org/mysql-unique-constraint/). — Ivan Stoev, Mar 30 '20 at 09:23

score 2 · Accepted Answer · answered Mar 30 '20 at 09:34

I have encounter this problem before. The reason why this cannot be done with fluent API is most likely (i'm speculating) because several databases doesn't allow this feature, and it makes sense. this post explains why:

MySQL: UNIQUE constraint without index

If you still wanna do this, just run a command on the OnModelCreating like this:

context.Database.ExecuteSqlCommand("ALTER TABLE table
DROP CONSTRAINT table_constraint"); //SQL Server / Oracle / MS Access

Setting a column unique null without being an index in .NET EF Core

1 Answers1