Tip of the Month (August 2007)
Database Referential Integrity Constraints and the Synchronizer
Desktop/server databases with referential integrity constraints require special consideration when using our synchronizer. Say you have two tables: CUSTOMERS and ORDERS. When inserting records, you have to be sure the synchronizer inserts the CUSTOMERS first and then the ORDERS. But when deleting records, you have to be sure the synchronizer deletes the ORDERS first and then the CUSTOMERS.
When using the Visual CE synchronizer in the general case (i.e., where you are doing both insertions and deletions), you have to deal with this referential integrity constraint problem. The Visual CE synchronizer synchronizes one table completely before going on to the next table. This is problematic since there is no one ordering of the tables that will allow the synchronizer to both insert and delete records.
You have a few options:
Of course, you may not need this general case (i.e., where you are doing both insertions and deletions). You may only be inserting records on the handheld (in which case, you just have to be sure that the CUSTOMERS are synchronized before the ORDERS are). Or, you may only be deleting records on the handheld (in which case, you just have to be sure that the ORDERS are synchronized before the CUSTOMERS are). In these cases, the synchronization will work as long as the tables are synchronized in the correct order.
Synchronizing Tables in a Specific OrderIt isn't obvious, but it is possible to control the order in which Visual CE will synchronizes the tables. To specify this order: