Unique Key constraints for multiple columns in Entity Framework

Question

I'm using Entity Framework 5.0 Code First;

public class Entity
 {
   [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public string EntityId { get; set;}
   public int FirstColumn  { get; set;}
   public int SecondColumn  { get; set;}
 }

I want to make the combination between FirstColumn and SecondColumn as unique.

Example:

Id  FirstColumn  SecondColumn 
1       1              1       = OK
2       2              1       = OK
3       3              3       = OK
5       3              1       = THIS OK 
4       3              3       = GRRRRR! HERE ERROR

Is there anyway to do that?

Gregorio

Answer 1

With Entity Framework 6.1, you can now do this:

[Index("IX_FirstAndSecond", 1, IsUnique = true)]
public int FirstColumn { get; set; }

[Index("IX_FirstAndSecond", 2, IsUnique = true)]
public int SecondColumn { get; set; }

The second parameter in the attribute is where you can specify the order of the columns in the index.
More information: MSDN

Velarize

This is correct for data annotations :), if you want the answer for using the fluent API see Niaher's answer below https://mcmap.net/q/98577/-unique-key-constraints-for-multiple-columns-in-entity-framework – Rothwell

But I need it working for foreign keys! Can you help me? – Inguinal

@0xFEEDC0DE see my answer below that address the use of foreign keys in indices. – Horseflesh

Can you post how to use this index with linq to sql? – Cystolith

@JJS - I got it to work where one of the properties was a foreign key.. by any chance is your key a varchar or nvarchar? There is a limit to the length of which can be used as a unique key.. #2864493 – Express

using System.ComponentModel.DataAnnotations.Schema; – Isia

please note that this code creates a "unique index" but not a "unique key" which are different things. – Bendicta

What if I want to disallow the switch as well? – Hermaherman

Answer 2

I found three ways to solve the problem.

Unique indexes in EntityFramework Core:

First approach:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Entity>()
   .HasIndex(p => new {p.FirstColumn , p.SecondColumn}).IsUnique();
}

The second approach to create Unique Constraints with EF Core by using Alternate Keys.

Examples

One column:

modelBuilder.Entity<Blog>().HasAlternateKey(c => c.SecondColumn).HasName("IX_SingeColumn");

Multiple columns:

modelBuilder.Entity<Entity>().HasAlternateKey(c => new [] {c.FirstColumn, c.SecondColumn}).HasName("IX_MultipleColumns");

EF 6 and below:

---

First approach:

dbContext.Database.ExecuteSqlCommand(string.Format(
                        @"CREATE UNIQUE INDEX LX_{0} ON {0} ({1})", 
                                 "Entitys", "FirstColumn, SecondColumn"));

This approach is very fast and useful but the main problem is that Entity Framework doesn't know anything about those changes!

---

Second approach:
I found it in this post but I did not tried by myself.

CreateIndex("Entitys", new string[2] { "FirstColumn", "SecondColumn" },
              true, "IX_Entitys");

The problem of this approach is the following: It needs DbMigration so what do you do if you don't have it?

---

Third approach:
I think this is the best one but it requires some time to do it. I will just show you the idea behind it: In this link http://code.msdn.microsoft.com/CSASPNETUniqueConstraintInE-d357224a you can find the code for unique key data annotation:

[UniqueKey] // Unique Key 
public int FirstColumn  { get; set;}
[UniqueKey] // Unique Key 
public int SecondColumn  { get; set;}

// The problem hier
1, 1  = OK 
1 ,2  = NO OK 1 IS UNIQUE

The problem for this approach; How can I combine them? I have an idea to extend this Microsoft implementation for example:

[UniqueKey, 1] // Unique Key 
public int FirstColumn  { get; set;}
[UniqueKey ,1] // Unique Key 
public int SecondColumn  { get; set;}

Later in the IDatabaseInitializer as described in the Microsoft example you can combine the keys according to the given integer. One thing has to be noted though: If the unique property is of type string then you have to set the MaxLength.

Gregorio

(y) I find this answer better. Another thing, the third approach may not necessarily be the best. (I like the first one actually.) I personally prefer not having any EF artifacts carried over into my entity classes. – Fendley

