How Referential Integrity Works

Referential integrity is a database constraint that ensures consistency between coupled tables. Referential integrity holds if any field in a table that is declared as a foreign key contains only values from a parent (primary) table’s primary key.

Entity Framework (EF) supports referential integrity constraints (RIC). For example: it is possible to define model with entity types – Order, OrderLine and a constraint between them saying that one property of OrderLine is a foreign key with a value from related entity of type Order. In this case Order is called a principal entity and OrderLine is called dependent entity. EF enforces that entities of type OrderLine cannot exist without related entities of type Order.

Specifying referential integrity constraints

RICs are specified in the conceptual schema definition language (CSDL). Here is a sample model (fragment of CSDL file) with the definition of Order, OrderLine and a relationship with an RIC between them. Referential integrity constraint is defined by a “ReferentialConstraint” element in the definition of the “OrderOrderLine” association (in bold):

  <EntityType Name="Order">

    <Key>

      <PropertyRef Name="O_ID" />

    </Key>

    <Property Name="O_ID" Type="Int32" Nullable="false" />

    <Property Name="ShipCountry" Type="String" />

    <NavigationProperty Name="OrderLines" Relationship="Self.OrderOrderLine"

      FromRole="Order" ToRole="OrderLine" />

  </EntityType>

  <EntityType Name="OrderLine">

    <Key>

      <PropertyRef Name="Order_ID" />

      <PropertyRef Name="Product_ID" />

    </Key>

    <Property Name="Order_ID" Type="Int32" Nullable="false" />

    <Property Name="ProductID" Type="Int32" Nullable="false" />

    <Property Name="Quantity" Type="Int16" />

    <NavigationProperty Name="Order" Relationship="Self.OrderOrderLine"

      FromRole="OrderLine" ToRole="Order" />

  </EntityType>

  <Association Name="OrderOrderLine">

    <End Role="Order" Type="Self.Order" Multiplicity="1" />

    <End Role="OrderLine" Type="Self.OrderLine" Multiplicity="*" />

    < ReferentialConstraint >

      < PrincipalRole = "Order">

        < PropertyRefName = "O_ID" />

      </ Principal >

      < DependentRole = "OrderLine">

        < PropertyRefName = "Order_ID" />

      </ Dependent >

    </ ReferentialConstraint >

  </Association>

There is a restriction on an RIC definition that the properties participating in referential constraint have to form a complete key of the principal type.

Example: If the entity type Order has a composite key consists of the “O_ID” property and additionally the “Customer_ID” property, then the referential constraint having this type as a principal role must consists of the “Customer_ID” property as well (as a consequence the entity type OrderLine has to have “Customer_ID” as a part of its composite key):

  <EntityType Name="Order">

    <Key>

      <PropertyRef Name="O_ID" />

      < PropertyRefName = "Customer_ID" />

    </Key>

    …

  </EntityType>

  <EntityType Name="OrderLine">

    <Key>

      <PropertyRef Name="Order_ID" />

      < PropertyRefName = "Customer_ID" />

      <PropertyRef Name="Product_ID" />

    </Key>

    …

  </EntityType>

  <Association Name="OrderOrderLine">

    …

    <ReferentialConstraint>

      <Principal Role="Order">

        <PropertyRef Name="O_ID" />

        < PropertyRefName = "Customer_D" />

      </Principal>

      <Dependent Role="OrderLine">

        <PropertyRef Name="Order_ID" />

        < PropertyRefName = "Customer_ID" />

      </Dependent>

    </ReferentialConstraint>

  </Association>

Scenarios with circular references are not supported. For example: EF does not support the scenario in which there are relationships Client-Order, Order-OrderLine, OrderLine-Client and all the relationships have referential constraints (Client can only exist with Order, Order can only exist with OrderLine, OrderLine can exist only with Client).

Affected operations

Having referential constraints in your conceptual schema affects following operations:

1. Calling ObjectContext.SaveChanges(). In this case two things happen:

a. Propagation of properties.

For every entity which is in the Added state and which is a dependent end of some RIC, principal entity has to be found. Then values of properties which are part of RIC are propagated from principal to dependent entity.

If there is no principal entity found – InvalidOperationException is thrown.

NOTE: Values of properties on the dependent entity are overridden.

 

