Unique Constraints in the Entity Framework


Update: this feature has been postponed and will not be included in Entity Framework 5.

Unique Constraints in the Entity Framework

We’ve been busy working on enabling unique constraints in the Entity Framework. Unique Constraints allow you to model candidate keys (also known as alternate keys, or natural keys). This post covers the design of the feature so that we can get some early thoughts and opinions on what you’d like to see in this feature.

Note: Tooling and the design time experience is an essential part of any Entity Framework feature that we do. However, the motivation of the Design blog is to take a look under the hood of the features as we build them, and therefore we cover XML metadata and code as opposed to what the design time experience will look like. We will have subsequent posts that cover the designer experience.

Background

Being able to model and use Unique Constraints is one of the top requests we hear from the community using Entity Framework. Like a primary key, a unique constraint is a set of properties on an entity whose values identify the entity as unique. Examples of unique constraints might be Social Security Number, Employee Number, Serial Number, etc. Unique constraints are often used to represent a natural and meaningful key, when a generated key is used as a primary key.

Most databases support unique constraints today.  Foreign key constraints can generally be based on any unique constraint (as opposed to only on the Primary Key) on the principal side.  

A key difference between a unique constraint and an entity key is that null values are allowed in columns that participate in the unique constraint.

Consider the following example. Here, I have an Employee entity type which includes an EmployeeId, as well as a Name and a Social Security Number.

The EmployeeId is the entity key, and it serves the purpose of uniquely identifying each Employee entity. However, I also have a SocialSecurityNumber property that can be used to uniquely identify the entity. This is a unique constriant, and today in the Entity Framework there is no way to specify that this is unique field.

This requirement itself is actually pretty self-explanatory, and we might as well say that what’s needed is a way to represent this “uniqueness” in the metadata that describes the entity data model. However, the fact that you can make assumptions about the uniqueness of a given property allows you to take advantage of that fact in interesting ways. For example, DDL generation could leverage this information to generate the corrsponding database constructs .


Taking this example further, let’s say I have the following entity data model.

In this case, I now have a few associations that are based on the EmployeeId. What I might actually want in this case is to build one of the associations using EmployeeId as the foreign key, but build the other association using the SocialSecurityNumber as the foreign key value – i.e. in this particular example, it might make sense to build my data model like this instead:


So what I’ve done here is that I’ve used the unique constriant on SocialSecurityNumber as the basis for the association between TaxWithholding and Employee. In this particular case, the SocialSecurityNumber property seems to be more appropriate and pertinent information defining the TaxWitholding entity type. It is also the perfect for acting as a “foreign key” to the parent Employee.  Because I have a first class association based on this constraint, I am also able to take advantage of the richness in the conceptual model – like being able to navigate the association using a Navigation Property.

Unique Constraints in the Model

Let’s take a look at CSDL metadata for Employee and TaxWithholding types, and the association between them:

<EntityType Name=Employee>

  <Key>

    <PropertyRef Name=EmployeeId />

  </Key>

  <UniqueConstraint Name=SocialSecurityNumber>

    <PropertyRef Name=SocialSecurityNumber></PropertyRef>

  </UniqueConstraint>

  <Property Type=Int32 Name=EmployeeId Nullable=false />

  <Property Type=String Name=Name Nullable=false />

  <Property Type=String Name=SocialSecurityNumber Nullable=false />

  <NavigationProperty Name=EmployeeDetail

                      Relationship=UniqueModel.EmployeeEmployeeDetail

                      FromRole=Employee ToRole=EmployeeDetail />

  <NavigationProperty Name=TaxWithholding

                      Relationship=UniqueModel.EmployeeTaxWithholding

                      FromRole=Employee ToRole=TaxWithholding />

</EntityType>

 

<EntityType Name=TaxWithholding>         

  <Key>

    <PropertyRef Name=SocialSecurityNumber />

  </Key>

  <Property Type=Int32 Name=SocialSecurityNumber Nullable=false />

  <Property Type=Int32 Name=NumberOfDependents Nullable=false />

  <NavigationProperty Name=Employee

                      Relationship=UniqueModel.EmployeeTaxWithholding

                      FromRole=TaxWithholding ToRole=Employee />         

