locked
Order of deletion in multi-table sync scope, and foreign key constraint error RRS feed

  • Question

  • This is a SqlServer to SqlServer system, data flows both ways but is constrained by filter so that a given row will only go in one direction.

    The program performs the provisioning in a separate sequence, generally once, not every time as in most samples.  I am not programmatically creating adapters, but instead use the SqlSyncProvider class that takes a scope name and builds the adapters from the known configuration.  When I look at the scope_info and scope_config data, they correctly reflect the provisioning.  Drilling in to the config_data on the scope reveals the adapters are ordered properly such that parent tables appear before child tables.  This is the case on both servers (they are exactly the same, the only difference being the filter information on the select_changes stored procedures).

    Inserting complex records (where there are many inter-table relationships within the scope) works great ... everything sync's in the correct order and their are no problems.  My issue is on the delete side.  It appears to me that despite the documentation indicating that the adapters will process in reverse order for deletion, that isn't happening.  Parent tables are being processed before child tables, and foreign key constraints are being triggered as a result.

    FYI, our constraints do not cascade deletes. 

    I have used Sql Profiler to verify this behavior.  On the one server, I delete a group of records in a single transaction in the proper order.  I can see the triggers updating the tracking data (in this case, setting the tombstone flag).  Then the sync occurs, and the changes are fetched and processed.  I can see this happening too, and as I said, I can see the tables being processed in the wrong order (pretty much backwards of the way they were processed on the originating server), resulting in errors.

    I'll add one final detail:  The server that I originated the change on is a hub, theoretically synchronizing with multiple satellites.  I never call the SyncOrchestrator.Synchronize() method on this server.  I always have the other server perform the synchronization.  It receives a notification to do so, and then processes two synchronization requests:  First with sync direction = Download, then with SyncDirection = upload.

    I appreciate any and all help!

    -Kevin

    Thursday, June 24, 2010 4:14 PM

All replies

  • Hey Kevin,

    Could you please specify the filter you use and how it makes sure that a given row will only go in one direction? 

    Thanks,

    Ann 


    Ann Tang
    Thursday, June 24, 2010 7:19 PM
  • Hi Ann,

    I am happy to answer, especially if it helps, but this isn't a simple answer.

    Personnel and PersonnelUDF are two tables in the scope I describe in the OP.  Both have an ObjectId (int) for primary key.  PersonnelUDF has a foreign key to Personnel.  This is a 1-1 relationship (PersonnelUDF is an extension table, but I don't want to get in to the reason as it is off topic).

    Anyway, our system has a concept of Partition (identified by ID in a table), and ApplicationServer (also identified by ID in a table).  A given ApplicationServer is responsible for n Partitions.  The filtering is based on the partitions the ApplicationServer is responsible for.

    Each record in the Personnel table has an associated PartitionID.  Hence the filter for Personnel that I specify is:

     ( [side].PartitionID in (8388608) and ( ([side].ObjectID > 5000) or ([side].PartitionID in (2097152001) ) ) )

    And that means that the record's partitionID is in that owned by the ApplicationServer (which is the 2097152001 value), or is a record we want to go everywhere (PartitionID=8388608 and ID>5000).

    PersonnelUDF does not itself have a PartitionID column, so the filter used for this object is even more complex, but the purpose is the same.  In this case, the filter will travel the foreign key dependancy and select records where the parent record meets the same test described above.  So this looks like:

      ( [side].ObjectID in (select ObjectID from Personnel where PartitionID in (8388608)))  and (([side].ObjectID > 5000)  or ( [side].ObjectID in (select ObjectID from Personnel where PartitionID in (2097152001)))  ))

    Thanks again for any help you can provide,

    -Kevin

    Thursday, June 24, 2010 8:41 PM
  • So was this information useful in formulating some help or am I on my own?
    Monday, June 28, 2010 8:04 PM