Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

Question

I inherited a project and I m running into a SQL error that I m not sure how to fix  On an eCommerce site  the code is inserting order shipping info into another database table  Here s the code that is inserting the info into the table  string sql    quot INSERT INTO AC Shipping Addresses     pk OrderID  FullName  Company  Address1  Address2  City  Province  PostalCode  CountryCode  Phone  Email  ShipMethod  Charge Freight  Charge Subtotal    VALUES   quot     Order OrderNumber  sql     quot     quot     Order Shipments 0  ShipToFullName Replace  quot   quot    quot    quot      quot   quot   if   Order Shipments 0  ShipToCompany     quot  quot       sql     quot     quot     Order Shipments 0  ShipToFullName Replace  quot   quot    quot    quot      quot   quot     else     sql     quot     quot     Order Shipments 0  ShipToCompany Replace  quot   quot    quot    quot      quot   quot     sql     quot     quot     Order Shipments 0  Address Address1 Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address Address2 Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address City Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address Province Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address PostalCode Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address Country Name Replace  quot   quot    quot    quot      quot   quot   sql     quot     quot     Order Shipments 0  Address Phone Replace  quot   quot    quot    quot      quot   quot   if   Order Shipments 0  ShipToEmail     quot  quot       sql     quot    quot     Order BillToEmail Replace  quot   quot    quot    quot      quot   quot     else     sql     quot    quot     Order Shipments 0  ShipToEmail Replace  quot   quot    quot    quot      quot   quot     sql     quot     quot     Order Shipments 0  ShipMethod Name Replace  quot   quot    quot    quot      quot   quot   sql     quot    quot    shippingAmount  sql     quot    quot     Order ProductSubtotal ToString      quot   quot   bll dbUpdate sql    It is working correctly  but it is also outputting the following SQL error   Violation of PRIMARY KEY constraint  PK AC Shipping Addresses   Cannot insert duplicate key in object  dbo AC Shipping Addresses   The duplicate key value is  165863    From reading similar questions  it seems that I should declare the ID in the statement  Is that correct  How would I adjust the code to fix this issue

User · Answer

There could be several things causing this and it somewhat depends on what you have set up in your database.

First, you could be using a PK in the table that is also an FK to another table making the relationship 1-1. IN this case you may need to do an update rather than an insert. If you really can have only one address record for an order this may be what is happening.

Next you could be using some sort of manual process to determine the id ahead of time. The trouble with those manual processes is that they can create race conditions where two records gab the same last id and increment it by one and then the second one can;t insert.

Third, you query as it is sent to the database may be creating two records. To determine if this is the case, Run Profiler to see exactly what SQL code you are sending and if ti is a select instead of a values clause, then run the select and see if you have due to the joins gotten some records to be duplicated. IN any even when you are creating code on the fly like this the first troubleshooting step is ALWAYS to run Profiler and see if what got sent was what you expected to be sent.

User · Answer

I was getting the same error on a restored database when I tried to insert a new record using the EntityFramework  It turned out that the Indentity Seed was screwing things up   Using a reseed command fixed it   DBCC CHECKIDENT    Prices    RESEED  4747030  GO

User · Answer

Pretty sure pk OrderID is the PK of AC Shipping Addresses    And you are trying to insert a duplicate via the  Order OrderNumber    Do a    select   from AC Shipping Addresses where pk OrderID   165863    or select count          Pretty sure you will get a row returned    What it is telling you is you are already using pk OrderID   165863 and cannot have another row with that value     if you want to not insert if there is a row    insert into table  pk  value   select 11 as pk   val  as value  where not exists  select 1 from table where pk   11

User · Answer

Not OP s answer but as this was the first question that popped up for me in google  Id also like to add that users searching for this might need to reseed their table  which was the case for me  DBCC CHECKIDENT tablename

User · Answer

Make sure if your table doesn t already have rows whose Primary Key values are same as the the Primary Key Id in your Query

User · Answer