</EntityType>

 

<Association Name=“FK_Employee_TaxWithholding>

  <End Type=Model.Employee

       Role=Employee

       Multiplicity=1 />

  <End Type=Model.TaxWithholding

       Role=TaxWithholding

       Multiplicity=0..1 />

  <ReferentialConstraint>

    <Principal Role=Employee>

      <PropertyRef Name=SocialSecurityNumber />

    </Principal>

    <Dependent Role=TaxWithholding>

      <PropertyRef Name=SocialSecurityNumber />

    </Dependent>

  </ReferentialConstraint>

 

</Association>

 

Foreign Key Associations may be based on a Unique Constraint on the principal end. The ReferentialConstraint element above shows exactly this scenario.

 

In order to represent unique constraints as they are defined in the store / database, Store Metadata (SSDL) also includes the ability to specify unique constraints as they are defined on the database schema. Here’s the corresponding SSDL for the above example:

 

<EntityType Name=Employees>

  <Key>

    <PropertyRef Name=EmployeeId />

  </Key>

  <UniqueConstraint Name=“SocialSecurityNumber>

    <PropertyRef Name=SocialSecurityNumber />

  </UniqueConstraint>

  <Property Name=EmployeeId Type=int Nullable=false />

  <Property Name=Name Type=nvarchar(max) Nullable=false />

  <Property Name=SocialSecurityNumber Type=nvarchar(max) Nullable=false />

</EntityType>

<EntityType Name=TaxWithholdings>

  <Key>

    <PropertyRef Name=EmployeeId />

  </Key>

  <Property Name=EmployeeId Type=int Nullable=false />

    <Property Name=SocialSecurityNumber Type=nvarchar(max) Nullable=false />

    <Property Name=NumberOfDependents Type=nvarchar(max) Nullable=false />

    <Property Name=NumberOfAllowances Type=nvarchar(max) Nullable=false />

</EntityType>

<Association Name=TaxWithholdingEmployee>

  <End Role=TaxWithholding

       Type=Store.TaxWithholdings Multiplicity=0..1 />

  <End Role=Employee Type=Store.Employees Multiplicity=1 />

  <ReferentialConstraint>

    <Principal Role=Employee>

      <PropertyRef Name=SocialSecurityNumber />

    </Principal>

    <Dependent Role=TaxWithholding>

      <PropertyRef Name=SocialSecurityNumber />

    </Dependent>

  </ReferentialConstraint>

</Association>

 

The details specified in SSDL are very similar to what is specified in the CSDL. A Unique Constraint in CSDL can map to a Unique constraint on the SSDL implicitly, based on how the columns are mapped in the MSL metadata. Nothing additional needs to be specified.

 

Here are some more details with regards to how Unique constraints can be specified:

  • In many ways unique constraints are similar to entity keys:
    • Unique constraints will be supported on Entity Type
    • More than one scalar property may compose a Unique constraint.
    • Server generated values can participate in a unique constraint
    • Navigation Properties may not participate in a unique constraint.
    • Associations can be based on unique constraints
  • However, unlike Entity Keys:
    • Unique constraints have names to identify them. These names should be unique within a type hierarchy.
    • Nullable properties may participate in unique constraints
  • A given property can participate in more than one unique constraint.
  • Properties participating in unique constraint may be mutable
  • Unique constraints may include properties from a base type

 

Support for OnUpdate cascading behavior

Unlike Primary Keys, Entity Framework will support mutability on properties that participate in a unique constraint.


Along with that, we plan on supporting OnUpdate cascading behavior.  This is something that is supported by databases. It simply allows you to specify that when the principal end of the relationship changes, the dependent end should be automatically updated accordingly as well.

Cascading is only handled for entities and relationships that are loaded into the object state manager. If you have OnUpdate Cascade behavior specified in the model, it will most certainly also make sense to make sure that the database itself has the same OnUpdate Cascade specification so that the database will manage the referential integrity of your data. This is equivalent to the OnDelete Cascade behavior supported today for associations based on primary keys.