Possibly, second approach should be: CREATE UNIQUE INDEX ix_{1}_{2} ON {0} ({1}, {2})? (see BOL ) – Languid

Stupid question: Why so you start all your name by "IX_" ? – Ungracious

@BastienVandamme it is a good question. the auto generate index by EF begin with with IX_. It seem to be a convention in EF index by default, the index name will be IX_{property name}. – Gregorio

Yes it should be. Thanks for the Fluent API implementation. There is a serious lack of documentation on this – Follow

you don't need array in HasAlternateKey(c => new [] {c.FirstColumn, c.SecondColumn}) should be: HasAlternateKey(c => new {c.FirstColumn, c.SecondColumn}) – Coroneted

Answer 3

If you're using Code-First, you can implement a custom extension HasUniqueIndexAnnotation

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Infrastructure.Annotations;
using System.Data.Entity.ModelConfiguration.Configuration;

internal static class TypeConfigurationExtensions
{
    public static PrimitivePropertyConfiguration HasUniqueIndexAnnotation(
        this PrimitivePropertyConfiguration property, 
        string indexName,
        int columnOrder)
    {
        var indexAttribute = new IndexAttribute(indexName, columnOrder) { IsUnique = true };
        var indexAnnotation = new IndexAnnotation(indexAttribute);

        return property.HasColumnAnnotation(IndexAnnotation.AnnotationName, indexAnnotation);
    }
}

Then use it like so:

this.Property(t => t.Email)
    .HasColumnName("Email")
    .HasMaxLength(250)
    .IsRequired()
    .HasUniqueIndexAnnotation("UQ_User_EmailPerApplication", 0);

this.Property(t => t.ApplicationId)
    .HasColumnName("ApplicationId")
    .HasUniqueIndexAnnotation("UQ_User_EmailPerApplication", 1);

Which will result in this migration:

public override void Up()
{
    CreateIndex("dbo.User", new[] { "Email", "ApplicationId" }, unique: true, name: "UQ_User_EmailPerApplication");
}

public override void Down()
{
    DropIndex("dbo.User", "UQ_User_EmailPerApplication");
}

And eventually end up in database as:

CREATE UNIQUE NONCLUSTERED INDEX [UQ_User_EmailPerApplication] ON [dbo].[User]
(
    [Email] ASC,
    [ApplicationId] ASC
)

Celloidin

But that is index not constraint! – Severson

In your second code block (this.Property(t => t.Email)), what is that containing class? (ie: what is this) – Ligulate

nvm. EntityTypeConfiguration<T> – Ligulate

@RomanPokrovskij: The difference between a unique index and a unique constraint appears to be a matter of how records of it are maintained in SQL Server. See technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx for details. – Badlands

@Celloidin I appreciate your nice extension method – Arius

I was able to build a multiple column index using this method, when one of the indexes was a foreign key, but I had to specifically include the foreign key in the business object using an attribute – Zygophyte

I would like to underline that RomanPokrovskij has a point. All these approaches introduce a unique index NOT a unique constraint. It gets the job done in practical terms but if you strive for 100% "correctness" in the way your tables get declared you will have to manually tweak the auto-generated db-migration in order to have it introduce an actual CONSTRAINT [aka Sql("ALTER TABLE ADD CONSTRAINT etc")]. Just my 2c. – Unbodied

This may have been required at the time it was written. It is now (EF core 2.1) possible to do directly with the fluent API as I detail below – Sulfite

Answer 4

The answer from niaher stating that to use the fluent API you need a custom extension may have been correct at the time of writing. You can now (EF core 2.1) use the fluent API as follows:

modelBuilder.Entity<ClassName>()
            .HasIndex(a => new { a.Column1, a.Column2}).IsUnique();

Sulfite

doesn't work with navigation properties -> System.InvalidOperationException: 'Organization' cannot be used as a property on entity type 'Association' because it is configured as a navigation.. – Ferment

