none
sql server UNIQUE constraint column allow duplicate values by using "Edit Top 200 Rows"?

    Question

  • hi

    I create a table like this:

    if object_id('dbo.user_account', 'u') is not null 
       drop table dbo.user_account 
    
    create table dbo.user_account (
       user_id              varchar(100)         not null,
       user_name            varchar(100)         not null,
       sex                  char(1)              not null,
       qzone_no             varchar(32)          null,
       constraint pk_user_account primary key nonclustered (user_id)
    )
    go
    
    create unique index uq_qzone_no on dbo.user_account (
    qzone_no asc
    )
    where qzone_no is not null
    go
    

    but, when I use microsoft sql server management tool(right click the table and choose "Edit Top 200 Rows") to insert two same row,it's successful, is this a bug?

    Wednesday, November 13, 2013 3:17 AM

All replies

  • Hi,

    Execute the below query and see how many records you are getting.

    select count(1), qzone_no from user_account group by qzone_no

    If the count(1) value is greater than 1 then there is duplicate data.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Wednesday, November 13, 2013 3:41 AM
  • When you enter a new row using Edit Top 200 Rows, SQL doesn't attempt to insert that row until either you try to move to a different row or try to close the window.  So if you enter a row then move to a new row, that inserts the first row.  If you then enter the same values in this second row, you will not get the duplicate exception until you move to a new row, back to the first row, or to some other row or close the window.  I'm guessing you don't get an error because you are still on the second row.

    If that's not the cause of what you are seeing, could you tell us what release level of SQL you are using.

    Tom

    • Proposed as answer by Aalamjeet Rangi Wednesday, November 13, 2013 6:06 AM
    Wednesday, November 13, 2013 4:14 AM
  • hi, thanks for your reply.

    after I create a new table called dbo.user_account, it's ok.

    but, our production database still have the problem

    below is the table's information(in the production, the table called account.user_account):

    1. index information

    select OBJECT_NAME(object_id) as table_name,name,index_id,is_unique
    from sys.indexes
    where object_id = OBJECT_ID('ACCOUNT.USER_ACCOUNT')
    and name = 'UQ_LEZONE_NO'

    table_name             name                   index_id      is_unique
    USER_ACCOUNT     UQ_LEZONE_NO   3                   1

    2. index column information:

    select * from sys.index_columns
    where object_id = OBJECT_ID('ACCOUNT.USER_ACCOUNT') and index_id = 3

    object_id       index_id     index_column_id          column_id
    -----------        -----------    ---------------                   -----------
    1305771709  3                 1                                 8

    select object_id,name,column_id from sys.columns where object_id = OBJECT_ID('ACCOUNT.USER_ACCOUNT') and column_id=8

    object_id         name              column_id
    1305771709   LEZONE_NO     8

    3. duplicate data info:

    select count(1) as row_count ,lezone_no from account.USER_ACCOUNT
    where lezone_no is not null
    group by LEZONE_NO having count(LEZONE_NO)>1

    row_count   lezone_no
    ----------- --------------------------------
    2           769D585CDE07FDDF44A7C09AF1D6FEE9

    so, I still don't know why this happen.

    Hoping the information is enough.

    Wednesday, November 13, 2013 6:28 AM
  • Yes, I know how the "Edit Top 200 Rows" works.

    It got an error after I recreate a table same as the original table,

    but the table on production still have the problem, you can see my pre-reply.

    Wednesday, November 13, 2013 9:54 AM
  • Hi fly2thesky,

    Have you executed the following codes?

      select * from account.USER_ACCOUNT 
      where LEZONE_NO='769D585CDE07FDDF44A7C09AF1D6FEE9'

    Does it return two rows with the same data? I tried to reproduce the result but I received an error message after inserting a same value into LEZONE_NO column. What’s your SQL Server version, please apply all latest updates and check the result again.

    Best Regards,
    Allen Li


    Allen Li
    TechNet Community Support

    Thursday, November 14, 2013 8:35 AM
  • Hi Li,

    When I execute the following code:

    select user_id,user_name,lezone_no 
    from account.USER_ACCOUNT 
    where LEZONE_NO='769D585CDE07FDDF44A7C09AF1D6FEE9'


    it returns:

    user_id user_name lezone_no
    ---------------------------------   -------------------------------- --------------------------------
    2BBB64931B7203FDED2D5F28EB8BD4A1 FF3E9C7AABCC04FAEA2A582FEC8CD3A6 769D585CDE07FDDF44A7C09AF1D6FEE9
    2BBB6D931B7203FDED2D5F28EB8BD4A1 F220BD79A2DFE97E52275522E181DEAB 769D585CDE07FDDF44A7C09AF1D6FEE9

    and, when I execute :

    select @@version

    it returns:

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
    Feb 10 2012 19:39:15 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Best Regards,

    fly2thesky

    Thursday, December 05, 2013 2:32 AM
  • The unique index may be corrupted. Delete the duplicate data and recreate the unique index.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, December 05, 2013 5:56 AM
  • I would recommend running DBCC CHECKTABLE first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 05, 2013 10:29 AM