all groups > sql server replication > april 2006 >
You're in the

sql server replication

group:

Primary key conflict because of composite key during merge replica


Primary key conflict because of composite key during merge replica Matt Sradley
4/13/2006 7:38:01 AM
sql server replication:
I am have an issue with a primary key conflict during merge replication.

The first error is:

The row was updated at 'HDC.Hospital' but could not be updated at
'TABLET2\VIA_MSDE.Hospital'. Violation of PRIMARY KEY constraint
'PK_TranLines'. Cannot insert duplicate key in object 'TranLines'.

The second error is:

The same row was updated at 'TABLET2\VIA_MSDE.Hospital' and deleted at
'HDC.Hospital'. The resolver chose the deletion as the winner.

Here is my theory:

The TranLines table is composed of a composite key with the primary key of
its parent table (TranDocs) and the line# of the TranLines rows associated
with the TranDoc (this is a typical Invoice master detail relationship). When
invoices are updated in our system the TranLines are deleted then inserted as
a group instead of being individually updated. I think the merge process is
not obeying the order of deletes and inserts and probably trying to insert
some lines that have not been deleted yet on the subscriber and then a
compensating delete occurs.

For example:

The invoice already exists in bother places. Line#2 was changed on
publisher. Merge agent tries to insert Line#2 on subscriber before deleting
first.

I know I can use NOT FOR REPLICATION on relationships and triggers. Can you
do this for a PK constraint as well? If not is there another work around?

Thanks in advance,

RE: Primary key conflict because of composite key during merge replica Matt Sradley
4/13/2006 8:01:02 AM
One thought I had after posting this is a NOT FOR REPLICATION option would
not work here anyway would it. Then there would be two rows in the TranLines
table with the same composite key. Then the out of order delete would delete
AddThis Social Bookmark Button