18

I'm intrested in what is the real difference between this

e.HasIndex(c => new { c.UserId, c.ApplicationId, c.Value }).IsUnique();

and this

e.HasAlternateKey(c => new { c.UserId, c.ApplicationId, c.Value });

in EF core fluent api configuration.

The use-case here is that we have a PK called Id then we have a secondary(alternate key) which is a composite key which is built from the following attributes: UserID ApplicationId and Value. The only thing i found about my question is the following quote from here: https://www.learnentityframeworkcore.com/configuration/fluent-api/hasindex-method

"While similar in most practical aspects, this is not the same as creating a unique constraint on the column, which can be achieved by creating an alternate key on the property."

I dont really get the concept of this difference and i also don't know the difference between a unique constraint and a unique index.

If somone could explain the definitions(and the difference between them) to me i would be really grateful.

Menyus
  • 6,633
  • 4
  • 16
  • 36
  • 2
    Here's an explanation of the difference https://www.mssqltips.com/sqlservertip/4270/difference-between-sql-server-unique-indexes-and-unique-constraints/ – juharr Feb 04 '20 at 12:51
  • @juharr Thank's man the article was informative would you like to sum up the difference as an answer or want me to write one? ^^ – Menyus Feb 04 '20 at 13:04
  • 1
    Alternate keys can be used at principal side of a FK relationships. Also in EF Core once created they cannot be modified (similar to primary keys). EF Core documentation: [Alternate Keys](https://learn.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations#alternate-keys) – Ivan Stoev Feb 04 '20 at 13:12
  • 1
    The issue of mutable alternate keys is being tracked at [Support modifying the value of alternate key properties](https://github.com/dotnet/efcore/issues/4073), so please upvote to elevate the issue for consideration in a future release. – Mark G Jun 04 '20 at 20:19

1 Answers1

13

From the official documentation:

If you just want to enforce uniqueness on a column, define a unique index rather than an alternate key (see Indexes). In EF, alternate keys are read-only and provide additional semantics over unique indexes because they can be used as the target of a foreign key.

In other words - both create a unique index, but the alternate key has some additional properties (e.g. can be used as the target of a foreign key)

Pavlo Kyrylenko
  • 2,355
  • 2
  • 21
  • 21