SQL 服务器脚本 创建索引时，发现对象名称的“获取重复键”

Question

我收到以下错误

创建唯一索引语句终止，因为找到对象名称 'dbo 的重复键。MY_TABL"和索引名称"idxReqId"。重复的键值为 （0）。

该脚本更改表，添加列，最后添加唯一的非聚集索引。脚本在此行上失败

CREATE UNIQUE NONCLUSTERED INDEX idxReqId ON [dbo].[MY_TABL](MY_REQID)

我做了以下查询：

SELECT * FROM sys.indexes WHERE name='idxReqId' AND object_id = OBJECT_ID('[dbo].[MY_TABL]')

我一无所获

我查询了SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[MY_TABL]')并返回了 1，但它是针对聚集索引的。

我不知道如何克服这个错误。任何帮助，不胜感激。

USE [TestWebDb2_Local]
GO

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET XACT_ABORT ON
GO
BEGIN TRANSACTION
BEGIN TRY 
    IF COLUMNPROPERTY(OBJECT_ID('[dbo].[MY_TABL]'), 'MY_REQID', 'ColumnId') IS NULL
    BEGIN
        ALTER TABLE [dbo].[MY_TABL] ADD MY_REQID INT NOT NULL DEFAULT(0) 
    END
    IF COLUMNPROPERTY(OBJECT_ID('dbo.MY_TABL'), 'MY_ISCOMPLETED', 'ColumnId') IS NULL
    BEGIN
        ALTER TABLE [dbo].[MY_TABL] ADD MY_ISCOMPLETED BIT NOT NULL DEFAULT(0)
    END
END TRY
BEGIN CATCH
        -- Test whether the transaction is uncommittable.
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT 'The transaction from Alter table is in an uncommittable state.' +
                  ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO 

 BEGIN TRY 
    UPDATE [dbo].[MY_TABL] SET MY_REQID = 20 WHERE MY_SHORT_NAME = '1035'
    UPDATE [dbo].[MY_TABL] SET MY_REQID = 37, MY_ISCOMPLETED = 1 WHERE MY_SHORT_NAME = 'ABVRPARA'
END TRY
BEGIN CATCH
        IF (XACT_STATE()) = -1
        BEGIN
            PRINT 'The transaction from UPDATE table is in an uncommittable state.' +
                  ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
END CATCH;

BEGIN TRY
        CREATE UNIQUE NONCLUSTERED INDEX idxReqId ON [dbo].[MY_TABL](MY_REQID)
        PRINT 'Index created'
END TRY
BEGIN CATCH
        -- Test whether the transaction is uncommittable.
        IF (XACT_STATE()) = -1
        BEGIN
            DECLARE @ErrorMessage NVARCHAR(4000);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
            SELECT 
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();
            -- Use RAISERROR inside the CATCH block to return error
            -- information about the original error that caused
            -- execution to jump to the CATCH block.
            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );

            PRINT 'The transaction from Create Index is in an uncommittable state.' +
                  ' Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO
if XACT_STATE() = 1
BEGIN
    COMMIT TRANSACTION  
    PRINT 'Transaction committed.';
END;  

Answer 1

运行以下查询

SELECT MY_REQID, COUNT(*) as COUNT
FROM [dbo].[MY_TABL]
GROUP BY MY_REQID
HAVING COUNT(*) > 1

这将为您提供一个非唯一MY_REQIDs的列表。 要创建此索引，它们必须是唯一的。 删除重复项/找出它们不唯一的原因，因为您知道哪些/多少条记录不是唯一的。