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:

• Disable the referential integrity constraints before you synchronize, do your synchronization, and then turn the referential integrity constraints back on.
• Synchronize to a "staging" table in the database and then run some process on the desktop to move these changes between the "staging" database and the "real" database. This "staging" table, of course, would not have any integrity contraints.
• If your database allows it, set up triggers such that deletes "cascade". When cascaded deletes are turned on, deleting one of the CUSTOMERS will delete all of the corresponding ORDERS. Then, arrange it so Visual CE synchronizes the CUSTOMERS first (how to do this is described below).

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 Order

It isn't obvious, but it is possible to control the order in which Visual CE will synchronizes the tables. To specify this order:

1. In the windows folder of your desktop/server, there is a file called WCEODBC.INI. MAKE A BACKUP COPY OF THIS FILE
2. In the windows folder of your desktop/server, there is a file called WCEODBC.INI. Open this file using a text editor such as notepad.
3. In this file, there is a section for each table. Carefully cut and paste these sections and put them in the order you want the tables to be synchronized. When doing this:
   • Be sure that, when you are done, there is not two sections for the same table.
   • Be sure that, when you move a section, you move the entire section.
   • Be sure that, when you move a section, you don't move part of the next section.
   Note: You may see sections called [VICESYNC] and/or [VICEMNGR]. Just leave those where they are.
4. Save the file and close notepad.
5. Test your synchronization. If is does not work, restore the WCEODBC.INI from your backup and try again.