CODEWITHSUNDEEP
sql-serversql-server-2008primary-key
Justin Hollender
Justin Hollender

Reputation: 373

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

On an eCommerce site, this code is inserting order shipping info into another database:

string sql = "INSERT INTO AC_Shipping_Addresses   
(pk_OrderID, FullName, Company, Address1, Address2, City, Province, PostalCode, CountryCode, Phone, Email, ShipMethod, Charge_Freight, Charge_Subtotal)  
VALUES (" + _Order.OrderNumber;
sql += ", '" + _Order.Shipments[0].ShipToFullName.Replace("'", "''") + "'";
if (_Order.Shipments[0].ShipToCompany == "")
{
  sql += ", '" + _Order.Shipments[0].ShipToFullName.Replace("'", "''") + "'";
}
else
{
  sql += ", '" + _Order.Shipments[0].ShipToCompany.Replace("'", "''") + "'";
}
sql += ", '" + _Order.Shipments[0].Address.Address1.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Address2.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.City.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Province.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.PostalCode.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Country.Name.Replace("'", "''") + "'";
sql += ", '" + _Order.Shipments[0].Address.Phone.Replace("'", "''") + "'";
if (_Order.Shipments[0].ShipToEmail == "")
{
  sql += ",'" + _Order.BillToEmail.Replace("'", "''") + "'";
}
else
{
  sql += ",'" + _Order.Shipments[0].ShipToEmail.Replace("'", "''") + "'";
}
sql += ", '" + _Order.Shipments[0].ShipMethod.Name.Replace("'", "''") + "'";
sql += ", " + shippingAmount;
sql += ", " + _Order.ProductSubtotal.ToString() + ")";
bll.dbUpdate(sql);

It is working, 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).

Should I declare the ID in the statement?

How would I adjust the code to fix this?

Upvotes: 20

Views: 347829

Answers (8)

Ger Groot
Ger Groot

Reputation: 1101

On a restored database inserting a new record using the EntityFramework the Indentity/Seed cah screw things up.

Use a reseed command:

DBCC CHECKIDENT ('[Prices]', RESEED, 4747030);GO

Upvotes: 32

Renet
Renet

Reputation: 349

Not an answer to the question but users searching for this might need to reseed their table:

DBCC CHECKIDENT(tablename)

Upvotes: 2

paparazzo
paparazzo

Reputation: 45106

Assuming pk_OrderID is the PK of AC_Shipping_Addresses and you are trying to insert a duplicate via the _Order.OrderNumber, do

select * from AC_Shipping_Addresses where pk_OrderID = 165863;

or select count(*) ....

You should get a row returned.

It is telling you that 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)

Upvotes: 4

Bhavik Saisuthar_AgileInfoways
Bhavik Saisuthar_AgileInfoways

Reputation: 21

I had requirement to move schema and data from local in house SQL server to azure SQL server.

To achieve this I

  1. tried bacpac from in house server to azure.
  2. used one of the features of the SSMS like Tasks > Deploy database to Microsoft Azure SQL database.
  3. used Microsoft Data Migration Assistant tool.

Problem was, when a script was executing, somehow a table, which is facing this already having id problem, is having records automatically added, it could be because of the cursor or any unique key applied.

So you have to check whether records are there or not before executing this query.

Upvotes: 1

Sudharsana
Sudharsana

Reputation: 1

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.

Upvotes: 0

Supercoder
Supercoder

Reputation: 1204

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
}

Upvotes: 1

B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

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) > 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 > 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 > 0, there is such a record.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 0

Related Questions