How to have more than 1 many-to-many relationship

Question

My problem is simple but I can't seem to get EF to understand my database schema (Oracle).

Problem

I want to model more than 1 M-M relationship see diagram below:

As you can see I have a book and a journal - and I want to store a URI against both of these, by way of using a linking table (junction table, intersection table, you get it!). In this case it happens to be "www.google.com" that I want to store against Journal 16 and Book 75, they would be stored in the linking table in the follwoing format marked out in blue. For completeness I have added a green example for link with an ID of 5. So you can see how the data knits together, I think you'll agree it's nothing too crazy.

Anyway, when I try and update EF model, it complains (in Visual Studio) with an error along the lines of.

Problem in mapping fragments starting at line 210:Foreign key constraint 'REF_URI_JOURNAL_FK1' from table REF_URI (REF_ID) to table JOURNALs (REF_ID): The columns of table REF_URI are mapped to AssociationSet BOOK_URISet's End BOOKs but the key columns of table JOURNALs are not mapped to the keys of the EntitySet BOOKs corresponding to this End.

Yeh - not really getting that error message!
I can get EF to work with 1 M-M relationship, e.g.

and it all works "hunky dory", but when I added a second M-M relationship it fell over with that error message above e.g.

Anyone know how I can get round this?

Answer 1

For two many-to-many relationships your need two linking tables (junction tables, intersection tables) REFBOOK_URI and REFJOURNAL_URI. I think that is what the exception is saying in a cryptic fashion. If you only had one table it would mean that if Journal 123 links to URI 789 then also Book 123 must also link to URI 789 because the REF_ID column in the link table would be a non-nullable foreign key to both the Journal and the Book table.