I have a parent and child table. We just noticed that there are cases that the download_delete cursor of the parent says it should delete records while the download_delete cursor of child records says it should not.
This is offcourse not right, and I was looking in to changing the download_delete cursor of the child table so that it complies with the parent table.
But to my astonishment I noticed that the child and parent records are in fact deleted from the remote database whenever parent records are deleted even though there is a foreign key constraint (I was expecting an exception or error). I checked in Sybase central (of the remote db) to see if the constrains has a cascade delete but the Delete action is "Not permitted".
another odd thing I noticed was when I was checking the logs of the remote database. I saw that the parent record was FIRST removed and then the Child record was removed (all within 1 and the same synchronization but still)...
How is this possible?
> How is this possible?
It is a feature! Seriously! In order to serve you better, the dbmlsync client silently deletes rows that get in the way of a smooth download!
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.
As punishment for not accepting the default behavior, you are forced to learn about sp_hook procedures and write a bunch of code...
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. See: sp_hook_dbmlsync_download_log_ri_violation sp_hook_dbmlsync_download_ri_violation
Most people ( well, me :) find it easier to restructure their affairs ( i.e., their schema and/or application ) to avoid this feature altogether; i.e., go along to get along.