PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload (307482)

SYMPTOMS

When there are foreign key constraints at a Merge Replication Subscriber that are not marked as NOT FOR REPLICATION, if a foreign key constraint violation occurs due to an INSERT that is sent by the Publisher, a conflict is logged at the Publisher (if centralized conflict reporting is enabled for the publication), and the associated Publisher row is deleted during the next upload of changes from the Subscriber.

The text of the conflict that is logged is similar to:

The row was inserted at 'Publisher' but could not be inserted at 'Subscriber'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__T2__C1__79A81403'. The conflict occurred in database 'SubDB', table 'T1', column 'C1'.

The conflict table for the problem article should have an entry for the conflict with a reason_code of 547 and a conflict_type of 6. This conflict_type indicates that a conflict occurred while downloading the insert to the Subscriber. The reason_code is the native SQL Server error number that corresponds to a foreign key constraint violation.

During a subsequent merge with this Subscriber, the problem row is deleted from the Publisher database. Deletion of the row results in a row in the MSmerge_tombstone system table with a reason of "system delete."

CAUSE

Although this behavior may seem undesirable, the merge replication design favors data convergence over transactional consistency. If the Publisher row that prevented the Subscriber foreign key conflict is not deleted during the next merge upload, the data between the Publisher and the Subscriber is non-convergent. That is, a row exists at the publisher that does not exist at the Subscriber.

RESOLUTION

You can resolve this problem in the following ways:

Mark the subscriber foreign key constraints as NOT FOR REPLICATION. -or-
Use merge replication join filters to avoid foreign key rows at the Subscriber that have no associated parent key row. -or-
Use a custom COM resolver to handle this situation.

MORE INFORMATION

Steps to Reproduce Behavior

Create two tables at the Publisher, named T1 and T2. Do not establish a foreign key constraint between these tables.
Create the same two tables at the Subscriber. This time create a foreign key constraint between table T1 (the parent) and table T2 (the child).
Publish the tables for merge replication, and push a new subscription to the Subscriber making sure that you choose not to initialize the subscriber by selecting No, the Subscriber already has the schema and data.
Insert a row into table T2, the Publisher database. The insert succeeds because there are no constraints between the T1 and T2 tables.
Run the Merge Agent. The Merge Agent should report that it resolved one (1) conflict. To view the details of the conflict:
- Use the Merge Conflict Viewer. -or-
- Use the article's conflict table.
Run the Merge Agent again. The Merge Agent reports that it uploaded one (1) delete. If you perform a select from T2 on the Publisher, you will see that the row you inserted has been deleted.