In Entity Framework, how can I create a referrential constraint using a subset of the primary key?

Question

My data model contains two tables with composite primary keys and an associative table. Part of the composite primary key is common between the tables.

SitePrivilege
-------------
SiteId
PrivilegeId

UserSite
--------
SiteId
UserId

UserSitePrivilege
-----------------
UserId
SiteId
PrivilegeId

I have created a SitePrivilege entity and a UserSite entity. I have mapped a many-to-many association between them to UserSitePrivilege.

<Association Name="UserSiteSitePrivilege">
  <End Type="PrivilegeModel.UserSite" Multiplicity="*" Role="UserSite" />
  <End Type="PrivilegeModel.SitePrivilege" Multiplicity="*" Role="SitePrivilege" />
</Association>
...
<AssociationSetMapping Name="UserSiteSitePrivilege" TypeName="PrivilegeModel.UserSiteSitePrivilege" StoreEntitySet="UserSitePrivilege">
  <EndProperty Name="SitePrivilege">
    <ScalarProperty Name="PrivilegeId" ColumnName="PrivilegeId" />
    <ScalarProperty Name="SiteId" ColumnName="SiteId" />
  </EndProperty>
  <EndProperty Name="UserSite">
    <ScalarProperty Name="SiteId" ColumnName="SiteId" />
    <ScalarProperty Name="UserId" ColumnName="UserId" />
  </EndProperty>
</AssociationSetMapping>

The above code produces this error:

Each of the following columns in table UserSitePrivilege is mapped to multiple conceptual side properties: UserSitePrivilege.SiteId is mapped to UserSiteSitePrivilegeSitePrivilege.SiteId, UserSiteSitePrivilege.UserSite.SiteId

So I added a referential constraint.

<Association Name="UserSiteSitePrivilege">
  <End Type="PrivilegeModel.UserSite" Multiplicity="*" Role="UserSite" />
  <End Type="PrivilegeModel.SitePrivilege" Multiplicity="*" Role="SitePrivilege" />
  <ReferentialConstraint>
    <Principal Role="UserSite">
      <PropertyRef Name="SiteId"/>
    </Principal>
    <Dependent Role="SitePrivilege">
      <PropertyRef Name="SiteId"/>
    </Dependent>
  </ReferentialConstraint>
</Association>
...
<AssociationSetMapping Name="UserSiteSitePrivilege" TypeName="PrivilegeModel.UserSiteSitePrivilege" StoreEntitySet="UserSitePrivilege">
  <EndProperty Name="SitePrivilege">
    <ScalarProperty Name="PrivilegeId" ColumnName="PrivilegeId" />
    <ScalarProperty Name="SiteId" ColumnName="SiteId" />
  </EndProperty>
  <EndProperty Name="UserSite">
    <ScalarProperty Name="SiteId" ColumnName="SiteId" />
    <ScalarProperty Name="UserId" ColumnName="UserId" />
  </EndProperty>
</AssociationSetMapping>

Now it produces this error:

Properties referred by the Principal Role UserSite must be exactly identical to the key of the EntityType PrivilegeModel.UserSite referred to by the Principal Role in the relationship constraint for Relationship PrivilegeModel.UserSiteSitePrivilege. Make sure all the key properties are specified in the Principal Role.

How do I correctly model this relationship?

Answer 1

If your primary key is a compound key, all your foreign key relationships must also be using the entire compound key (all key columns) for reference - I don't see any way to get around this, this is a basic tenet of relational database design, really.

This is definitely one of the major reasons I would probably choose to use a substitute column on the main table for the primary key, instead of a compound key made up from actual data columns.

UPDATE: yes, based on your comment, you're absolutely right - the DB design is solid. Not quite sure why EF can't deal with this....

Marc

Answer 2

Overlapping FKs like this are not supported in 3.5 SP1.

i.e.

UserSitePrivilege
----------
UserId 
SiteId
PrivilegeId

PK => UserId, SitedId, PrivilegeId
FK1 => UserId, SiteId
FK2 => SiteId, PrivilegeId

FK1 overlaps with FK2. This is going to be supported as of Beta2 of EF 4. This is because FK Associations (which is available in Beta2) are much more flexible than Independent Associations (what you have in 3.5 SP1 and 4.0 Beta 1).

See this post for more on FK Associations

In the meantime your only option is probably to hide all of this behind DefiningQueries and CUD procedures etc.

• Alex