To prevent inserting a record that exist already  I d check if the ID value exists in the database  For the example of a Table created with an IDENTITY PRIMARY KEY   CREATE TABLE  dbo   Persons            ID INT IDENTITY 1 1  PRIMARY KEY      LastName VARCHAR 40  NOT NULL      FirstName VARCHAR 40       When JANE DOE and JOE BROWN already exist in the database    SET IDENTITY INSERT  dbo   Persons  OFF  INSERT INTO  dbo   Persons   FirstName LastName  VALUES   JANE   DOE     INSERT INTO Persons  FirstName LastName   VALUES   JOE   BROWN      DATABASE OUTPUT of TABLE  dbo   Persons  will be   ID    LastName   FirstName 1     DOE        Jane 2     BROWN      JOE   I d check if i should update an existing record or insert a new one  As the following JAVA example    int NewID   1  boolean IdAlreadyExist   false     Using SQL database connection    STEP 1  Set property System setProperty  java net preferIPv4Stack    true       STEP 2  Register JDBC driver Class forName  com microsoft sqlserver jdbc SQLServerDriver       STEP 3  Open a connection try  Connection conn1   DriverManager getConnection DB URL  USER pwd        conn1 setAutoCommit true       String Select    select   from Persons where  ID       ID      Statement st1   conn1 createStatement        ResultSet rs1   st1 executeQuery Select          iterate through the java resultset     while  rs1 next              int ID   rs1 getInt  ID            if  NewID  ID                IdAlreadyExist   true                       conn1 close      catch  SQLException e1        System out println e1     if  IdAlreadyExist  false          Insert new record code here   else         Update existing record code here

User · Answer

What is the value you re passing to the primary key  presumably  pk OrderID    You can set it up to auto increment  and then there should never be a problem with duplicating the value - the DB will take care of that  If you need to specify a value yourself  you ll need to write code to determine what the max value for that field is  and then increment that    If you have a column named  ID  or such that is not shown in the query  that s fine as long as it is set up to autoincrement - but it s probably not  or you shouldn t get that err msg  Also  you would be better off writing an easier-on-the-eye query and using params  As the lad of nine years hence inferred  you re leaving your database open to SQL injection attacks if you simply plop in user-entered values  For example  you could have a method like this   internal static int GetItemIDForUnitAndItemCode string qry  string unit  string itemCode        int itemId      using  SqlConnection sqlConn   new SqlConnection ReportRunnerConstsAndUtils CPSConnStr                 using  SqlCommand cmd   new SqlCommand qry  sqlConn                         cmd CommandType   CommandType Text              cmd Parameters Add   Unit   SqlDbType VarChar  25  Value   unit              cmd Parameters Add   ItemCode   SqlDbType VarChar  25  Value   itemCode              sqlConn Open                itemId   Convert ToInt32 cmd ExecuteScalar                         return itemId         that is called like so   int itemId   SQLDBHelper GetItemIDForUnitAndItemCode GetItemIDForUnitAndItemCodeQuery   unit  itemCode     You don t have to  but I store the query separately   public static readonly String GetItemIDForUnitAndItemCodeQuery    SELECT PoisonToe FROM Platypi WHERE Unit    Unit AND ItemCode    ItemCode     You can verify that you re not about to insert an already-existing value by  pseudocode    bool alreadyExists   IDAlreadyExists query  value   gt  0    The query is something like  SELECT COUNT FROM TABLE WHERE BLA    CANDIDATEIDVAL  and the value is the ID you re potentially about to insert   if  alreadyExists     keep inc ing and checking until false  then use that id value   Justin wants to know if this will work   string exists    SELECT 1 from AC Shipping Addresses where pk OrderID      Order OrderNumber  if  exists  gt  0       What seems would work to me is   string existsQuery   string format  SELECT 1 from AC Shipping Addresses where pk OrderID    0     Order OrderNumber       Or  better yet  string existsQuery    SELECT COUNT    from AC Shipping Addresses where pk OrderID    OrderNumber       Now run that query after applying a value to the OrderNumber query param  use code similar to that above   then  if the result is  gt  0  there is such a record

[sql-server] Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Examples related to sql-server

Examples related to sql-server-2008

Examples related to primary-key