locked
Msg 2601, Level 14, State 1, Line 3 Cannot insert duplicate key row in object 'dbo.Table1' with unique index 'IX_Table1_IMPORTID_U'. The duplicate key value is (<NULL>). RRS feed

  • Question

  • I tried to insert a new row in "table1" but got error. I am using a trigger on column "IMPORTID"  but it is not working.

    Got error, Cannot insert duplicate key row in object 'dbo.table1' with unique index 'IX_table1_IMPORTID_U'. The duplicate key value is (<NULL>). 

    Wednesday, May 23, 2018 4:36 PM

All replies

  • Hi,

    As error is self explanatory, There is unique constraint on column ImportID and re-inserting same record throws error so in your case, ImportID record which is present in table dbo.Table1 and trigger is trying to insert the same record again. In order to avoid this issue, you will need to add a condition in Trigger to first check whether same record exist in table dbo.Table1 before inserting records.

    Hope this helps!

    Thanks,

    Alambir


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    Wednesday, May 23, 2018 5:55 PM
  • Hi Alambir, 

    Trigger always update a unique value but trigger is not working in here. I tried with debugger but debugger is not going in to trigger.  

    Thanks

    Wednesday, May 23, 2018 6:16 PM
  • Hi Alambir, 

    Trigger always update a unique value but trigger is not working in here. I tried with debugger but debugger is not going in to trigger.  

    Thanks

    Can you please share schema of table and code of insert statement, It will be helpful for troubleshooting.

    Thanks,

    Alambir


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    Wednesday, May 23, 2018 6:22 PM
  • Hi Alambir, 

    Trigger always update a unique value but trigger is not working in here. I tried with debugger but debugger is not going in to trigger.  

    Thanks

    I am using a trigger on column "IMPORTID" 

    You create trigger on a table not on a particular column

    So assuming what you meant was trigger is doing an update of the column value you need to ensure the updated values doesn't already exist in your table

    And from your explanation and from error message what I'm guessing is that you're having some logic to update the column with a unique value but its breaking in some case causing a NULL value to be inserted to the column for multiple rows which is causing this error.

    To suggest a solution, we need to see how your update look like. If you show the update stub then we might be able to suggest something 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Wednesday, May 23, 2018 6:43 PM
    Wednesday, May 23, 2018 6:43 PM