Deep in Sync: Handling PK-FK Constraints

  • Article

Roger Jennings in his most recent blog post asked an interesting question on how sync services deal with one-to-many relationship between tables undergoing synchronization? Roger is the most active blogger I know of and he is watching sync services closely. It is no secret that his blogs is one of my favorites. I check it at least once a day if not more! I cannot wait to see his upcoming article on sync services next May along with the test harness that he is working on.

Now back to the question, the one-to-many relationship in relational databases is implemented as PK-FK constraint. In my demos, I showed two tables but did not have PK-FK constraint established. The reason is that I wanted to do some rodom actions on the database and having such relation makes it rather complex (and I am lazy too ;-) ). I should consider fixing that in later demos though.

So let’s imagine for a minute that ‘order_details’ table has a foreign key that references ‘orders’ table’s primary key. Now,  how sync services deals with such setup and will it be able to synchronize the data without constraint failures? The quick answer is yes, sync services is capable of dealing with PK-FK relationships between tables. However, as you experienced from the demos, the sync framework is very open model and correct implementation is required to get sync to work properly. To achieve that you need to understand few basic sync concepts. Nothing new here since the demos have already shown all that but did not elaborate on why they are needed; now it is the time to dive deep in sync:

Grouping PK-FK Tables Together
The framework provides SyncGroup type for this very reason. With SyncGroup, you instruct the runtime to move changes of set of tables as one unit. This is particularly important when the tables have PK-FK relationships. This way, FK rows in one table will go with their PK rows in the other table.

Tracking Row Creation
Recall that in all the demos I added ‘create_timestamp’ column on every table along with ‘update_timestamp’. ‘create_timestamp’ is needed to indicate when the row was created. This is important to distinguish inserts from updates but more importantly it guarantees that a given row will be enumerated as an insert even if it is beeing updated very frequently. That said, hot rows won’t escape enumeration at least once. This is critical for PK rows because its is required for subsequent FK rows.

Change Application Order
As you can see, the two things I mentioned above guarantees that PK-FK changes will either come togather in one dataset or at least PK rows will come before their FKs. The only thing left is to ensure that PKs are inserted prior to FKs. For deletes, FKs are deleted first then PKs. This is done by the sync providers. On the client side, the order of SyncTable objects in the agent’s SyncTableCollection defines the order for applying inserts. On the server, the order of SyncAdapter objects in the provider SyncAdapterCollection defines the order for applying inserts. Deletes are applied in reverse order on both sides.

Hopefully, I was able to give some insights on how the framework handles PK-FK relationships. I would encourage you to experiment the scenarios you have in mind and share your feedback with the sync team through the forum.
 

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews . See examples: Top 10 Hotels in Myrtle Beach, Best Hotels in New York City, Romantic Hotels in Seattle , Hotels with a kitchen or kitchenette and Top 10 Hotels in San Francisco . Your feedback is welcome on twitter.com/raveable, raveable blog.

Comments

  • Anonymous
    March 24, 2007
    Rafik, See today's update to http://oakleafblog.blogspot.com/2007/03/sync-services-bidirectional-test.html re your post. After I get the body of the article finished, I'll probably try ROWGUIDCOL PK/FK combinations with OrderID an identity column (but not PK) on the server and an ordinary int on the client. Code can be refined for a month or more after the article deadline. Cheers, --rj

  • Anonymous
    June 01, 2008
    The comment has been removed

  • Anonymous
    June 06, 2008
    The comment has been removed