A
A
Anton Polyakov2016-05-19 11:24:31
SQL
Anton Polyakov, 2016-05-19 11:24:31

How to fix Violation of PRIMARY KEY constraint with composite PK?

Initial data: there is a distribution server, there is a subscriber server.
Transactional replication from distribution to subscriber is configured.
The replication monitor outputs the following:


Violation of PRIMARY KEY constraint 'PK_dboSM_AlarmsStatistics'. Cannot insert duplicate key in object 'dbo.SM_AlarmsStatistics'. The duplicate key value is (ExceedingCur, 6061, 0). (Source: MSSQLServer, Error number: 2627)

in the SM_AlarmsStatistics table, a composite index of three columns that should contain (ExceedingCur, 6061, 0) from the error above.
if run
use distribution
go
exec sp_browsereplcmds '0x000064A0000048E5000A00000000', '0x000064A0000048E5000A00000000'
--по коду ошибки в Replication monitor
go

then we will see where exactly replication stops (is this how it works?)
Crashes at point 2
1. {CALL [sp_MSins_dboSM_States] (677390,0,6061,2016-05-18 00:00:00.000,'ExceedingCur','Yes' ,1,1)}
2. {CALL [sp_MSins_dboSM_AlarmsStatistics] (N'ExceedingCur',6061,0,2016-05-18 00:00:00.000,1,1,677390)}
I tried to truncate this table but The error remains even though the table is empty.
I also tried to manually insert values ​​into this table, everything is perfectly inserted in the subscriber
insert into SM_AlarmsStatistics values(N'ExceedingCur',6061,0,convert(datetime, '2016-05-18 00:00:00.000',121),1,1,677390)

Is it possible to update the composite index somehow (something like DBCC CHECKIDENT (@fullTableName, RESEED, @nextIdToGo)) or what to do in this case?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2016-05-19
@tsklab

How to fix Violation of PRIMARY KEY constraint with composite PK?
Transactional replication configured...
Try turning off column, table, database replication (one by one).
composite index
Use a surrogate key.
of three columns
Make a unique key, solely for verification.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question