Create unique constraint on column

Question

I have this model

public class Record
{
    public int Id {get;set;}
    public int Client {get;set;}
    public bool IsActive {get;set;}
}

I want to create a constraint for the IsActive column.

Only 1 record can be active per client.

How can i achieve this on the model?

You can achieve this by creating unique index on `Client` and `IsActive` columns — vasily.sib, Jun 24 '19 at 07:57
Oh I get it. Basically it means if there is a client with true on isActive it cannot insert this again. But isn't this going to do the same for false? because the previous ones are all false. After client record is done updating it is set to false so client can start on a new record next time he logs in — Jackal, Jun 24 '19 at 07:58
you need to look at filtered index. EF can't provide you with an easy way here, but you can look at [this question](https://stackoverflow.com/questions/32894547/entity-framework-filter-index) — vasily.sib, Jun 24 '19 at 08:06
something like `CREATE UNIQUE NONCLUSTERED INDEX ... WHERE ([IsActive]=(1))` — vasily.sib, Jun 24 '19 at 08:07

score 2 · Accepted Answer · answered Jun 24 '19 at 08:37

You need to create a filtered index for your column the ef core have filter index feature and you can configure it in your dbcontext with overriding OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Record>(entity =>
   {
       entity.HasIndex(e => e.Client)
        .HasName("Record_Filtered_Index")
        .HasFilter("([IsActive]=(1))");
   });
}

Create unique constraint on column

1 Answers1