This is how you can specify OnUpdate Cascade behavior:

<Association Name=TaxWithholdingEmployee>

  <End Role=TaxWithholding

       Type=Store.TaxWithholdings Multiplicity=0..1 />

  <End Role=Employee Type=Store.Employees Multiplicity=1>

     <OnUpdate Action=Cascade />

  </End>

  <ReferentialConstraint>

    <Principal Role=Employee>

      <PropertyRef Name=SocialSecurityNumber />

    </Principal>

    <Dependent Role=TaxWithholding>

      <PropertyRef Name=SocialSecurityNumber />

    </Dependent>

  </ReferentialConstraint>

</Association>

 

The default Action for OnUpdate is “None”.

What mapping scenarios are not supported?

We are still contemplating exactly what set of capabilities might not make it into the next release with regards to the unique constraint support– therefore your feedback on this will be of significant value to us.

As it stands right now, the following scenarios will *NOT* be supported:

  • Split Entities based on Unique Constraints – this is the scenario where a single entity is split across multiple tables on the database, where the split/join is based on a Unique Constraint instead of Primary Key.
  • Inheritance Support with Unique Constraints – Support mapping inheritance based on unique constraints as opposed to primary key 
  •  Independent Association based on Unique Constraints – This is the association type that was present in Entity Framework 3.5SP1 (as opposed to the Foreign Key Associations that we released in Entity Framework 4.0). In theory it is possible to enable Independent associations to store the foreign key values based on unique constraints but we are considering not supporting it because of the popularity of Foreign Key Associations as the standard way of representing associations.


We would like to know more about whether you need the following capabilities:

  • Associations based on unique constraints – Do you see the need to form associations using unique constraints where you are associating to a unique constraint on the principal end instead of the primary key on that entity? Is this something you see in existing/legacy databases?
  • Mutability of Unique Constraints – Mutability of unique constraints are important. However, if you are associating to a unique constraint on the principal end, how important is mutability in this scenario?

 
Once again, the purpose of this post is to find out how you’d like to see the feature built – so please feel free to tell us what your own preferences would be around this feature.


Please let us know what you think!


Faisal Mohamood – Program Manager, Entity Framework

Kati Iceva – Software Design Engineer, Entity Framework

