locked
Sync Framework Referential integrity problem RRS feed

  • Question

  • I have a master table header record with two child table Detail records that contain Foreign Keys to the header record.  In my scenario, deletes do not cascade to children, but instead the Foreign Key in a child is set to null when an Header record is deleted.  This all happens in one offline client session and the data is then saved to the local client Compact Framework 3.5 database.

     

    When the changes attempt to sync to the server, I get FK constraint errors, seemingly irrespective of the order of the Sync Tables in the ServerSyncProvider

     

    Somewhere I saw that Deletes are processed first (in reverse sync table order) before inserts/updates (in sync table order).  Is this the order:

     

    1.Detail table deletes

    2.Header table deletes

    3.Header table inserts/updates

    4.Detail table inserts/updates

     

    I can see that the FK error would result during step 2, because the updated Null FK's in the detail records would not be processed until step 4.  Is there a way around this conundrum, or am I missing something obvious here?

     

    Thanks in advance for ANY help you can give me... This Sync Framework is the greatest thing since sliced bread if I can just get smart enough to use it correctly!

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:00 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Sunday, April 13, 2008 1:31 AM

All replies

  •  

    Hi,

    are you using P2P or the sync service V1( hub-spoke cases ) ?

     

    the sync service for ado.net V1 doesn't support FK creation at the client db and user application was expected to create the desired FKs after the inital sync or during the inital sync with the SchemaCreated event.

     

    could you confirm if this is the case ?

     

    thanks

    Yunwen

    Sunday, April 13, 2008 5:01 PM
    Moderator
  •  

    I'm using the sync service for ado.net V2.

     

    I don't enforce Referential Integrity on the Client, because its possible to have Child FK's to parent objects that are not part of the current parent object table filter.  My code only allows manipulation of Child FK fields where the parent object is in the Client DB Parent object table.  So my Client database assumes any FK downloaded from the server is correct, even if the parent object referred to is not on the client.

     

    My problem is strictly a timing issue involving deleting a parent object during the same sync session as updating the Child Object FK's to another parent object or null.  If the order of posting the changes was the following, there would be no FK error:

     

    Desired 1.  Post changes to child object FK's to Null (or some other FK, if appropriate)

    Desired 2.  Post delete of parent object (which now has no linked child objects)

     

    Instead, the order seems to be:

     

    Actual 1.  Post delete of parent object (but child objects still linked)

    Actual 2.  Post changes to child object FK's to Null (or some other FK, if appropriate)

     

    SQL throws the FK violation during Actual 1.  My conflict handling continues after the error, so the child FK's are properly adjusted, but the parent object that was not deleted because of the error stays in the server database (it remains deleted in the Client DB, the desired state for the Client).  Complicating matters even more is that the change tracking logic prevents the Client from ever seeing the improperly "Not Deleted" parent object, since the client database timestamp only finds incremental changes after that timestamp.

     

    I understand that the Sync order is handled the way it is to properly process parent & child deletes in the same sync... If the order was my "desired" order above, that situation would throw an error.  Isn't there a way to handle both situations?

     

    I'm considering breaking my sync into 2 sessions, 1 handling all the updates and inserts, and a second to handle the deletes.  I think for this to work I would have to update any FK's to the proposed deleted records to null during the insert/update phase, then deleting them in the second phase.

     

    Should that work, or is there a better or simpler way?

    Monday, April 14, 2008 12:49 AM
  •  

    the sync service actualy will always process deletes first in the reversed or of sync adapters on the provider ( and then update/insert in the order of the sync adapters for those tables). Putting deletes to be send in one sync session is not easy ( unless you put some complex logic in your app ).

     

    I am curious, could this be resolved by removing the cascade action on your server tables ? so that when the PK rows were deleted, the client row will still there ?

     

    thanks

    Yunwen

    Sunday, April 20, 2008 12:18 AM
    Moderator