违反 PRIMARY KEY 约束.无法在对象中插入重复键 [英] Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

问题描述

我继承了一个项目，但遇到了一个不知道如何修复的 SQL 错误.

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 = "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);

它工作正常，但它也输出以下 SQL 错误:

It is working correctly, but it is also outputting the following SQL error:

违反 PRIMARY KEY 约束PK_AC_Shipping_Addresses".无法插入对象dbo.AC_Shipping_Addresses"中的重复键.重复的键值是 (165863).

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).

看了类似的问题，好像应该在语句中声明ID.

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?

推荐答案

很确定 pk_OrderID 是 AC_Shipping_Addresses 的 PK

Pretty sure pk_OrderID is the PK of AC_Shipping_Addresses

并且您正在尝试通过 _Order.OrderNumber 插入重复项

And you are trying to insert a duplicate via the _Order.OrderNumber

做一个

select * from AC_Shipping_Addresses where pk_OrderID = 165863;

或者选择 count(*) ....

or select count(*) ....

很确定你会得到一行返回.

Pretty sure you will get a row returned.

它告诉您的是您已经在使用 pk_OrderID = 165863 并且不能有具有该值的另一​​行.

What it is telling you is you are already using pk_OrderID = 165863 and cannot have another row with that value.

如果有一行就不想插入

insert into table (pk, value) 
select 11 as pk, 'val' as value 
where not exists (select 1 from table where pk = 11) 

这篇关于违反 PRIMARY KEY 约束.无法在对象中插入重复键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！