PRB: Unresolved Merge Conflicts With Constraints And Triggers

• Microsoft SQL Server version 7.0

SYMPTOMS

When merge replication is established on tables that have constraints, namely, foreign key constraints or user triggers and these triggers/ constraints are not created with "NOT FOR REPLICATION" option, merge agent will log a conflict. For example, if the conflict is on a delete statement, the message may look like:

'DELETE' statement conflicted with column reference constraint 'constraintname'.
The conflict occurred in database 'databasename', table 'tablename',column 'columnname'

The error described above occurred when trying to delete this data at this
location, possibly because the deletion violated one or more constraints.
If you choose to ignore this conflict, you should resolve it through other
means. Consider logging the details of this conflict, then sending the log
entry to your system administrator. The value of the unique ID in column
'rowguid10' is '{31D07D7A-50EA-11D3-AD71-0050041B87D2}'.

WORKAROUND

When constraints or user triggers need to be enforced on tables involved in merge replication for business reasons, it is recommended that constraints/user triggers be marked as "NOT FOR REPLICATION" to avoid the above problem. If these constraints/triggers are not marked as "NOT FOR REPLICATION", the conflicts like the ones described above have to be resolved manually by removing the entry from the conflict tables MSmerge_delete_conflict (in case of delete conflict) or conflict_tablename (in case of insert or update conflicts) on the publisher database for the given rowguid.

MORE INFORMATION

Conflict viewer does not resolve these conflicts, fearing breakage of database integrity rules.

Here is an example of typical scenario where conflict viewer cannot resolve conflicts:

1. Create two tables on the publisher:




create table t1(c1 int primary key,  c2 uniqueidentifier rowguidcol)
create table t2(c1 int primary key, c2 int foreign key references t1(c1), c3 uniqueidentifier rowguidcol). 

2. Insert the following rows into table t1 and t2:




insert into t1 values(1,newid())
insert into t2 values(1,1,newid()) 

3. Create a merge publication consisting of table t1 as an article.



4. Subscribe to the publication.



5. Run the snapshot and merge agents to synchronize the data.



6. Delete the following row from subscriber:




delete from t1 where c1 =1 

7. Run the merge agent now and the delete done in step 6 will cause the publisher to log a conflict, which will not be resolved automatically.

Additional query words:

Keywords :
Version : winnt:7.0
Platform : winnt
Issue type : kbprb