Comments (44)

  1. Chris Hohmann says:

    I think this is certainly a step in the right direction, but what i would really like to see is mutable entity keys. Without this, Entity Framework is not suitable for any database that happens to use natural keys instead of surrogate keys. One glaring example of this is the Customers table in Microsoft's Northwind database.

  2. Remco Blok says:

    Although this article has screenshots of entities in the designer and examples of the xml metadata, can I assume the DbContext / Code First approach will also be supported?

  3. dpblogs says:

    Yes, we would support this in DbContext and Code First

  4. drasto says:

    The most important feature would of cause be that when entity specified using property that has unique constraint on and that entity will be found in context database will not be hit.

  5. rjfhendriks says:

    Hi, good the see some progress in this area. You are taking it far!

    Wat I really would like to see supported is the possibility to define an unique key on an N:M table with a PK ID and a UK on the 2 foreign keys to the PK's of the N and M table with other properties. We now parse the unique key database error according to it's type (Oracle / sql server) .. 🙂

  6. Bob Kolo says:

    I would love to see associations on unique constraints. We use these a fair amount in our production database where certain primary keys need to be kept private.

  7. Ben Vitale says:

    We absolutely need associations on unique constraints. That is quite prevalent in our legacy schema. The property on the principal end needs to be mutable to null and support cascade delete.

    Thanks.

  8. MolallaComm says:

    For code first, would you add a [Unique] meta data annotation or something similar?  Also, I agree with Ben on the mutable to null part, which makes the t-sql generated for the constraint a little less portable in that I believe only SQL2008 and later support filtered indexes which allow you to have more than one NULL row at a time (i.e. CREATE UNIQUE NONCLUSTERED INDEX [IX_GponOntPort_CircuitID_Unique] ON [dbo].[GponOntPort](CircuitID) WHERE CircuitID IS NOT NULL).

  9. Ken Smith says:

    You asked: "Do you see the need to form associations using unique constraints where you are associating to a unique constraint on the principal end instead of the primary key on that entity? Is this something you see in existing/legacy databases?"

    And the answer on my end, at least, is: Yes, this is very much something we would like to use in our databases, and can't now, because of this particular EF limitation.  Specifically, imagine a database that uses surrogate keys for all tables, and has the following hierarchy: User->Contact->ContactGroup<-Group<-User.  In other words, both the Group and the Contact tables belong to the User, and hence, it makes sense to allow a ContactGroup to join a Contact and a Group if they both belong to the same user.  The best way I've found to do model this is to add the UserId table to ContactGroup, add a unique key to Contact and to Group which includes the UserId, and add foreign keys from ContactGroup to Contact and Group which are based on their unique keys rather than their primary keys.

    That said, perhaps there's a better way to model this….?

  10. Simon Francesco says:

    +1 to associations to unique constraints. Also yes to mutability of these constraints. For example we have several FKs in our databases that link attributes of a transaction to the transaction line based on a composite unique key. We would hope to be able to change the transaction line, have the change cascade to the attribute rather than have to delete the attribute, change the transaction line and then create the new attribute.

    While these are underlying features, it is important to follow up new features with designer support and code generation. It is *painful* maintaining metadata by hand that is available from the database when it could be automatically handled eg defaults, validation, identity attributes, … they all need pushing through the SSDL and CSDL.

  11. Youhei Kondou says:

    There is a missing point for UNIQUE CONSTRAINT. The name is "unique constraint for multiple columns". It's important for environment in "Single primary key" convention. I use "unique constraint for multiple columns" instead of "primary keys for multiple columns".

  12. Preetham says:

    When is this feature going to be available? I want to build bi-directional one-one relationships and it can't happen without have unique constraints…

  13. Aaron says:

    Yes, we definitely have a need for candidate key relationships. This would be a huge addition for us in adopting Entity Framework as our core framework for data access.

  14. Surrogate or not, mutable primary keys are a bad idea.

  15. Associations based on unique constraints is definately a good idea but I can live without it. More important is having the ability to define a unique constraint.

  16. Matt says:

    +1 to associations to unique constraints. It's overly simplistic to expect an entity to have only one unique key to create associations on. Any potential timescales on this feature?

  17. David Sherwood says:

    I would hope with unique constraint support you would provide routines to check if a change or add would violate the constraint. Possible just another form of the DbSet find routine. It is necessary to check this before saving changes and I assume that since you are enforcing the unique constraint any way that you would have more efficient routines that just a general Linq query.

  18. Pete Mack says:

    I am confused about what release this will be in. (I am trying it in EF 4.2 with no luck.)

    This would be a huge win for us, since we often want our primary key to include a Date column in the clustered key for data (not metadata) tables for performance, but we wish to use only the Identity column for referential constraints.

    We are faking it now, by:

    * Leaving the Date field out of the primary key.

    * Updating the generated ddl so that the PK is NONCLUSTERED

    * Adding a Unique clustered constraint on (Date, Identity)

    Unfortunately, there's a nasty post-build step to update the PK declaration, as NONCLUSTERED is also not an EF attribute on indexes.

  19. Pete Mack says:

    Another comment on member attributes.

    We have another problem, in that would like to include user-friendly XML serialization in the same data model.

    We are doing it now with Code-First programming, but this falls down quickly for complex data models.

    We have a potential work-around: overload the "Long Description" to encode the additional attributes, and add any additional xml serialization fields to an external partial class declaration.

    This requires a new .tt file that parses Long Description, and it isn't at all pretty.

  20. Jonny says:

    I cannot believe you haven't already got Unique Constraint support.  You said "We’ve been busy working on enabling unique constraints in the Entity Framework", more like "oh sh*t, we forgot to add this important feature, we best get that fixed and rolled out ASAP!".  True to form, the Worlds biggest, but always one step behind the rest!  No wonder people are leaving the Microsoft fold for pastures new!  

  21. soham says:

    please support the creating of unique constraints already!

    any timelines when we could expect this feature?

  22. Joe says:

    I too am anxious for unique constraints. When?

  23. Jon says:

    I have an immediate need for "Associations based on unique constraints." I am working with a legacy database that was designed poorly and as a result I need it badly. Personally, I would be fine with only supporting linking to a primary key, but, since there are a lot of existing screwed up databases out there, it would be very helpful.

  24. Doug Clutter says:

    I just read a post on StackOverflow that said EF v5.0 will NOT support unique constraints.  That's really a shame.  As you illustrate so well in this posting, it is an extremely useful part of any ORM toolkit.  Please consider adding it to v5.0 before you ship.  Thanks.

  25. Sounds awesome.  Unique constraint support is greatly needed.  Please prioritize.

  26. Mert says:

    Unique constraint should be easily configured using Fluent API.

  27. Justin says:

    Suppose you've got a table that describes an ordered list of things. The table might have columns for ThingId, ThingTitle, and ThingPositionIndex, where ThingPositionIndex must be unique. Even if the Entity Framework supported unique constraints somehow, I doubt they'd be enforced properly; I don't think SQL Server can do it. If you attempted to swap the order of two things the submit would fail because the unique constraint index is checked after each row update. What you really need is some type of constraint checking that happens at the end of a transaction or some form of a multi-update/delete/insert.

  28. Jerome Haltom says:

    Any update on this? Every year or so for the last 4 years I've tried to migrate my database from NHibernate to EF… and despite there being an entirely new version each time, this is a stopper. =/

  29. Gabriel Vonlanten C. Lopes says:

    This is a must! I have stumped with this issue every now and then. Please consider adding this!

  30. Renato Lima says:

    Stuck at the same problem, I have a product in final development phase, and at this point we have no alternative solution. Is it planned to be implemented on EF6?

  31. Karl says:

    same problem, this is a very urgent issue in my development,, any chance to get in EF6?

  32. David says:

    Well this is one EF fanboy who just shot himself in the foot. Seriously MS? Get your act together!

  33. I am very disappointed to learn the EF5 doesn't support Unique constraints! This is such a heavily requested feature and so easily implemented! Why hasn't it been done yet!?!? If EntityFramework is to compete with behemoths like NHibernate, you MUST stay stay relevant and responsive to requests of your users… over 2 years to release an important feature like this is not acceptable…

  34. John Carroll says:

    Every release of entity framework seems to contain features that are of little, if any use, in real word scenarios.

    Something like unique constraints which a large portion of the use community wants is clearly not exotic enough for the developers

  35. Hans says:

    I use modeling tools now for 30 years. vs2012 is the first tool I ever found, that cannot model a unique constraint.  The tools of IBM and Oracle can do it.

  36. nick d says:

    Come on guys – it's 2013, where is this? [Unique] constraint data annotation is badly needed and very basic requirement.

  37. Chris says:

    Is this going to happen? EF 6 is just about released and this is a pretty big feature to leave out.

  38. Steve says:

    Can't see any evidence in the EF 6 documentation about this feature :(. Would be good to get some confirmation from MS about when we can expect this feature to appear.

  39. batpox says:

    It didn't make the cut again? Unbelievable!

  40. lucaster says:

    Still no unique constraint? YOU MUST BE F*INKTROLLING

  41. Tim says:

    Whhaaaaat! Surely this is a joke… Half a job yet again, thanks a bunch Microsoft.

  42. Thanmal says:

    Why can't someone from MS at least comment as to what is going on with this feature? Is it just ineptitude or complete ignorance? Please suggest is this feature request is dead or is there any timeline on this?

    This is such a basic and required feature where you need to associate by unique keys and not by primary keys?

  43. AxelD says:

    Here's my suggestion for implementing a UniqueKey annotation in Code First:

    entityframework.codeplex.com/…/572705

    In this thread I'm suggesting an annotation syntax for table indices, but the semantics should be the same for unique keys, as they may span several columns of a table.

    In addition to table unique keys, there should be a column UniqueKey data annotation, too, to simplify single column unique keys.

    In addition, foreign keys referencing unique keys could simply be set using the ForeignKey data annotation, added to then navigation property.

Skip to main content