locked
Self-referencing Foreign Keys RRS feed

  • Question

  • On initial sync I have run into problems with self-referencing tables. I am syncing between MS SQL Server 2005 and SQLExpress 2005 using a custom sync provider.

     

    I can always drop the constraints if necessary, but on initial sync into an existing schema that already has the constraints in place, those inserts fail.

     

    Is there a way to guarantee that the parent in the relationship is inserted first?

     

    Here's a simplified example of what I'm trying to accomplish:

     

     

    Employee Table

    ID                                     Name         ManagerID

    3f14327a-f921-402b-b33d-bcea8c1bb221   Employee1    202b598a-f821-4f82-98e5-7440d70e367a

    202b598a-f821-4f82-98e5-7440d70e367a   Employee2    Null

    75e6396f-0b17-4333-96b7-47282b30099a   Employee3    Null

     

     

     

     

    I imagine the only way to fix this would be to correct the main problem with data coming over from a conversion being entered when there are no constraints on the table.

     

    I was going to just order the data by the ManagerID to insert the null ManagerIDs first, but there are at least 1 additional column with a foreign key constraint against the ID column.

    • Moved by Max Wang_1983 Wednesday, April 20, 2011 12:14 AM Forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, August 19, 2008 5:12 PM

Answers

  • if you can disable the table constraint and reenable it after the sync, then you may consider to disable it even before the applyChange occurs, this will avoid the applyCHangeFailed event to be fired and rows reinserted. --- better performance.

     

    just curious, are you using peer to peer sync ?

     

    thanks

    Yunwen

    Friday, August 22, 2008 8:32 AM
    Moderator

All replies

  • So far it looks like the easiest way to handle it is just use the ApplyChangeFailed event and RetryWithForceWrite.

     

    In the T-SQL for the insert for retry with force write, I just disable table constraints and re-enable after the insert. It's probably the best way to handle it.

     

    If anyone else has any thoughts, let me know.

    Wednesday, August 20, 2008 1:28 PM
  • if you can disable the table constraint and reenable it after the sync, then you may consider to disable it even before the applyChange occurs, this will avoid the applyCHangeFailed event to be fired and rows reinserted. --- better performance.

     

    just curious, are you using peer to peer sync ?

     

    thanks

    Yunwen

    Friday, August 22, 2008 8:32 AM
    Moderator
  • Thanks for the help. That's what I'll do.

    I just pulled up the Collaboration Scenarios section on peer-to-peer sync in the docs and must have never noticed it before. I'll have to check it out today.
    Friday, August 22, 2008 10:32 AM
  • I thought that it might make sense to add provide some info here.  The Sync Framework guarantees that tables will be synchronized in the order you specify within the sync adapter collection which, in turn, avoids violating foreign key constraint violations.  However, we do not grantee the ordering of rows within a table which has the potential to violate self-referencing FK constraints. It is certainly debatable as to whether or not we should.  Note that this ordering becomes extremely complex if I have more than one self-referencing constraint on the base table.  As Yunwen noted, the best practice would be to drop your self-referencing FK constraint priori to applying changes for each table so that it is only dropped and recreated once.  Feedback here is welcome.

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Friday, August 22, 2008 2:11 PM
    Moderator
  • Absolutely, I was doing this in the ApplyChangeFailed event to find out I was having trouble with the self-referencing constraints. In practice, I doubt self-referencing constraints will ever be an issue for me except during a new client's initial sync with data is converted from an aging, less-than-relational, database and bulk-loaded into an sql server database.

    Friday, August 22, 2008 4:07 PM
  • Hi Sean,

    I also have this problem and am considering disabling the foreign key during synchronization as you suggested above. I was wondering though whether using two scopes might be an option. The first could exclude the child column in the foreign key relationship while the second would include only this column. You would of course need to allow nulls or use a default value in the first scope.

    What do you think. Is there any merit to this approach? Is it even possible?

    Cheers,

    Scot
    MCSD.Net
    Wednesday, December 16, 2009 8:51 PM
  • The easiest way to handle this is in ApplyChangeFailed and then resolve as RetryNextSync.
    Managers will be synced first then employees.

    Thursday, December 17, 2009 7:49 PM
    Answerer