question

shamim007 avatar image
shamim007 asked

Cannot insert duplicate key row in object with unique index the duplicate key value SQL server

Hello Experts ,

one of our developer getting this error :

Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.nsIdentity' with unique index 'idx_nsIdentity_2'. The duplicate key value is (tkvW6Jxny7Y2toeh3OqVIg===...

all of sudden we are seeing the error ,we never seen them before and the index was created almost 10 years ago .

This error we noticed in Production and our prod database is running under SQL SERVER 2016 R2 standard with CU2 .

When I checked, the indexes, it's created like this:

CREATE UNIQUE NONCLUSTERED INDEX [idx_nsIdentity_2] ON [dbo].[nsIdentity]

( [SSN] ASC,

[DOB] ASC )

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

ON [PRIMARY]

GO

i noticed that Index name and table name are same and there are 3 indexes ,naming of the indexes are pretty funny like

First one is : idx_nsIdentity_1 (Clustered)

2Nd one : idx_nsIdentity_2(unique,Non-Clustered)

3rd one :idx_nsIdentity_3(Non_Unique,Non-Clustered).

what steps should i take to fix and why are we getting the error ?

Thanks

sql-server-2016nonclustered-indexunique-constraint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.