locked
Foreign Key woahs RRS feed

  • Question

  • User977421240 posted

    I'm wrestling here with an issue in my database that I can't get worked out no matter what I do.  I'm trying to establish a hierarchical link between 4 tables based off of 3 sets of fields. In each case the relationship is 1 to Many. I'm trying to get this nailed down to be imported into a Database first EF model and I'm dead in the water at the moment.  Fist the architecture of the db tables and fields.


    Layout


    Table 1:                  Table 2:                                                   Table 3:                  Table 4:

    --ID (PK)                -ID(PK) *different ID from table 1            IDFieldA (PK)  1------m  IDFieldA
      FieldA 1--------m FieldA                                                                                             IDFieldB (PK)
                                   FieldB 1--------------------------------m FieldB


    Now I the error that I'm running into is "There are no primary or candidate keys in the referenced table".  Now because these are all 1 to many relationships I've tired clustering with the Primary key field in the table to but that doesn't work either and I need to be able to establish my relationship before building my model from the database.

    Any thoughts?

    Regards,

    Kip...

    Wednesday, March 30, 2016 7:20 PM

All replies

  • User-219423983 posted

    Hi Ken Carter,

    I'm trying to get this nailed down to be imported into a Database first EF model and I'm dead in the water at the moment. 

    Do you mean that the error happens when you want to build EF models based on the existing database? If so, could your show the detailed error information? Or, you’d better clarify when the error happens.

    the error that I'm running into is "There are no primary or candidate keys in the referenced table". 

    I need to be able to establish my relationship before building my model from the database.

    According to this statement, I guess the error happens in SQL. If not, please let me know.

    If the error happens when you build the table, you could first refer to below thread which could give you some inspirations for your issues and then you could share your SQL code.

    http://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re

    About the EF database first, you could have a look at below video to learn how to implement it and then compare with your project to make sure your actions are right.

    https://msdn.microsoft.com/en-us/data/jj591506.aspx

    Best Regards,

    Weibo Zhang

    Thursday, March 31, 2016 2:29 AM
  • User977421240 posted

    No this was before building the model in EF. I was trying to established the relationships in prep to pull it in.  I finally gave up because I am not doing any joins or actually jumping from one table to the next but only looking up one reference in each subsequent table to drill down 3 layers and maintain the makers in session variables, I would just address each table independently and give up on the foreign keys.

    I'm sill interested in an answer should someone come up with an idea that works.

    Thanks,

    Kip (Ken) 

    Friday, April 1, 2016 3:02 PM
  • User-219423983 posted

    Hi Ken Carter,

    No this was before building the model in EF. I was trying to established the relationships in prep to pull it in.  I finally gave up because I am not doing any joins or actually jumping from one table to the next but only looking up one reference in each subsequent table to drill down 3 layers and maintain the makers in session variables, I would just address each table independently and give up on the foreign keys.

    Before building the foreign relationship between two tables, you should first have a look at below link that’s about the limitations and restrictions. As it says, when the referenced column is not a primary key, it should have an UNIQUE constraint on it. So, you’d better first make sure the “FieldA” and “FieldB” have the UNIQUE constraints

    https://msdn.microsoft.com/en-us/library/ms189049.aspx?f=255&mspperror=-2147217396#Anchor_0

    Besides, the following link provides a GUI explanation about creating Tables Relationship and you could have a look.

    http://www.connectsql.com/2011/01/sql-server-quickest-way-to-create.html

    Best Regards,

    Weibo Zhang

    Thursday, April 7, 2016 6:13 AM