Cannot insert duplicate key row in object

  • All,

    Thank you very much for creating forums such as this for those of us who are, at best, journeymen.

    I have an SS2K5 environment serving as the back-end for an MS Dynamics AX ERP application. I recently updated the value for a single record in a single column of a table. No errors, no problems. Upon realizing I had placed the incorrect value in the record I attempted to change it back to it's original value and received an error.

    ""Msg 2601, Level 14, State 1, Line 1

    Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.

    The statement has been terminated.""

    How do I get the original value back in this field?

  • twantoni (4/8/2009)


    All,

    Thank you very much for creating forums such as this for those of us who are, at best, journeymen.

    Greets 🙂

    ""Msg 2601, Level 14, State 1, Line 1

    Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.

    The statement has been terminated.""

    Seems that the value already exists in another row. I have no yet idea about Dynamics but try to select from your table INVENTDIM with a WHERE condition for the value you want to set.

    Flo

  • Flo,

    Thanks for the prompt response. Unfortunately the condition exists 42 times including my problem child. I don't believe the index is tied directly to the column I'm trying to update but I can't figure out what it is tied too.

  • Hi

    In Management Studio Object Explorer select your table "dbo.INVENTDIM" expand the node and expand the "Indexes" node there should be an index called "I_698DIMIDX". Right click and select "Script Index As"->"CREATE To"->"New Query Editor Window".

    Please post the script here than we can look for its uniqueness.

    Greets

    Flo

  • twantoni (4/8/2009)


    All,

    ""Msg 2601, Level 14, State 1, Line 1

    Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.

    The statement has been terminated.""

    How do I get the original value back in this field?

    Looks like 'I_698DIMIDX' is UNIQUE index and the column which you are trying to change teh value is par of this Index Definition.

    Just see the value which you are trying to change is already part of teh table or not!!!

  • The context of the script is:

    USE [LMC_AX4_Production]

    GO

    /****** Object: Index [I_698DIMIDX] Script Date: 04/09/2009 09:15:54 ******/

    CREATE UNIQUE NONCLUSTERED INDEX [I_698DIMIDX] ON [dbo].[INVENTDIM]

    (

    [DATAAREAID] ASC,

    [CONFIGID] ASC,

    [INVENTSIZEID] ASC,

    [INVENTCOLORID] ASC,

    [INVENTLOCATIONID] ASC,

    [INVENTBATCHID] ASC,

    [WMSLOCATIONID] ASC,

    [WMSPALLETID] ASC,

    [INVENTSERIALID] ASC

    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

  • twantoni (4/8/2009)


    Flo,

    I don't believe the index is tied directly to the column I'm trying to update but I can't figure out what it is tied too.

    Just because you are updating only one column, if all the columns combined violate the uniqueness, it is an error. SQL doesn't just toss these errors out randomly but are based upon your constraints.

    -- You can't be late until you show up.

  • Terry,

    I'm sorry I really don't understand indexes very well. Consequently the script I inserted in my last post might as well have been in Chinese, it really doesn't mean much to me. Would it be possible for you to explain your comment regarding the uniqueness of a record? There is only one record in the table with the correct DIMID, it's the one I'm trying to modify. Does my script indicate there is another field or combination of fields which are required to be unique?

    Any help you can provide is greatly appreciated.

  • The combination of all your key fields needs to be unique:

    DATAAREAID],

    [CONFIGID],

    [INVENTSIZEID],

    [INVENTCOLORID],

    [INVENTLOCATIONID],

    [INVENTBATCHID],

    [WMSLOCATIONID],

    [WMSPALLETID],

    [INVENTSERIALID]

    -- You can't be late until you show up.

  • Hi

    Little explanation:

    An UNIQUE INDEX (independent if CLUSTERED or NONCLUSTERED for the moment) is a unique key.

    Simplified a table is like a list of objects. You are able to create indexes and/or uniqueness. A unique index is like a key in a C# Dictionary or Hashtable. Let's say you have a Dictionary which gets as key a custom struct and the struct has the fields/properties which are defined in the unique index. You are not allowed to add the same key into your dictionary twice.

    Here a simple translation of a C# snipped to TSQL.

    C#

    class Program

    {

    struct MyKey

    {

    public MyKey(int dataAreaId, int configId, int inventSizeId)

    {

    DataAreaId = dataAreaId;

    ConfigId = configId;

    InventSizeId = inventSizeId;

    }

    public int DataAreaId;

    public int ConfigId;

    public int InventSizeId;

    public override int GetHashCode()

    {

    return DataAreaId + ConfigId + InventSizeId;

    }

    public override bool Equals(object obj)

    {

    if (obj is MyKey)

    {

    MyKey key = (MyKey)obj;

    if (key.ConfigId == this.ConfigId

    && key.DataAreaId == this.DataAreaId

    && key.InventSizeId == this.InventSizeId)

    return true;

    }

    return false;

    }

    }

    static void Main(string[] args)

    {

    Dictionary<MyKey, string> dict = new Dictionary<MyKey, string>();

    MyKey key;

    key = new MyKey(1, 1, 1);

    dict.Add(key, "Hello World");

    key = new MyKey(2, 1, 1);

    dict.Add(key, "Hello World 2");

    // Exception:

    key = new MyKey(1, 1, 1);

    dict.Add(key, "Hello World 3");

    }

    }

    TSQL

    USE tempdb

    GO

    IF (OBJECT_ID('tempdb..#test') IS NOT NULL)

    DROP TABLE #test

    GO

    -- Create table

    CREATE TABLE #test

    (

    DataAreaId INT,

    ConfigId INT,

    InventSizeId INT

    )

    -- Create unique index

    CREATE UNIQUE

    INDEX [I_698DIMIDX]

    ON #test (DataAreaId, ConfigId, InventSizeId)

    INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)

    SELECT 1, 1, 1

    INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)

    SELECT 2, 1, 1

    -- Error

    INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)

    SELECT 1, 1, 1

    GO

    -- Switch to "Results" tab to see the first two rows where inserted

    SELECT * FROM #test

    You can execute the TSQL script in Management Studio. It creates a temporary table in your tempdb. The table will automatically removed when you close your query window.

    Hope this helps!

    Maybe you should read some TSQL tutorials or how-to's. You don't have to read books with hundreds of pages but a main basic would help. Especially to you! It helps understanding the answers you get. This forum is called "Newbies" forum and that's exactly what it is, all members in this area really try to explain what they are doing but some real basics should be available.

    Best wishes!

    Flo

  • Flo & Terry,

    Thank you very much for your help and suggestions. The lightbulb went off after reading both of your last posts.

    The whole time I was looking for a record with a unique index which included 'L-092' when I should have been looking for a record without 'L-092' because this is what I was changing it too, from L-092 to blank. As soon as I looked for records with a blank BATCHID I found the duplicate.

    Thank you again.

  • Could you post the original update command you were issuing which was causing the error?

  • twantoni (4/9/2009)


    The lightbulb went off after reading both of your last posts.

    That's the beauty of these forums. A little nudge in the right direction and things come into focus. Then all is right in the world, for a few moments anyway. 😉 Glad we could help.

    -- You can't be late until you show up.

  • rgillings (4/13/2009)


    Could you post the original update command you were issuing which was causing the error?

    UPDATE INVENTDIM SET INVENTBATCHID='L-092'

    WHERE INVENTDIMID='IDN-008354'

Viewing 14 posts - 1 through 13 (of 13 total)

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