locked
Violation of unique constraint – But there is no duplicate value? RRS feed

  • Question

  • Hello Developers,

    I’m almost running out of steam trying to debug this error. I am loading data to a Filestream table. The table structure is similar to the one explained in one of the websites which I will borrow for description purposes of my challenge. It is not the BLOB data that I am interested in, but the unique identifier data.

    CREATE TABLE [dbo].[FS_Table]

    (

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

    [UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,

    [FS_Data] [varbinary](max) FILESTREAM NULL,

     CONSTRAINT [PK_Bin] PRIMARY KEY CLUSTERED

    (

           [Id] ASC

    ) ON [PRIMARY] FILESTREAM_ON [MediaFilegroup],

     CONSTRAINT [UQ_FileStream_Index] UNIQUE NONCLUSTERED

    (

           [UI] ASC

    )) ON [PRIMARY] FILESTREAM_ON [MediaFilegroup]

    INSERT INTO [dbo].[FS_Table] (Id, UI, FS_Data)

    VALUES (1

    ,NEWID()

    ,(SELECT * FROM OPENROWSET(BULK N'D:\temp\image_001.jpg', SINGLE_BLOB) AS Image001)

    );

    GO

    My challenge is that when I insert the data all works well until the job reaches a specific row number, and then it throws an error of unique constraint violation due to duplication.

    Violation of UNIQUE KEY constraint ' UQ_FileStream_Index'. Cannot insert duplicate key in object 'dbo.FS_Table'. The duplicate key value is (14d2fba6-82f3-431b-a71c-77f4dc886767).

    This happens on row 24855. It has happened three times. Please note that the supposed offending value has been different in all cases when the error happened as this value is randomly generated by the machine. One would expect that GUID values are unique within a given network. After reading around in the internet, I discovered that these values may not necessarily be unique all the time, hence the use of the UNIQUE constraint on the table to avoid any inadvertent duplication.

    After experience several failed processed, for the benefit of the doubt, I created a table with over 3million GUID values loaded to a column constrained by a UNIQUE constraint. In that way, there is no chance of duplication. Next, as I loaded the BLOB data, I alongside pulled the pre-generated GUID values from the configuration table, flagging all the ones that have been used. To my dismay, the same error about violation of unique constraint happened again on row number 24855. What is happening? I would appreciate if someone can advise please.

    I am running my job on SQL Server 2017, not sure if this will help.

    Kind regards,


    Mpumelelo


    • Edited by Mpumelelo S Thursday, January 16, 2020 12:43 AM
    Thursday, January 16, 2020 12:31 AM

Answers

  • @Jeff – I think what you have said sounds close to the cause of this mysterious duplicate value. Regarding identifying the value to be pulled, there is a flag column that I named “IsValueUsed” which I use to ensure that a given value is not pulled more than once.

    @Erland and @Lily – thanks for your views.

    To all:

    My solution is now working. I have changed the approach. Previously, I have been running my job from SSIS which was calling a stored procedure with the code details that I mentioned in my first posting. That SP did not only generate BLOB data, but had many other processes happening in it, which included loading of several tables with columns that share referential integrity in some instances. Although the INSERT statements that I was using were simple and straight forward, it is possible that, in that mix something was not right. I have transformed everything to SSIS and all is working perfectly well now. Thanks for your help.


    Mpumelelo

    • Proposed as answer by Lily Lii Monday, January 20, 2020 1:49 AM
    • Marked as answer by Mpumelelo S Monday, January 20, 2020 9:24 AM
    Friday, January 17, 2020 10:01 AM

All replies

  • Perhaps it is a subtle defect? See a precedent:


    As a workaround, maybe intercept the error and retry the operation after a random pause (in a loop).


    • Edited by Viorel_MVP Thursday, January 16, 2020 6:12 AM
    Thursday, January 16, 2020 6:12 AM
  • Hi Mpumelelo,

    There is very little chance that GUID deplicates, please make sure the autoIncrement property for the ID column is set to false and readonly is set to false.

    May I ask if you have tried to check and adjust row 24855 manually?

    Best Regards,

    Lily



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 16, 2020 7:09 AM
  • @Lily, I excluded that chance by creating a configuration table where I populated beforehand millions of GUID values on a column with a UNIQUE constraint. I then pulled the GUID values from that table instead of auto-generating them on the fly. However, the error recurred, all the same.

    @Viorel, thanks for the link.


    Mpumelelo

    • Edited by Mpumelelo S Thursday, January 16, 2020 9:59 AM
    Thursday, January 16, 2020 9:44 AM
  • If this duplicated again - when you are pulling a single value from a table where you know they are unique - then it must be the query you are using that is causing duplicates to be included in that batch.

    How are you identifying the value to be pulled from your table - or the data to be inserted into the table?  You stated this occurs on the same row every time - so where is this data coming from and how are you constructing the query for the insert process?


    Jeff Williams

    Thursday, January 16, 2020 10:03 PM
  • Could OPENROWSET somehow produce two rows?

    Run the load with out the UNIQUE constraint in place, and then check the contents. Also add the file name to the table as a debugging aid.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 16, 2020 10:57 PM
  • Hi Mpumelelo,

    Thank you very much for your reply. It consumes much test and patience to find the cause of the problem.

    Is there any null value in your source table? Here is a way to delete the duplicate rows manually: Duplicate key rows from the sys.syscommittab table in SQL Server.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 17, 2020 7:48 AM
  • @Jeff – I think what you have said sounds close to the cause of this mysterious duplicate value. Regarding identifying the value to be pulled, there is a flag column that I named “IsValueUsed” which I use to ensure that a given value is not pulled more than once.

    @Erland and @Lily – thanks for your views.

    To all:

    My solution is now working. I have changed the approach. Previously, I have been running my job from SSIS which was calling a stored procedure with the code details that I mentioned in my first posting. That SP did not only generate BLOB data, but had many other processes happening in it, which included loading of several tables with columns that share referential integrity in some instances. Although the INSERT statements that I was using were simple and straight forward, it is possible that, in that mix something was not right. I have transformed everything to SSIS and all is working perfectly well now. Thanks for your help.


    Mpumelelo

    • Proposed as answer by Lily Lii Monday, January 20, 2020 1:49 AM
    • Marked as answer by Mpumelelo S Monday, January 20, 2020 9:24 AM
    Friday, January 17, 2020 10:01 AM
  • Hi Mpumelelo,

    Thank you for your persistence. It's so kind of you to mark the solution as answer in order to close this thread. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, January 20, 2020 1:51 AM