SQL Server 2000 incorrect message: duplicate key with unique index

Question

Database server: SQL Server 2000 - 8.00.760 - SP3 - Standard Edition

I have a table with 5 columns, in which I want to add a large (300.000) number of records. There is a unique index on the combination of two columns. If I add the records with this unique index in place, SQL Server gives this error message:

Msg 2601, Level 14, State 3, Line 1 Cannot insert duplicate key row in object 'TESTTABLE' with unique index 'test'. The statement has been terminated.

(0 row(s) affected)

However, if I delete the unique index and then add the records, I do not get any error when I create this unique index afterwards.

Answer 1

Check which fields constitute your unique index.

You have duplication in your source data on those fields which is causing your INSERT to fail.

What you do about that depends on the nature of your data. If it's just that one row is newer than other, making the original obsolete, then you can get rid. Otherwise, speak to your business people about :-

• what you should do with the dupe data
• whether the unique constraint on the columns is necessary

Some problems can be solved easily. For this one, most of your solutions are actually non-technical.

Answer 2

One of your assumptions has to be wrong. Re-visit them. Either the nature of your index, the data coming in, the pre-existing data (if any), the collation (case-insensitive/case-sensitive).

Answer 3

Unique constraint (not index) that uses WITH NOCHECK in the ALTER TABLE?