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

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.

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.

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.

The entire compound key is being used in the foreign keys. UserSitePrivilege has two compound foreign keys: (UserId, SiteId) and (SiteId, PrivilegeId). But since it's an associative table, it is not represented as an entity.

A surrogate primary key on either table would solve the problem, but I'm hoping that I don't have to ask the DBA to change the DB design just to satisfy EF. – Michael L Perry Jun 17 '09 at 22:30.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.