Example (using model as above): If there is an entity of type OrderLine in the ObjectContext and the entity is in the added state, Entity Framework will find related Order and copy value of O_ID property from Order into Order_ID property in OrderLine.

Old value of Order_ID property in OrderLine entity is discarded.

 

b. Checking consistency of properties. Basically values of properties which are part of an RIC are verified so they match.

2. Deleting entity or relationship.

a. If the principal entity is deleted, all related dependent entities are deleted as well.

b. If a relationship between a principal and dependent entity is deleted – the dependent entity is deleted as well.

NOTE: Behavior “a” is the same as if cascade delete was defined on the relationship.

3. Changing an existing relationship between principal and dependent entity.

a. If a dependent entity has a fixed key (the entity is not in the Added state) an attempt to change its relationship with principal entity to another entity will fail and will result in throwing InvalidOperationException.

 

Example: Assume there is an entity of type OrderLine and entity of type Order, both entities are related and OrderLine entity is in the Unchanged. OrderLine cannot be related to other entity of type Order because this would change identity of OrderLine.

b. If dependent entity has a temporary key (the entity is in the Added state, changing its relationship with principal entity to another entity will result in deleting the dependent entity and then adding the entity again. It happens because to establish the new relationship, the old relationship has to be deleted so the dependent and also gets deleted and then re-added – see point 2.b. above.

NOTE: deleting entities and relationships in Added state results in detaching them from the context.

 

4. Calling ObjectContext.Attach() or ObjectContext.AttachTo(). In this case verification of RIC properties is performed – values of properties in principal and dependent entity must match.

NOTE: if the entity being attached is not related to its principal entity, validation succeeds.

 

Example: If entities of types Order and OrderLine are related, their Order_ID properties must have the same value. If entity of type OrderLine is being attached and it is not related to any entity of type Order, attach will succeed.

Additional notes

1. It’s possible to have multi level referential constraints. For example – having entity types Client, Order and OrderLine it’s possible to define relationships with RIC between both Client-Order and Order-OrderLine. Remember that in this case deleting Client or relationship between Client and Order will cause deletion of related Orders and OrderLines.

2. It’s possible for an entity type to be dependent (or principal) end of more than one relationship with referential constraint.

3. Changing existing relationships between may have some not obvious consequences.

Assume there are entity types Client, Order, OrderLine, relationships Client-Order and Order-OrderLine. Both relationships have referential constraints.

Client client1 = new Client();

Client client2 = new Client();

Order order1 = new Order();

OrderLine orderLine1 = new OrderLine();

client1.Orders.Add(order1);

order1.OrderLines.Add(orderLine1);

context.AddObject("Clients", client1);

context.AddObject("Clients", client2);

// reassign order1

order1.Client = client2; // (*)

 

As I described earlier removing relationship results in removing dependent end of relationship as well. In this case it means that line (*) results in following operations:

a. relationship client1-order1 is deleted

b. order1 is deleted

c. all entities for which order1 was a principal entity are deleted (in this case orderLine1 is dependent and it gets detached because it is in the Added state, relationship between order1 and orderLine1 gets removed as well)

d. order1 is re-added to the ObjectContext (note this it also means that a new ObjectStateEntry is created for this entity)

e. relationship between client2 and order1 is added.

Examples

Example 1:

OrderLine orderLine1 = new OrderLine();

context.AddObject("OrderLines", orderLine1);

context.SaveChanges(); // this will throw because orderLine1 is

// a dependent entity and it’s not related

// to any Order

Example 2:

Order order1 = new Order();

order1.O_ID = 3;

OrderLine orderLine1 = new OrderLine();

orderLine1.Order_ID = 5; // this value does not matter,

// it will be overriden in SaveChanges()

// define relationship

orderLine1.Order = order1;

// add the graph to the context

context.AddObject("OrderLines", orderLine1);

context.SaveChanges(); // this will succeed,

// Order_ID property will be propagated

Debug.Assert(orderLine1.Order_ID == 3, "property not propagated");

 

Example 3:

OrderLine orderLine1 = new OrderLine();

// if the graph is incomplete (entity doesn't have a pricipal entity related),

// we assume that the key has proper values (in terms of RI Constraints)

context.AttachTo("Order", orderLine1);