CODEWITHSUNDEEP
sqlsql-servert-sql
user1250264
user1250264

Reputation: 905

sql server script While creating Index, Getting duplicate key was found for the object name

I am getting the following error

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MY_TABL' and the index name 'idxReqId'. The duplicate key value is (0).

The script alter table, add columns and finally add a unique nonclustered index. The script fails on this line

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

I did the following query:

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

I got nothing back

I did a query for SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('[dbo].[MY_TABL]') and got 1 returned but it is for a CLUSTERED index.

I can't figure how to get past this error. Any help is appreciated.

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;

Upvotes: 0

Views: 3427

Answers (1)

Hogan
Hogan

Reputation: 70523

Run the following query

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

This will give you a list of MY_REQIDs that are not UNIQUE. To make this index they must be unique. Delete the duplicates / Figure out why they are not unique now that you know which / how many records are not unique.

Upvotes: 1

Related Questions