none
Non clustered unique index creation failing due to duplicate key

    Question

  • I am a DBA and my rebuild index job has failed with the error that "The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and index name". The non clustered unique index is created on two columns and i see that these two columns have a duplicate value. My question is that how was the duplicate data inserted in to the table when it already had a non clustered unique index in place on these columns.

    Please help me to understand this concept.

    Monday, September 29, 2014 11:45 AM

Answers

  • It is possible to insert duplicate values in a column declared as UNIQUE by disabling the index. However, at the time of rebuilding (enabling) the index, it will give the "duplicate value" error.

    It is possible that someone disabled the index to accommodate the duplicate value or the index was disabled before the maintenance job and the duplicate crept in during the maintenance.

    create table dbo.DemoTable 
    (UQCol1 int constraint [UQ_DemoTable_UQCol1] unique)
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- success
    insert into dbo.DemoTable values (1)
    
    -- attempt to insert duplicate fails due to constraint violation
    insert into dbo.DemoTable values (1)
    /*
    Msg 2627, Level 14, State 1, Line 2
    Violation of UNIQUE KEY constraint 'UQ_DemoTable_UQCol1'. Cannot insert duplicate key in object 'dbo.DemoTable'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    select * from dbo.DemoTable
    
    -- now disable the index
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    disable;
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- success insert duplicate
    insert into dbo.DemoTable values (1)
    
    select * from dbo.DemoTable
    
    -- try to enable the unique index
    -- this fails due to duplicate values
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    rebuild;
    /*
    Msg 1505, Level 16, State 1, Line 3
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DemoTable' and the index name 'UQ_DemoTable_UQCol1'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    -- verify, 0=enabled, 1=disabled
    -- still disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- delete the duplicate row
    delete top (1) from dbo.DemoTable where UQCol1 = 1
    
    select * from dbo.DemoTable
    
    -- try to enable the unique index again
    -- succeeds
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    rebuild;
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- attempt to insert duplicate fails due to constraint violation
    insert into dbo.DemoTable values (1)
    


    - Aalamjeet Rangi | (Blog)

    Monday, September 29, 2014 6:41 PM

All replies

  • My question is that how was the duplicate data inserted in to the table when it already had a non clustered unique index in place on these columns.

    The non-clustered index should prevent duplicates.  Perhaps the non-clustered index was also filtered based on some other criteria such that the qualifying rows are unique.  Clustered indexes cannot be filtered since the index leaf nodes are the entire table.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, September 29, 2014 11:55 AM
    Moderator
  • Hi,

        Please let me know whether non-clustered unique index was enabled/disabled on this table.

    Monday, September 29, 2014 11:55 AM
  • I am a DBA and my rebuild index job has failed with the error that "The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and index name".

    How did you do rebuild job? I mean what's the script? Did you disable index in the rebuild script? 

    Saeid Hasani [sqldevelop]

    Monday, September 29, 2014 2:47 PM
    Moderator
  • It is possible to insert duplicate values in a column declared as UNIQUE by disabling the index. However, at the time of rebuilding (enabling) the index, it will give the "duplicate value" error.

    It is possible that someone disabled the index to accommodate the duplicate value or the index was disabled before the maintenance job and the duplicate crept in during the maintenance.

    create table dbo.DemoTable 
    (UQCol1 int constraint [UQ_DemoTable_UQCol1] unique)
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- success
    insert into dbo.DemoTable values (1)
    
    -- attempt to insert duplicate fails due to constraint violation
    insert into dbo.DemoTable values (1)
    /*
    Msg 2627, Level 14, State 1, Line 2
    Violation of UNIQUE KEY constraint 'UQ_DemoTable_UQCol1'. Cannot insert duplicate key in object 'dbo.DemoTable'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    select * from dbo.DemoTable
    
    -- now disable the index
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    disable;
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- success insert duplicate
    insert into dbo.DemoTable values (1)
    
    select * from dbo.DemoTable
    
    -- try to enable the unique index
    -- this fails due to duplicate values
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    rebuild;
    /*
    Msg 1505, Level 16, State 1, Line 3
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DemoTable' and the index name 'UQ_DemoTable_UQCol1'. The duplicate key value is (1).
    The statement has been terminated.
    */
    
    -- verify, 0=enabled, 1=disabled
    -- still disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- delete the duplicate row
    delete top (1) from dbo.DemoTable where UQCol1 = 1
    
    select * from dbo.DemoTable
    
    -- try to enable the unique index again
    -- succeeds
    alter index [UQ_DemoTable_UQCol1] on dbo.DemoTable
    rebuild;
    
    -- verify, 0=enabled, 1=disabled
    select name, is_disabled from sys.indexes
    where name = 'UQ_DemoTable_UQCol1'
    
    -- attempt to insert duplicate fails due to constraint violation
    insert into dbo.DemoTable values (1)
    


    - Aalamjeet Rangi | (Blog)

    Monday, September 29, 2014 6:41 PM
  • I guess this is what has happened. But now my entire row is not a duplicate of the other. One column has a different value. But when considering the columns included in non clustered index, they are duplicate. So how will I resolve the issue. Is there any way other than deleting one of these rows?

    Mathew P Jacob MCITP

    Monday, October 06, 2014 10:15 AM
  • Hello Saeid , Rebuild is done using maintenance plan

    Mathew P Jacob MCITP

    Monday, October 06, 2014 10:16 AM
  • . So how will I resolve the issue. Is there any way other than deleting one of these rows?

    There is no other way. You need to delete the duplicates:

    http://www.sqlusa.com/bestpractices/eliminateduplicates/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Monday, October 06, 2014 11:07 AM
    Moderator