次の方法で共有

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

  • Anonymous
    March 12, 2011
    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.

  • Anonymous
    March 13, 2011
    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?

  • Anonymous
    March 14, 2011
    Yes, we would support this in DbContext and Code First

  • Anonymous
    March 23, 2011
    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.

  • Anonymous
    March 26, 2011
    The comment has been removed

  • Anonymous
    March 29, 2011
    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.

  • Anonymous
    April 04, 2011
    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.

  • Anonymous
    April 04, 2011
    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 WHERE CircuitID IS NOT NULL).

  • Anonymous
    April 04, 2011
    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....?

  • Anonymous
    April 11, 2011
    The comment has been removed

  • Anonymous
    May 24, 2011
    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".

  • Anonymous
    June 08, 2011
    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...

  • Anonymous
    June 14, 2011
    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.

  • Anonymous
    June 25, 2011
    Surrogate or not, mutable primary keys are a bad idea.

  • Anonymous
    July 08, 2011
    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.

  • Anonymous
    August 09, 2011
    +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?

  • Anonymous
    September 30, 2011
    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.

  • Anonymous
    November 10, 2011
    The comment has been removed

  • Anonymous
    November 10, 2011
    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.

  • Anonymous
    November 16, 2011
    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!  

  • Anonymous
    January 27, 2012
    please support the creating of unique constraints already! any timelines when we could expect this feature?

  • Anonymous
    February 10, 2012
    I too am anxious for unique constraints. When?

  • Anonymous
    February 25, 2012
    The comment has been removed

  • Anonymous
    March 15, 2012
    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.

  • Anonymous
    April 01, 2012
    Sounds awesome.  Unique constraint support is greatly needed.  Please prioritize.

  • Anonymous
    April 05, 2012
    Unique constraint should be easily configured using Fluent API.

  • Anonymous
    July 20, 2012
    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.

  • Anonymous
    January 26, 2013
    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. =/

  • Anonymous
    January 30, 2013
    This is a must! I have stumped with this issue every now and then. Please consider adding this!

  • Anonymous
    January 30, 2013
    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?

  • Anonymous
    February 05, 2013
    same problem, this is a very urgent issue in my development,, any chance to get in EF6?

  • Anonymous
    March 24, 2013
    Well this is one EF fanboy who just shot himself in the foot. Seriously MS? Get your act together!

  • Anonymous
    March 28, 2013
    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...

  • Anonymous
    April 13, 2013
    You can also use this (using DataAnnotations validation): blogs.microsoft.co.il/.../validationattribute-that-validates-a-unique-field-against-its-fellow-rows-in-the-database.aspx

  • Anonymous
    April 19, 2013
    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

  • Anonymous
    April 27, 2013
    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.

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

  • Anonymous
    June 13, 2013
    Is this going to happen? EF 6 is just about released and this is a pretty big feature to leave out.

  • Anonymous
    June 13, 2013
    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.

  • Anonymous
    November 16, 2013
    It didn't make the cut again? Unbelievable!

  • Anonymous
    December 18, 2013
    Still no unique constraint? YOU MUST BE F*INKTROLLING

  • Anonymous
    January 30, 2014
    Whhaaaaat! Surely this is a joke... Half a job yet again, thanks a bunch Microsoft.

  • Anonymous
    May 20, 2014
    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?

  • Anonymous
    November 24, 2014
    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.