@Ferment I found it easy to work around the issue by simply adding the navigation property identifier to the model. In my case it follows convention, so simply adding InstitutionId. Then .HasIndex(x => new{ x.InstitutionId, x.ExternalId }) . Produces migrationBuilder.CreateIndex( name: "IX_Customers_InstitutionId_ExternalId", table: "Customers", columns: new[] { "InstitutionId", "ExternalId" }, unique: true); – Harmonics

Answer 5

For those finding this looking for a 2021 solution, the working version of the accepted answer should now look like this:

[Index(nameof(FirstColumn), nameof(SecondColumn), IsUnique = true)]
public class Entity
 {
   [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public string EntityId { get; set;}
   public int FirstColumn  { get; set;}
   public int SecondColumn  { get; set;}
 }

So that the annotation should live on the model and not the individual columns. Also note the nameof() syntax.

This answer was derived from the official documentation: https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

Holder

Another last thing: SQL (or in my case, PostgreSQL) will yell at you if you try to run the ef update command and you have existing rows in the modified table that are breaking this new index! (as one would expect) – Holder

Just note that this answer applies to EF Core and is not available in EF6.x – Resourceful

Thanks, works on .net core and efcore 6 – Bought

How diff is this from [Index(nameof(FirstColumn), IsUnique = true), Index(nameof(SecondColumn), IsUnique = true)]? – Regalia

Been a while since I worked with EF Core, but my guess is that would mean both columns would be indexed seperatetly for uniqueness, and not combined uniqueness. But maybe they are identical, try it! – Holder

Answer 6

You need to define a composite key.

With data annotations it looks like this:

public class Entity
 {
   public string EntityId { get; set;}
   [Key]
   [Column(Order=0)]
   public int FirstColumn  { get; set;}
   [Key]
   [Column(Order=1)]
   public int SecondColumn  { get; set;}
 }

You can also do this with modelBuilder when overriding OnModelCreating by specifying:

modelBuilder.Entity<Entity>().HasKey(x => new { x.FirstColumn, x.SecondColumn });

Tonometer

But the are not keys I just want them as Unique the key should be the Id? I have updated the quesition thanks for help! – Gregorio

Answer 7

Completing @chuck answer for using composite indices with foreign keys.

You need to define a property that will hold the value of the foreign key. You can then use this property inside the index definition.

For example, we have company with employees and only we have a unique constraint on (name, company) for any employee:

class Company
{
    public Guid Id { get; set; }
}

class Employee
{
    public Guid Id { get; set; }
    [Required]
    public String Name { get; set; }
    public Company Company  { get; set; }
    [Required]
    public Guid CompanyId { get; set; }
}

Now the mapping of the Employee class:

class EmployeeMap : EntityTypeConfiguration<Employee>
{
    public EmployeeMap ()
    {
        ToTable("Employee");

        Property(p => p.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(p => p.Name)
            .HasUniqueIndexAnnotation("UK_Employee_Name_Company", 0);
        Property(p => p.CompanyId )
            .HasUniqueIndexAnnotation("UK_Employee_Name_Company", 1);
        HasRequired(p => p.Company)
            .WithMany()
            .HasForeignKey(p => p.CompanyId)
            .WillCascadeOnDelete(false);
    }
}

Note that I also used @niaher extension for unique index annotation.

Horseflesh

In this example you have both Company and CompanyId. This means the caller can change one but not the other and have an entity with incorrect data. – Nichollenicholls

@Nichollenicholls Which caller are you talking about? The class represents data in a database. Changing the value through queries will ensure consistency. Depending on your what the client application can do you might need to implement checks but that's not the scope of the OP. – Horseflesh

Answer 8

In the accepted answer by @chuck, there is a comment saying it will not work in the case of FK.

it worked for me, case of EF6 .Net4.7.2

public class OnCallDay
{
     public int Id { get; set; }
    //[Key]
    [Index("IX_OnCallDateEmployee", 1, IsUnique = true)]
    public DateTime Date { get; set; }
    [ForeignKey("Employee")]
    [Index("IX_OnCallDateEmployee", 2, IsUnique = true)]
    public string EmployeeId { get; set; }
    public virtual ApplicationUser Employee{ get; set; }
}

Portland

Long time. let say it has been work before long time! thank you for the update please add a comment to @chuck answer. I think Chuck is before long time does not use SO. – Gregorio

Does the property EmployeeID Here need an attribute to limit its length for it to be indexed? Else its created with VARCHAR(MAX) which cannot have a index? Add Attribute [StringLength(255)] to EmployeeID – Refulgent

EmployeeID is a GUID. Many tutorial suggest to map the GUID to string instead of guid, I dont know why – Portland

Answer 9

I assume you always want EntityId to be the primary key, so replacing it by a composite key is not an option (if only because composite keys are far more complicated to work with and because it is not very sensible to have primary keys that also have meaning in the business logic).

The least you should do is create a unique key on both fields in the database and specifically check for unique key violation exceptions when saving changes.

Additionally you could (should) check for unique values before saving changes. The best way to do that is by an Any() query, because it minimizes the amount of transferred data:

if (context.Entities.Any(e => e.FirstColumn == value1 
                           && e.SecondColumn == value2))
{
    // deal with duplicate values here.
}

Beware that this check alone is never enough. There is always some latency between the check and the actual commit, so you'll always need the unique constraint + exception handling.

Moultrie

Thanks @GertArnold for the answer but I do not want to validate the uniqueness on the business layer this is a database job and this shall be done in the database! – Gregorio

OK, stick to the unique index then. But you'll have to deal with index violations in the business layer anyhow. – Moultrie

from outside when I receive this kind of exception I will catched and than maybe reporting the error and break the process or shutdown the application. – Gregorio

Yes, ... do I need to respond to that? Remember I know nothing of your application, I can't tell you what the best way is to deal with these exceptions, only that you have to deal with them. – Moultrie

Be wary of DB unique constraints with EF. If you do this and then you wind up having an update that flip-flops the values of one of the columns that is part of the unique key, entity frameowkr will fail on the save unless you add a whole transaction layer. For instance: Page object has a child collection of Elements. Each element has SortOrder. You want combo of PageID and SortOrder to be unique. In front end, user flip flops order of elements with sortorder 1 and 2. Entity Framework will fail the save b/c it's trying to update sortorders one at a time. – Dermott

Answer 10

You should place Index attrib on top of the entity classs and define your multiple keys in string[]

[Index("FirstColumn", "SecondColumn", IsUnique = true, Name = "My_Unique_Index")]
public class Entity    

 {
   [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public string EntityId { get; set;}
   public int FirstColumn  { get; set;}
   public int SecondColumn  { get; set;}
 }

Bathometer

Answer 11

Recently added a composite key with the uniqueness of 2 columns using the approach that 'chuck' recommended, thank @chuck. Only this approached looked cleaner to me:

public int groupId {get; set;}

[Index("IX_ClientGrouping", 1, IsUnique = true)]
public int ClientId { get; set; }

[Index("IX_ClientGrouping", 2, IsUnique = true)]
public int GroupName { get; set; }

Gramps

Answer 12

I wanted to add my answer since the provided solutions did not help me. In my case one of the columns was a foreign key reference.

Old model:

public class Matrix
{
    public int ID { get; set; }

    public MachineData MachineData { get; set; }

    public MachineVariant MachineVariant { get; set; }
}

Note that MachineVariant is an enum and MachineData is a reference.

Trying to use the provided solution by @Bassam Alugili:

modelBuilder.Entity<Matrix>()
   .HasIndex(sm => new { sm.MachineData, sm.DoughVariant }).IsUnique(true);

Didn't work. So I added an ID column for the machineData foreign key like so:

public class Matrix
{
    public int ID { get; set; }

    public MachineData MachineData { get; set; }

    [ForeignKey("MachineData")]
    public int MachineDataID { get; set; }

    public MachineVariant MachineVariant { get; set; }
}

And changed the modelbuilder code to this:

modelBuilder.Entity<Matrix>()
   .HasIndex(sm => new { sm.MachineDataID, sm.DoughVariant }).IsUnique(true);

Which resulted in the desired solution

Submissive