CODEWITHSUNDEEP
sql-serverindexing
newguy
newguy

Reputation: 5976

CANNOT create unique index in SQL Server, how to know what index is the index ID referring to

I have a script that gives an error when being executed:

Msg 1505, Level 16, State 1, Server CBR07I300FVA1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 17. Most significant primary key is '44'. The statement has been terminated.

The script contains thousands of lines of queries so I have no idea where the error comes from in the script. Is there a way to know what "index ID 17" stands for?

Upvotes: 1

Views: 2257

Answers (4)

Denis Valeev
Denis Valeev

Reputation: 6015

Insert print statement before every significant step (say, create unique index) in the script and you're done.

It's usually done like this:

if @@error <> 0
   PRINT '@@error is ' + ltrim(str(@@error)) + '.'
else
   print 'Index IX_... successfully created'

Upvotes: 1

cjk
cjk

Reputation: 46425

If you are running this script in SSMS, just double click on the error and it will take you to the line of code that has caused the error...

Upvotes: 0

Piskvor left the building
Piskvor left the building

Reputation: 92752

The table you're working on already contains data; and the data isn't unique with regard to your new index.

Example:

 col1 | col2 | col3
====================
 foo  | 1    | q
 bar  | 2    | w
 bar  | 3    | e
 bar  | 2    | r

In the above table, you couldn't create a unique index on (col1,col2), exactly because the data in it would be non-unique (multiple rows with (bar,2)). The script can't know which of those "duplicate" rows is actually needed. There are three options available to it:

  • create a UNIQUE index with duplicate rows (invalid, as it's not unique any more)
  • delete the duplicate rows (unsafe, how can it know which rows are needed?)
  • do nothing and throw an error (safest option, you are here)

What you can do to resolve this:

run a query to find duplicates - if you group the rows by those columns used by the index, some of the groups will have multiple rows. Those are your duplicates; you need to somehow eliminate their duplicity.

Upvotes: 0

Yves M.
Yves M.

Reputation: 3318

You say a script with thousands of lines, eh?

My advise: put a print("Test") in the middle and see wether the error occurs before or after. And then again in the middle of the middle etc. until you find the place that is causing you the troubles.

Upvotes: 1

Related Questions