Entity Framework code first unique column

Question

I am using Entity Framework 4.3 and using Code Fist.

I have a class

public class User
{
   public int UserId{get;set;}
   public string UserName{get;set;}
}

How do I tell Entity Framework that UserName has to be unique when creating database table? I would prefer to use data anotations instead of configuration file if possible.

juFo · Answer 1 · 2019-01-25T08:07:07.407

312

In Entity Framework 6.1+ you can use this attribute on your model:

[Index(IsUnique=true)]

You can find it in this namespace:

using System.ComponentModel.DataAnnotations.Schema;

If your model field is a string, make sure it is not set to nvarchar(MAX) in SQL Server or you will see this error with Entity Framework Code First:

Column 'x' in table 'dbo.y' is of a type that is invalid for use as a key column in an index.

The reason is because of this:

SQL Server retains the 900-byte limit for the maximum total size of all index key columns."

(from: http://msdn.microsoft.com/en-us/library/ms191241.aspx )

You can solve this by setting a maximum string length on your model:

[StringLength(450)]

Your model will look like this now in EF CF 6.1+:

public class User
{
   public int UserId{get;set;}
   [StringLength(450)]
   [Index(IsUnique=true)]
   public string UserName{get;set;}
}

Update:

if you use Fluent:

  public class UserMap : EntityTypeConfiguration<User>
  {
    public UserMap()
    {
      // ....
      Property(x => x.Name).IsRequired().HasMaxLength(450).HasColumnAnnotation("Index", new IndexAnnotation(new[] { new IndexAttribute("Index") { IsUnique = true } }));
    }
  }

and use in your modelBuilder:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  // ...
  modelBuilder.Configurations.Add(new UserMap());
  // ...
}

Update 2

for EntityFrameworkCore see also this topic: https://github.com/aspnet/EntityFrameworkCore/issues/1698

Update 3

for EF6.2 see: https://github.com/aspnet/EntityFramework6/issues/274

Update 4

ASP.NET Core Mvc 2.2 with EF Core:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Unique { get; set; }

edited Jan 25 '19 at 08:07

answered Apr 14 '14 at 08:51

juFo

17,849
10
105
142

29

Thanks for replying to this guy's old post with some relevant current info! – Jim Yarbro Jul 20 '14 at 17:28
3

why an index, why not just a constraint? – John Henckel Oct 27 '15 at 20:50
3

To answer the index vs contraint question...MSDN: `"There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating a UNIQUE constraint on the column makes the objective of the index clear."` https://msdn.microsoft.com/en-us/library/ms187019.aspx – tinonetic Jun 21 '16 at 08:12
3

Note; in Entity Framework Core 1.0.0-preview2-final a data annotations method isn't available - https://docs.efproject.net/en/latest/modeling/indexes.html#data-annotations – Edward Comeau Sep 07 '16 at 21:36
What if the table already has value. I can't set it as null or default to a value.When I generate the migration script the constraint adding script thorws error – Arjun Menon Apr 27 '21 at 07:21
4

In EntityFrameworkCore it's done like this: `[Index(propertyNames: nameof(UserName), IsUnique = true)] public class User { public int UserId{get;set;} [StringLength(450)] public string UserName{get;set;} }` – Patrick Koorevaar Apr 29 '21 at 09:04

score 29 · Answer 2 · answered May 16 '12 at 08:36

29

EF doesn't support unique columns except keys. If you are using EF Migrations you can force EF to create unique index on UserName column (in migration code, not by any annotation) but the uniqueness will be enforced only in the database. If you try to save duplicate value you will have to catch exception (constraint violation) fired by the database.

answered May 16 '12 at 08:36

Ladislav Mrnka

360,892
59
660
670

1

That's exactly what I was looking for! I was wondering if it was possible to add such constraints using migrations. – Alex Jorgenson Oct 07 '12 at 21:49
@Ladislav Mrnka: Is this also possible via the Seed method? – Raheel Khan Jun 01 '13 at 06:19
3

Care to provide an example? Thanks! – Zero3 May 11 '16 at 17:31

Anas Alweish · Answer 3 · 2019-09-25T06:42:13.113

19

In EF 6.2 using FluentAPI, you can use HasIndex()

modelBuilder.Entity<User>().HasIndex(u => u.UserName).IsUnique();

edited Sep 25 '19 at 06:42

answered Sep 25 '19 at 06:37

Anas Alweish

2,818
4
30
44

score 11 · Answer 4 · edited May 23 '17 at 12:10

From your code it becomes apparent that you use POCO. Having another key is unnecessary: you can add an index as suggested by juFo.
If you use Fluent API instead of attributing UserName property your column annotation should look like this:

this.Property(p => p.UserName)
    .HasColumnAnnotation("Index", new IndexAnnotation(new[] { 
        new IndexAttribute("Index") { IsUnique = true } 
    }
));

This will create the following SQL script:

CREATE UNIQUE NONCLUSTERED INDEX [Index] ON [dbo].[Users]
(
    [UserName] ASC
)
WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

If you attempt to insert multiple Users having the same UserName you'll get a DbUpdateException with the following message:

Cannot insert duplicate key row in object 'dbo.Users' with unique index 'Index'. 
The duplicate key value is (...).
The statement has been terminated.

Again, column annotations are not available in Entity Framework prior to version 6.1.

What about creating an index with mutliple columns using the configuration ? — FindOutIslamNow, Oct 02 '18 at 09:22

score 5 · Answer 5 · answered Mar 09 '14 at 19:58

5

Note that in Entity Framework 6.1 (currently in beta) will support the IndexAttribute to annotate the index properties which will automatically result in a (unique) index in your Code First Migrations.

answered Mar 09 '14 at 19:58

Robba

7,684
12
48
76

cpoDesign · Accepted Answer · 2017-03-30T08:20:09.560

Solution for EF4.3

Unique UserName

Add data annotation over column as:

 [Index(IsUnique = true)]
 [MaxLength(255)] // for code-first implementations
 public string UserName{get;set;}

Unique ID , I have added decoration [Key] over my column and done. Same solution as described here: https://msdn.microsoft.com/en-gb/data/jj591583.aspx

IE:

[Key]
public int UserId{get;set;}

Alternative answers

using data annotation

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column("UserId")]

using mapping

  mb.Entity<User>()
            .HasKey(i => i.UserId);
        mb.User<User>()
          .Property(i => i.UserId)
          .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
          .HasColumnName("UserId");

Be careful with this solution. Adding the `Key` attribute to the `UserName` property will cause the `UserName` property to become the primary key in the database. Only the `UserId` property should be marked with the `Key` attribute. This solution will give you the 'correct' behavior on the programming side while giving you an incorrect database design. — Alex Jorgenson, Oct 07 '12 at 21:48

Entity Framework code first unique column

6 Answers6

Linked