Cannot insert duplicate key row in object ''bla.bla.bla'' with unique index ''bla.bla.bla''.

Question

Cannot insert duplicate key row in object ''bla.bla.bla'' with unique index ''bla.bla.bla''.

Stephen Hubbard

SSC Enthusiast

Points: 101
More actions
August 7, 2007 at 1:38 pm

#73345

Ok, brand new to SQL and one of my new job descriptions is administering an application with SQL on the backend. I do not want anyone to instruct me on the use of SQL, I understand that much...I just need some guidance as to how to find the answer to my issue.

Help Please?
ITHubb
'People can only do to you what you allow them to.'

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

For starters, use Books Online and this web site. Books Online is a great SQL Server resource and contains information on many common errors such as the duplicate key error you are getting. A search of SSC should also net results for your topic.

This is all I can really tell you without 'instructing' you

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Stephen Hubbard SSC Enthusiast Points: 101 More actions · Answer 2

Thanks for the deviation John, lol. I searched SSC and found only one thread pertaining to my issue but cannot help me because I did not manually insert the key, the program did. But I will take your advice on using the books online to see if I can come up with the solution.

And then again, maybe I need SOME instruction...

John Rowan SSC Guru Points: 56440 More actions · Answer 3

The error itself is pretty straight forward. The application is attempting to insert a row and the key already exists. This leaves a couple of questions unanswered. What does the data represent? Should the row already exist? If not, why does it? If so, why is it attempting to insert a new one? Why doesn’t the application check for the existence of a row prior to attempting to insert one? Is this a one-time error or are you getting it often and if so, has the application code changed recently?

From a database perspective, SQL Server is doing its job here. The flaw is in code or logic. If you need to find out what the application is attempting to insert that is failing, I suggest running SQL Profiler and capturing the INSERT statement from the application. You can then search the table for the dupe and back-track yourself into the code and logic.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Stephen Hubbard SSC Enthusiast Points: 101 More actions · Answer 4

I appreciate the help John. Thanks much. I will get these questions answered and going forward, will know what questions to ask. I'm building a trace and will let you know the results of what I find.

Thanks again.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 5

Here's a powerful hint... the "key" this error message speaks to is either a "Primary Key" or a column with a UNIQUE index (usually a "Primary Key" for this error).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 6

Oh... almost forgot... if the code uses MAX(someid)+1 to determine the next "Key" or uses @@IDENTITY (SCOPE_IDENTITY is OK), that may be the source of the "Key" being out of sync with the data which would cause the attemp at the non-unique insertion to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Hubbard SSC Enthusiast Points: 101 More actions · Answer 7

My built-in human language pack does not compute. Can you explain that tom me in simpler terms? Lol

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 8

Heh... yeah... Just look for anything that uses MAX(somecolumnname)+1 or @@IDENTITY in the code. If the aren't "in sync", it'll cause Primary Key violations which is the error you're getting.

Also, lookup @@Identity, Scope_Identity, Primary Key and Unique Index in Books Online to help with the human language pack thingy...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen Hubbard SSC Enthusiast Points: 101 More actions · Answer 9

So I've gone through the books online (Upgraded my language pack) and although very informative and enlightening, didn't resolve the issue for me. Being a Systems Admin, I took the route I knew best and uninstalled the app, deleted all registry entries and log files (Backed up of course) and re-installed the app. No more problems there. Only because it was specific to 2 users was this route taken. I'm still troubleshooting the actual error.