locked
[VS Lightswitch 2012] No support for multiple-path composite keys; gives a reference integrity error RRS feed

  • Question

  • I came across an error recently that read that one of the relationships could not be created because one of the keys could cause a data integrity problem. There was no error in the database, because I did not set a CASCADE rule for the paths, which means that the values could not be edited anyway. Either way, Lightswitch did not like this and rejected one of the path relations when updating the datasource.

    It makes sense in a way, because a composite key could give problems if someone were to update the value and the cascade rule was not set correctly. (Here is an example of the relation: A->Z;  A->Y;  Z->B;  Y->B. Therefore A."Key" will have a problem on table B) I do prefer to use ID keys instead, but the previous developer did not create the system this way.

    When I tried to use a surrogate key using ID's instead, everything worked as intended. This is in VS2012 update 4.

    So my question then, is if I should go ahead and modify my database to use surrogate keys instead as a recommendation. And in light of this fact, it is probably safe to assume that composite keys are not recommended for use in Lightswitch data models?

    Thank you for your responses to this issue.

    Thursday, February 6, 2014 7:42 PM

Answers

  • I'm sorry for triple posting, but I think the problem is the entity framework being used.

    The reason my client wants to use composite keys is so that he can easily modify data in the database if he needs to, and would not be able to easily modify the data. Everyone else will be using the application.

    But after some research, it seems like anything before EF4 has this problem and throws an error in this circumstance. The thing is, it doesn't seem like there's any other way to represent the data so I am guessing that either ID's will need to be used or some relationships will have to be hacked together to be shown on the screen.

    The best solution in this scenario is to change the relationships to use surrogate keys to be able to have the relationship, or do something at the database level to have the information you need.
    • Edited by agmarkis Thursday, February 13, 2014 9:49 PM
    • Marked as answer by agmarkis Thursday, February 13, 2014 9:49 PM
    Thursday, February 13, 2014 8:33 PM

All replies

  • HI agmarkis

    Thanks for your feedback.

    According to your description above, it throws the error message when your use composite keys on your side. For this issue, it's recommended to enable diagnostics, it will give more details why this happens.

    1. In Solution Explorer, open the shortcut menu for the My Project node and choose Open.
    2. In the Application Designer, choose the Client Type tab, and then choose the Web option..
    3. On the menu bar, choose View, Other Windows, Output to display the Output window.
    4. On the menu bar, choose Debug,Start Debugging.
    5. In the address bar of the browser, add client tracing arguments to the end of the URL.

    If you want to use surrogate keys on your side, I don't have example available on my side.

    hope it helps,

    Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 10, 2014 8:45 AM
  • *sigh* I'm sorry but I don't think you understand when I am getting this error. I am getting the error while updating the datasource. As in, I make the changes in the database to have multiple pathways via composite keys, I click "Update Datasource", and while it tries to update, it gives an error that it is not able to use the composite key for the relationships.

    I switched everything into surrogate keys, and then it updated fine with the relationships.

    There is nothing I can do to debug if it is an error thrown in visual studio itself.

    Tuesday, February 11, 2014 5:45 PM
  • Has anyone else had this problem or could recreate it?

    I made a completely new project and narrowed down the tables to that were causing the problem and this is the error that it returned to me:

    Foreign key constraint 'FK_T_PE_List_T_CO' has been omitted from the

    storage model. Column 'PROJECT' of table 'Tbd.T_PE_List' is a

    foreign key participating in multiple relationships. A one-to-one

    Entity Model will not validate since data inconsistency is

    possible.


    I am assuming that it is impossible to use a composite key with multiple relationships? They are all One to Many relationships, so I don't know why it is complaining about a one-to-one.



    • Marked as answer by agmarkis Wednesday, February 12, 2014 11:10 PM
    • Unmarked as answer by agmarkis Thursday, February 13, 2014 8:17 PM
    • Edited by agmarkis Thursday, February 13, 2014 8:34 PM
    Wednesday, February 12, 2014 11:10 PM
  • I'm sorry for triple posting, but I think the problem is the entity framework being used.

    The reason my client wants to use composite keys is so that he can easily modify data in the database if he needs to, and would not be able to easily modify the data. Everyone else will be using the application.

    But after some research, it seems like anything before EF4 has this problem and throws an error in this circumstance. The thing is, it doesn't seem like there's any other way to represent the data so I am guessing that either ID's will need to be used or some relationships will have to be hacked together to be shown on the screen.

    The best solution in this scenario is to change the relationships to use surrogate keys to be able to have the relationship, or do something at the database level to have the information you need.
    • Edited by agmarkis Thursday, February 13, 2014 9:49 PM
    • Marked as answer by agmarkis Thursday, February 13, 2014 9:49 PM
    Thursday, February 13, 2014 8:33 PM