Cannot insert duplicate key row in object 'TABLENAME' with unique index 'DOC_IDX2'

I am writing a script that parses thru dates in a column and fixes them. When I run the script i hit a row after it has updated a bunch with no problem and get this error:

Cannot insert duplicate key row in object 'TABLENAME' with unique index 'DOC_IDX2'

I think it may have somthing to do with my indexes, but am not sure what to do.

Here is my script:
do while not rs.eof    
      rawDate=rs("f3")
      goodDate=""
      numErrors=0            
      
      If Len(rawDate)<>10 Then 'Only parse bad dates that have less then or greater then 10 digits            
            If InStr(rawDate,"/")>0 Then 'Parse dates with slash in them
                  rawDateArr=Split(rawDate,"/")                  
                  cnt=0
                  If Ubound(rawDateArr)=2 Then 'only parse dates that have 3 items in the array 03/15/2009 vs 03/15
                        For each x in rawDateArr 'loop thru array of items in date
                              cnt=cnt+1 'count position of loops thru array to determine year
                              If cnt=3 Then 'fix year values
                                    If Len(x)=2 Then '2 digit year or 4
                                          If cint(x)<20 Then 'if 2 digit year add a 20 or 19 in front
                                                x="20"&x
                                          Else
                                                x="19"&x
                                          End If                                     
                                    End if
                              ElseIf Len(x)<2 Then 'fix day and month values
                                    x="0"&x
                              End If
                              goodDate=goodDate&x&"/"
                        Next                  
                        goodDate=left(goodDate,10)
                        rs("f3")=goodDate
                        rs.update
                        HandleError
                        
                  End if
            End If      

            
            
            If InStr(rawDate,"/")=0 and Len(rawDate)=8 Then 'Parse dates without slashes that are 8 digits long
                  goodDate=mid(rawDate,5,2)&"/"&right(rawDate,2)&"/"&left(rawDate,4)            
                  rs("f3")=goodDate
                  rs.update                   
                  HandleError
            End If      
      End If
rs.movenext
loop
jrking1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
I assume there is a UNIQUE key constraint 'DOC_IDX2' on the table 'TABLENAME'

I suggest you to do either one of these.

1. If you want the field to be unique - before insert to table, first check whether that field already exists. If exists, then update that row else only insert.

2. Or if you want duplicate records, remove that constraint.

ALTER TABLE TABLENAME DROP CONSTRAINT DOC_IDX2
GO


0
Paul_Harris_FusionCommented:
I think in your case the error message is a little misleading.

From looking at your script,  you are not attempting to insert new rows.

However, you are updating rows with the line
 rs("f3")=goodDate

If there is a unique index on the f3 column,  and there is already a row in the table with the value matching goodDate,  then the update will fail since it would create a duplicate value in a column that is constrained to be unique.

So the error is caused by an update despite the message saying Insert.

You could check for other rows with the same value of goodDate prior to making the update.
0
Paul_Harris_FusionCommented:
A bit more...

The language in your script suggests this is a data cleansing exercise.   Such projects often identify duplicates once a consistent convention is applied to names etc and I think this is what is happening with you.

You may need to considering binning the duplicates.

One approach is to
creae a copy of your 'dirty' table but without the unique constraint on your f3 column
run your script against the copy - this will clean the f3 column but result in duplicates.
query for the duplicates and take appropriate action to get rid of them - change the values, delete one or more of the records etc
Transfer the results back into your real table
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

jrking1978Author Commented:
Thank you for your help.

There is not a column named doc_idx2, so assume that this is an index? Unfortunately, I can;t chang ehte design of this table as it is used in a software package. We are trying to clean up dates that were inserted in wrong fromats.

Are you saying I need to query the index to see if a value exists? Is there a workaround so I do not need to mess with DB design, or mess with the index?

Thanks, I know basic SQL, but when it gets into indexes and unique keys i am lost.
0
Paul_Harris_FusionCommented:
My previous post suggests a workaround using a temporary table

Create a temp table as follows:

Select *
Into #TempTable
From MyRealTable

This will create a copy of your data but without any of the constraints and indexes.  Run your script on this process and then see what you get.   You can use aggregate queries to check if any date value occurs more than once.

Looking at your script, it is only column f3 (assuming it was not aliased when you queried the recordset) that is being updated.   It must therefore be this column that is indexed by DOC_IDX2.
You can confirm this using SQL Server Management studio - open the database, select the table, open the indexes folder, double-click the DOC_IDX2 index.  You will see the columns that are part of the index.

So if you need to check for an existing record it would be something like

Select count(0) from MyTable where f3 <>  yourvalueforgoodDate and recordId <> currentRecordId

Note this is pseudo SQL - but I am sure you get the idea.
0
jrking1978Author Commented:
so if I am able to make the cahnegs on the TempTable I create, do I then copy that table back into the RealTable? Or is that jsut to test without index contraints?

0
Paul_Harris_FusionCommented:
It is to test without index constraints.

The correction of badly formatted dates is clearly generating duplicates
e.g. if you had two rows with date values as follows
1 Jan 2009
Jan 1 2009
and you brought them into a consistent format you would have a duplicate.

Part of your cleansing has to include figuring out what to do in this case.

Having a look at the records in a temp table will at least let you see the scale of the problem and form a plan.

0
jrking1978Author Commented:
When I query my table for any date that has 1/1/2009, there are around 50 of them, so it seems strange that it has a problem with some, but corrected others. I will try the temptable and post back with the results.
0
Paul_Harris_FusionCommented:
In that case, I would check what columns are part of that index.   Maybe it is a composite key and it is combination of the date and some other column that must be unique.

Use SQL Server Managment studio or else use the following SQL

Select i.name ,i.is_unique,  col_name(c.object_id,c.column_id ) as columnName
from sys.indexes i, sys.index_columns c
where i.name = 'DOC_IDX2'
and c.object_id = i.object_id

This will show you the columns that are part of that index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrking1978Author Commented:
Looks like there is a non-clustered index, and the column f3 is part of it along with 6 other columns. Under options there is a chekbox "ignore duplicate values" and it is not checked.  In the General optiosn there is a checkbox "Unique" and it is checked.

I tried rebuilding the index, that did not help.

I then created the temp table, verified there were no indexes and ran my script. The script ran great and completed with no errors.

So how do I fix this index problem?
0
jrking1978Author Commented:
I do notice that 2 of my filelds that are in the index have nulls in them, where the other fields that did not raise an error do not ahve nulls in them. I wonder if that is causing an issue?
0
jrking1978Author Commented:
Thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.