How Referential Integrity Works

Article
05/22/2008

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):

<Key>

</Key>

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

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

</EntityType>

<Key>

</Key>

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

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

</EntityType>

< 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):

<Key>

< PropertyRefName = "Customer_ID" />

</Key>

…

</EntityType>

<Key>

< PropertyRefName = "Customer_ID" />

</Key>

…

</EntityType>

…

< PropertyRefName = "Customer_D" />

</Principal>

< 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);

Comments

Anonymous
May 22, 2008
PingBack from http://blog.xcalia.com/erix/2008/05/23/referential-integrity-constraints-in-ef/
Anonymous
May 22, 2008
There is a very interesting article about EF referential integrity here .
Anonymous
May 22, 2008
Je vous invite vivement à lire l' article suivant
Anonymous
May 24, 2008
For everybody who is interested on internals of Entity Framework (ER) in relation to referential integrity
Anonymous
May 31, 2008
Hi! When issue RTM version Entity Framework?
Anonymous
June 03, 2008
Hi, I have a scenario where I have a foreign key constraint, but it is an optional node in a hierarchy. To change your example slightly, you have an Order, a SubOrder and and OrderLine. Now an OrderLine could be under a SubOrder or could just be under an Order directly. Is it possible to have this sort of mapping with the Entity Framework?
Anonymous
June 13, 2008
Mirek Szymanski, a Developer on the Entity Framework Team, wrote this article on " How Referential
Anonymous
May 21, 2012
A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship. How do I debug which RIC was violated? The error message I'm getting above isn't very helpful.
Anonymous
April 01, 2014
referential integrity best for protection

Partager via

How Referential Integrity Works

Specifying referential integrity constraints

Affected operations

Additional notes

Examples

Comments

Ressources supplémentaires