Referential integrity and synchronization

All MobiLink clients, with the exception of UltraLiteJ, enforce referential integrity when they incorporate the download into
the remote database.

Rather than failing the download transaction, by default the MobiLink client automatically deletes all rows that violate referential
integrity.

This feature has the following benefits:

Protection from mistakes in your synchronization scripts. Given the flexibility of the scripts, it is possible to accidentally
download rows that would break the integrity of the remote database. The MobiLink client automatically maintains referential
integrity without requiring intervention.

You can use this referential integrity mechanism to delete information from a remote database efficiently. By only sending
a delete to a parent record, the MobiLink client removes all the child records automatically for you. This can greatly reduce
the amount of traffic MobiLink must send to the remote database.

MobiLink clients provide notification if they have to explicitly delete rows to maintain referential integrity, as follows:

For SQL Anywhere clients, dbmlsync writes an entry in the log. There are also dbmlsync event hooks that you can use. See:

For UltraLite clients, the warning SQLE_ROW_DELETED_TO_MAINTAIN_REFERENTIAL_INTEGRITY is raised. This warning takes a parameter
which is the table name. To maintain referential integrity, the warning is raised on every row that is deleted. Your application
can ignore the warnings if you want synchronization to proceed. If you want to explicitly handle the warnings, you can use
the error callback function to trap them and, for example, count the number of rows that are deleted.

If you want synchronization to fail when the warning is raised, you must implement a synchronization observer and then signal
the observer (perhaps through a global variable) from the error callback function. In this case, synchronization fails on
the next call to the observer.

Referential integrity checked at the end of the transaction

The MobiLink client incorporates changes from the download in a single transaction. To offer more flexibility, referential
integrity checking occurs at the end of this transaction. Because checking is delayed, the database may temporarily pass through
states where referential integrity is violated. This is because rows that violate referential integrity are automatically
removed before the download is committed.

Example

Suppose that an UltraLite sales application contains the following two tables. One table contains sales orders. Another table
contains items that were sold in each order. They have the following relationship:

If you use the download_delete_cursor for the sales_order table to delete an order, the default referential integrity mechanism
automatically deletes all rows in the sales_order_items table that point to the deleted sales order.

This arrangement has the following advantages:

You do not require a sales_order_items table script because rows from this table are deleted automatically.

The efficiency of synchronization is improved. You need not download rows to delete from the sales_order_items table. If each
sales order contains many items, the performance improves because the download is now smaller. This technique is particularly
valuable when using slow communication methods.

Changing the default behavior

For SQL Anywhere clients, you can use the sp_hook_dbmlsync_download_ri_violation client event hook to handle the referential
integrity violation. Dbmlsync also writes an entry to its log.