Many to Many Relationship, EDMX shows no primary key warning?

Question

I have a table Students and another one Subjects, and a third table StudentsSubjects , and the Students and Subjects table have a many-to-many relationship as you can see in the edmx diagram below.

However, the EDMX file in VisualStudio 2012:

doesn't show the StudentsSubjects table AND
shows a warning "StudentsSubjects" doesn't have a primary key defined but it was inferred.

enter image description here

I defined both StudentId and SubjectId in StudentSubject table as foreign keys only (not as primary keys as well). Am I doing it wrong? How do I fix the Visual studio warning above?

in addition to StudentId and SubjectId in StudentSubject Table, whey not add an Auto incremented int column e.g RecordID and make it an Identity column/PrimaryKey — StackTrace
@SQL.NETWarrior , thats interesting, is that standard/good practice? — iAteABug_And_iLiked_it

StuartLC StuartLC · Accepted Answer · 2013-10-20T14:53:15

The behaviour for your question #1 is by design - assuming that your junction table, StudentsSubjects has only the 2 columns, being the foreign keys StudentId and SubjectId, then EF will hide the junction table and instead provide one-to-many navigation from both Student and Subject. (Microsoft calls this a Pure Join Table).

For this EF N:N behaviour to work, no other columns are permitted in the junction table, not even a simple surrogate key (as suggested by @SQL.Net Warrior).

To avoid the warning in question #2, the primary key for the junction table would thus need to be the composite key (StudentId, SubjectId), i.e.

ALTER TABLE StudentsSubjects 
ADD CONSTRAINT PK_StudentsSubjects
PRIMARY KEY (StudentId, SubjectId);

See also How to set up a many-to-many relationship in Entity Framework designer thingy

Many to Many Relationship, EDMX shows no primary key warning?

1 Answers