Primary Key Violation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Primary Key Violation

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-10-02 : 09:42:16

Hi All,

I've observed a strange issue in our environment.
we are using sql server 2008 R2 with SP2.

whenever a table is moving from primary range to secondary range on it's identity values, application is getting crashed with the message as below.

Violation of PRIMARY KEY constraint 'PK6'. Cannot insert duplicate key in object 'dbo.TD_TRANN'. The duplicate key value is (17868679).
The statement has been terminated.

OR

Violation of UNIQUE KEY constraint 'IX_TDS_COST'. Cannot insert duplicate key in object 'dbo.TDS_COST'. The duplicate key value is (17, 19431201).

identity ranges were auto managed by replication.

please suggest.

Arnav
Even you learn 1%, Learn it with 100% confidence.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 09:59:59

I don't know what this means:

"moving from primary range to secondary range on it's identity values"

but you are attempting to insert duplicate PKs or are inserting columns that violate a UNIQUE constraint. Those constraints are there to preserve referential integrity.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-10-02 : 10:23:44

Thank you Gbritton,
Yes, that is for referential integrity.

for example, a table has an identity ranges as below.

Range_begin Range_End Next_range_begin Next_range_end
16739013 16744013 16744013 16749013

we can get this from MSmerge_identity_range_allocations which is in distribution database.
in the above example, application is crashing when the range 16744013 ends. that is strange.

Experts... please suggest your views.

Arnav
Even you learn 1%, Learn it with 100% confidence.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-02 : 10:32:14

you need to figure out why you are attempting to insert duplicate values in columns that don't allow duplicates. Both error messages indicate PK or UQ violations due to duplicate values.

Since we cannot see your MSmerge_identity_range_allocations table or the queries you are using, there's not much more we can do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources