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