Unable to create index because of duplicate that doesn't exist?
- by Alex Angas
I'm getting an error running the following Transact-SQL command:
CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName
ON DimMeasureTopic(TopicShortName)
The error is:
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.DimMeasureTopic' and the index
name 'IX_TopicShortName'. The
duplicate key value is ().
When I run SELECT * FROM sys.indexes WHERE name = 'IX_TopicShortName' or SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DimMeasureTopic]') the IX_TopicShortName index does not display. So there doesn't appear to be a duplicate.
I have the same schema in another database and can create the index without issues there. Any ideas why it won't create here?