由于重复不存在而无法创建索引？ [英] Unable to create index because of duplicate that doesn't exist?

问题描述

运行以下Transact-SQL命令时出错：

I'm getting an error running the following Transact-SQL command:

CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName
ON DimMeasureTopic(TopicShortName)

错误是：

消息1505，级别16，状态1，行1
CREATE UNIQUE INDEX语句
终止，因为重复的键是
找到的对象名称
'dbo.DimMeasureTopic'和索引
名称'IX_TopicShortName'。
重复键值为（）。

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 ().

当我运行 SELECT * FROM sys.indexes时WHERE name ='IX_TopicShortName'或 SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID（N'[dbo]。[DimMeasureTopic]'） the IX_TopicShortName索引不显示。所以似乎没有重复。

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?

推荐答案

这不是索引已经存在，而是有重复的值表格中的 TopicShortName 字段。根据错误消息，重复值是一个空字符串（它可能只是发布的一个方面我猜）。这样的重复项会阻止创建 UNIQUE 索引。

It's not that the index already exists, but that there are duplicate values of the TopicShortName field in the table itself. According to the error message the duplicate value is an empty string (it might just be a facet of posting I guess). Such duplicates prevent the creation of a UNIQUE index.

您可以运行查询以确认您有重复：

You could run a query to confirm that you have a duplicate:

SELECT
    TopicShortName,
    COUNT(*)
FROM
    DimMeasureTopic
GROUP BY
    TopicShortName
HAVING
    COUNT(*) > 1

据推测，在其他数据库中，数据不同，并且不存在重复项。

Presumably in the other database the data are different, and the duplicates are not present.

这篇关于由于重复不存在而无法创建索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！