UNIQUE constraint mystery

  • sqlnyc

    SSCommitted

    Points: 1726

    Hi everyone,

    Running SQL 2008R2 Standard with SP2, and db comparability level = 90 (SQL 2005).

    Perhaps someone can shed light on this mystery I discovered. I have a table defined with a UNIQUE CONSTRAINT:

    CREATE TABLE [dbo].[t1]

    (

    [col1] [int] IDENTITY(1, 1) NOT NULL

    ,[ProcessedDate] [datetime] NULL

    ,[col3] [int] NOT NULL

    ,CONSTRAINT [UQ_T1_Col1_ProcessedDate_Col3] UNIQUE NONCLUSTERED ([col1] ASC, [ProcessedDate] ASC, [col3] ASC)

    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    )

    ON [PRIMARY]

    GO

    You will notice that IGNORE_DUP_KEY = OFF, however, there are a number of dupes in the table. If I drop the constraint and re-apply, it fails with unique constraint violation.

    Can anyone enlighten me as to how this seemingly contradictory condition came to be?

    Thanks,

    SQLNYC

  • sqlnyc

    SSCommitted

    Points: 1726

    my apologies, that should have been without the IDENTITY:

    CREATE TABLE [dbo].[t1]

    (

    [col1] [int] NOT NULL

    ,[ProcessedDate] [datetime] NULL

    ,[col3] [int] NOT NULL

    ,CONSTRAINT [UQ_T1_Col1_ProcessedDate_Col3] UNIQUE NONCLUSTERED ([col1] ASC, [ProcessedDate] ASC, [col3] ASC)

    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    )

    ON [PRIMARY]

    GO

  • sqlnyc

    SSCommitted

    Points: 1726

    It appears that the index was disabled.

  • Grant Fritchey

    SSC Guru

    Points: 395510

    That'll do it. Glad you figured it out.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply