Entity Framework Hell – “Referential integrity constraint violation. A Dependent Role has multiple principals with different values.”

A few weeks back, without any new code changes, an application that I was working on started throwing strange Entity Framework exceptions when users tried to save new data records. The specific exception message was:

Referential integrity constraint violation. A Dependent Role has multiple principals with different values.

I fired up Google and tried to find some information on this exception message, and couldn’t find ONE article that provided a clear explanation of the root issue.  I spent a full day debugging the application trying to understand which data values were causing the issue. In the end, I downloaded the Entity Framework 6 source code from CodePlex, added the EF projects to my application solution, replaced all the EF NuGet package references with project references, and debugged the application right into the EF source code where the exception was being thrown (the stack trace on the exception message told me exactly where the exception was coming from). After 20 minutes of debugging I pinpointed the root cause – bad data introduced a few days earlier during a data-refresh process, highlighting a design-flaw in the database schema.

Consider the following superhero-themed database with two tables, Game and Team, which records the score of games between superhero teams.

The Game table has two composite-value foreign keys:

  • FK_Game_Team_HomeTeam (Division & HomeTeamCode)
  • FK_Game_Team_AwayTeam (Division & AwayTeamCode)

The schema works fine assuming you only intend to record games between teams in the same division. However, consider the sample code below:

This code will throw an EF exception

As soon as you try to add a game for teams from different divisions, you get the funky exception message “A Dependent Role has multiple principals with different values.”  When EF tries to fix-up the foreign keys for the two object properties HomeTeam and AwayTeam, it catches the conflicting Division property values – first it tries to set Division=1 for the AwayTeam object property, and then it tries to set Division=2 for the HomeTeam object property (Game is the Dependent Entity and Team is the Principal Entity, and the different principal values are the values for Division).  EF can’t set the Division property value to two different values, and so throws the exception.

Case solved.

If you come across this exception message in the course of your own development, check your schema for tables containing multiple composite-value foreign-key relationships where two or more relationships use the same database field.