Proper way to sync a self-referenced table? RRS feed

  • Question

  • I tried to sync a self referencing table and got the following error:

    Apply change failed! <br/>
    System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_MyTable_MyTable" . The conflict occurred in database "MyDB" , table "dbo.MyTable" , column 'MyTableId' .

    So, my question is , what is the proper way to sync a self referencing table?

    My first approach would be to try split the job in two: sync first the parent rows, then commit, and then the dependent rows and commit. Would this work if I assign the same table in two different sync groups applying different filter per group?

    My second idea is to sort the rows in the server before they get sent to the client, putting the independent ones first and then the rest. The row insertion at the client would follow the same order.

    Thanks a lot
    C# + BizTalk2009 Developer
    Thursday, November 19, 2009 12:03 AM


  • I started by trying my second idea above and it worked ok.

    The reason it worked was that a parent row, as per business logic, cannot be in turn child of any other. The solution I had wont work if, for example, one of the rows that is a child one is inserted when its parent hasn't been yet.

    C# + BizTalk2009 Developer
    Thursday, November 19, 2009 3:43 PM