none
[MSFx v2.1] best practice of handling the foreign-key constraint violation??? RRS feed

  • Question

  • Hi All,

    I am using v2.1 to synchronize between SQL 2008 and a set of SQLce. the scope contains a set of tables which a few FKs are defined (schema-wise in the server-side) among them.

     

    I would like to handle the FK confllict as follows:

    1) if the FK column value is not defined/existed in the lookup (parent) value; change the value to the default error number -1 (which is the valid record in the lookup table)

    2) to implement [Step 1] in the  serverProvider_ApplyChangeFailed with the DBApplyChangeFailedEventArgs Object

    3) After bi-directional sync is completed, the client will validate all the uploaded (either inserted or updated) row records FK columns value.  if any -1 values is found, that record will be marked as dirty and the user will be prompted which pieces of data need to be collected

     

    Issue : 

    A) Because in my application, the sync is for a batch of multiple offline-created records (i.e. the end-user requests no on-demand/sync-interrupted conflict-resolution (like the msdn article sample does). 

    B) Because there is no buit-in ConflictType for that so the only way to achieve [Step 2]  is to parse the sqlexception error text which i found cumbersome.

     

    Quick Fix :

    i could add  FK constraints to the table in the client SDFs; However, in reality while syncing back the the Server DB, there is always a chance the FK constraints is violated in the server DB  (well, I could argue that we could still do so by dis-allowing the delete/update of the lookup id records)

     

     

    Any comment/idea?

     



    --alex
    Tuesday, March 22, 2011 5:32 AM

All replies

  • your workaround above should work. just make sure its the Context in DBApplyChangeFailedEventArgs  that you're modifying. you can then do a retry to persist the updated row with your default value.
    Tuesday, March 22, 2011 7:24 AM
    Moderator
  • Hello Alex!
    I have a similar task in our project and we workaround this "Foreign Key" problem with "Disabling constraints -> Applying data  -> Enabling constraint" in one transaction. We do this on client (download to client) and server (when upload to server) side too. We use MS SQL Server 2008, SQL Compact 3.5 SP1 and Sync Framework 2.0.  
    Friday, March 25, 2011 6:20 AM