Merge replication server side foreign key violation from unpublished table
- by Reiste
We are using SQL Server 2005 Merge Replication with SQL CE 3.5 clients.
We are using partitions with filtering for the separate subscriptions, and nHibernate for the ORM mapping. There is automatic ID range management from SQL Server for the subscriptions.
We have a table, Item, and a table with a foreign key to Item - ItemHistory. Both of these are replicated down, filtered according to the subscription.
Item has a column called UserId, and is filtered per subscription with this filter:
WHERE UserId IN
(SELECT... [complicated subselect]...)
ItemHistory hangs off Item in the publication filter articles.
On the server, we have a table ItemHistoryExport, which has a foreign key to ItemHistory. ItemHistoryExport is not published.
Entries in the Item and ItemHistory tables are never deleted, on the server or the client. However, the "ownership" of items (and hence their ItemHistories) MAY change, which causes them to be moved from one client subscription/partition to another from time to time.
When we sync, we occasionally get the following error:
A row delete at '48269404 - 4108383dbb11' could not be propagated to 'MyServer\MyInstance.MyDatabase'. This failure can be caused by a constraint violation. The DELETE statement conflicted with the REFERENCE constraint "FK_ItemHistoryExport_ItemHistory". The conflict occurred in database "MyDatabase", table "dbo.ItemHistoryExport", column 'ItemHistoryId'.
Can anyone help us understand why this happens? There shouldn't ever be a delete happening on the server side.
