sql-server – 违反PRIMARY KEY约束.无法在对象中插入重复键

我继承了一个项目,我遇到了一个sql 错误,我不确定如何修复.

在电子商务网站上,代码将订单发货信息插入另一个数据库表.

这是将信息插入表中的代码：

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 += ","''") + "'";
}
else
{
  sql += ",'" + _Order.Shipments[0].ShipToCompany.Replace("'","''") + "'";
}
sql += ",'" + _Order.Shipments[0].Address.Address1.Replace("'","''") + "'";
sql += ",'" + _Order.Shipments[0].Address.Address2.Replace("'",'" + _Order.Shipments[0].Address.City.Replace("'",'" + _Order.Shipments[0].Address.Province.Replace("'",'" + _Order.Shipments[0].Address.PostalCode.Replace("'",'" + _Order.Shipments[0].Address.Country.Name.Replace("'",'" + _Order.Shipments[0].Address.Phone.Replace("'","''") + "'";
if (_Order.Shipments[0].ShipToEmail == "")
{
  sql += ",'" + _Order.BillToEmail.Replace("'",'" + _Order.Shipments[0].ShipToEmail.Replace("'",'" + _Order.Shipments[0].ShipMethod.Name.Replace("'"," + shippingAmount;
sql += "," + _Order.ProductSubtotal.ToString() + ")";
bll.dbUpdate(sql);

它工作正常,但它也输出以下sql 错误：

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.

那是对的吗？我如何调整代码来解决此问题？

任何帮助是极大的赞赏！

解决方法

非常确定pk_OrderID是AC_Shipping_Addresses的PK

并且您正尝试通过_Order.OrderNumber插入副本

做一个

select * from AC_Shipping_Addresses where pk_OrderID = 165863;

或选择计数(*)….

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

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

如果你想不插入,如果有一行

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

sql-server – 违反PRIMARY KEY约束.无法在对象中插入重复键 – SQL Server

解决方法

猜你在找的MsSQL相关文章