Unique Index Error when creating non-unique index - SQL Server

Question

I trying to and a non-unique index to a table table in SQL Server 2005. I am getting the following error when I try to create it.

Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.oe_pick_ticket' and the index name 'idx_pick_ticket_popup_wmms'. The duplicate key value is (1093066, N, N, N, , FBF, 100001, 1074359, 1118930).

My create statement is as follows:

CREATE NONCLUSTERED INDEX idx_pick_ticket_popup_wmms 
ON oe_pick_ticket (invoice_no, delete_flag, direct_shipment, auxiliary, oe_pick_ticket_type_cd, company_id, location_id, order_no)

I have tried rebuilding the clustered/unique/primary key index and that didn't change anything. Does anyone know why I am getting this error and how to resolve it?

Are you 100% sure that index doesn't already exist? Have you tried dropping it to see if it throws an error? It sounds like that index name is already present on that table. — JNK
– JNK, Commented Aug 3, 2010 at 16:57
Odd that the error message talks about a UNIQUE index but I don't see the UNIQUE keyword in the statement you've provided. There seems to be a disconnect there. — Joe Stefanelli
– Joe Stefanelli, Commented Aug 3, 2010 at 17:56

Mark S. · Accepted Answer · 2010-08-04 00:42:59Z

4

It turns out that I was able to figure this out on my own. I ran a DBCC CHECKTABLE on the table and there was an inconsistency. After that I ran it again with the REBUILD_REPAIR option and it fixed the primary key inconsistency.

answered Aug 4, 2010 at 0:42

Mark S.

1,5661 gold badge16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unique Index Error when creating non-unique index - SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related