如何将特定值多次插入到DB的主键值中 [英] How do I insert specific value multiple times into a primary key value of a DB
问题描述
我正在使用零售应用程序,每次尝试在单次销售中多次输入相同产品代码的特定产品时,我收到错误:'违反主键约束。不能插入重复的密钥'
任何对此的帮助都将受到高度赞赏。
谢谢。
我尝试过:
我已从数据库中的productcode字段中删除了主键。这不起作用。
这是将销售订单保存到数据库的功能:
公共共享函数SaveOrder(ByVal newOrder As EkOrderClass)As Integer
GetCon()
opencon()
Dim TR As SqlClient。 SqlTransaction = con.BeginTransaction
CMD.Transaction = TR
cmd.CommandText =INSERT TBL_ORDER(EmpID,OrderDate,OrderTime)VALUES(@ EncID,@ orderDate,@ OrderTime); SELECT Scope_Identity()
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue(@ EmpId,frmSales.lblUser.Text)
cmd.Parameters.AddWithValue(@ orderDate,今天)
cmd.Parameters.AddWithValue(@ OrderTime,frmSales.lblTime.Text)
Dim OrderID As Int32
尝试
OrderID = System.Convert。 ToInt32(cmd.ExecuteScalar)
MyOrderId = OrderID
Catch ex As Exception
TR.Rollback ()
closecon()
抛出
结束尝试
每个det作为EkOrderDetail在newOrder.Details
cmd.CommandText = INSERT [TBL_ORDER_DETAILS](OrderID,ProductCode,UnitPrice,Quantity)VALUES(@ OrderID,@ productCode,@ price,@ quantity)
CMD.Parameters.Clear()
cmd.Parameters.AddWithValue( @orderID,MyOrderId)
cmd.Parameters.AddWithValue(@ productCode,det.ProductCode)
CMD.Parameters.AddWithValue(@ price,det.ProductPrice)
CMD .Parameters.AddWithValue(@ quantity,det.ProductQuantity)
尝试
CMD.ExecuteNonQuery()
Catch ex As Exception
TR.Rollback()
closecon()
抛出
结束尝试
下一个
TR.Commit()
closecon()
返回Ord erID
结束功能
我试图设置
IGNORE_DUP_KEY = ON
但这会从订单中排除多次插入的任何项目。
请帮助
谢谢
主键始终具有UNIQUE约束:这意味着您不能拥有多个具有特定值的行。当您尝试时,您会收到错误消息:
违反主键约束。无法插入重复的密钥这意味着您的数据库设计错误:您应该至少有两个表:
产品:
ID(主键)说明
SK0012大型塑料香蕉套装(6)
SK0013大型塑料香蕉套装(12)
SK0142大象,现场, 3吨。和销售表:
销售额:
ID产品日期数量TotalPrice
1001 SK0013 2017-03-31 5 100.00
1002 SK0012 2017-03-31 2 20.00
1003 SK0013 2017-04-01 1 20.00
1004 SK0013 2017-04-01 5 100.00
Sales.ID字段是一个IDENTITY字段,SQL将为您维护。
有意义吗?
是的先生,这很有道理。但在我的情况下,我有两个表用于订购系统,即订单和订单详细信息表
订单
IrderId int NotNull ----------->主键
EmpId Varchar(50)notnull
OrderDate date notnull
OrderTime nchar(20)null
- ---------------------------------------
OrderDetails
OrderId int notnull ----------------->主键
ProductCode int notnull ------------->主键
UnitPrice十进制(18,0)notnull
这是不对的。一个表只能有一个PRIMARY KEY,就像一个种族只有一个胜利者一样。您需要三张桌子:
订单
订单详情
产品
试一试:
订单
Id int NotNull ----------->主键
EmpId Varchar (50)notnull
OrderDate DATETIME notnull
OrderDetails
Id int notnull ---- ------>主键
OrderId int NotNull -----> Orders.ID的外键
ProdID int notnull ------> Products.ID的外键
数量INT NotNull
TotalPrice decimal(18,0)notnull
产品
< pre lang =text> Id int notnull ---------->主键
Desc NVARCHAR(256)
UnitPrice decimal(18,0)notnull
Quote:如何将特定值多次插入数据库的主键值
你不要这样做!
按设计主键仅允许UNIQUE值。每个值只有一次。
只允许在非主键中多次使用相同的值。
即SQL 101!
问题是你的数据库设计。
I am working a retail application and each time i try to enter a particular product of the same product code multiple times in a single sale, i received the error: 'Violation of primary key constraint. cannot insert duplicate key'
Any help on this will be highly appreciated.
Thanks.
What I have tried:
I have removed the primary key from productcode field in the database. This did not work.
Here is the function that save the sales order to the database:
"Public Shared Function SaveOrder(ByVal newOrder As EkOrderClass) As Integer GetCon() opencon() Dim TR As SqlClient.SqlTransaction = con.BeginTransaction CMD.Transaction = TR cmd.CommandText = "INSERT TBL_ORDER (EmpID, OrderDate, OrderTime) VALUES (@EmpID, @orderDate, @OrderTime);SELECT Scope_Identity()" cmd.Parameters.Clear() cmd.Parameters.AddWithValue("@EmpId", frmSales.lblUser.Text) cmd.Parameters.AddWithValue("@orderDate", Today) cmd.Parameters.AddWithValue("@OrderTime", frmSales.lblTime.Text) Dim OrderID As Int32 Try OrderID = System.Convert.ToInt32(cmd.ExecuteScalar) MyOrderId = OrderID Catch ex As Exception TR.Rollback() closecon() Throw ex End Try For Each det As EkOrderDetail In newOrder.Details cmd.CommandText = "INSERT [TBL_ORDER_DETAILS] (OrderID, ProductCode, UnitPrice, Quantity) VALUES (@OrderID, @productCode, @price, @quantity)" CMD.Parameters.Clear() cmd.Parameters.AddWithValue("@orderID", MyOrderId) cmd.Parameters.AddWithValue("@productCode", det.ProductCode) CMD.Parameters.AddWithValue("@price", det.ProductPrice) CMD.Parameters.AddWithValue("@quantity", det.ProductQuantity) Try CMD.ExecuteNonQuery() Catch ex As Exception TR.Rollback() closecon() Throw ex End Try Next TR.Commit() closecon() Return OrderID End Function
I have tried to setIGNORE_DUP_KEY = ONbut this exclude from the Order details any items inserted multiple times.
Please help
Thanks解决方案A primary key always has the UNIQUE constraint: which means that you can't ever have more than one row with a particular value. When you try, you get the error message:
Violation of primary key constraint. cannot insert duplicate keyWhat this means in your case is that your database design is wrong: you should have at least two tables:
Products:
ID(primary key) Description SK0012 Set of big plastic bananas (6) SK0013 Set of big plastic bananas (12) SK0142 Elephant, live, 3 tonnes.And a sales table:
Sales:
ID ProdID Date Quantity TotalPrice 1001 SK0013 2017-03-31 5 100.00 1002 SK0012 2017-03-31 2 20.00 1003 SK0013 2017-04-01 1 20.00 1004 SK0013 2017-04-01 5 100.00
The Sales.ID field is an IDENTITY field, which SQL will maintain for you.
Make sense?
Yes sir, It make lots of sense. But in my case, I have two tables for the Ordering system, i.e Order and Order details tables
Order IrderId int NotNull ----------->Primary Key EmpId Varchar(50) notnull OrderDate date notnull OrderTime nchar(20) null ----------------------------------------- OrderDetails OrderId int notnull -----------------> Primary Key ProductCode int notnull -------------> Primary Key UnitPrice decimal(18,0) notnull
That's not right. A table can only have one PRIMARY KEY, in the same way that a race only has one winner. You need three tables:
Orders
OrderDetails
Products
Try this:
Orders
Id int NotNull ----------->Primary Key EmpId Varchar(50) notnull OrderDate DATETIME notnull
OrderDetails
Id int notnull ----------> Primary Key OrderId int NotNull -----> Foreign key to Orders.ID ProdID int notnull ------> Foreign key to Products.ID Quantity INT NotNull TotalPrice decimal(18,0) notnull
Products
Id int notnull ----------> Primary Key Desc NVARCHAR(256) UnitPrice decimal(18,0) notnull
Quote:How do I insert specific value multiple times into a primary key value of a DB
YOU DON'T !
By design primary key allow only UNIQUE values. Each value is only 1 time.
Same value multiple times is allowed only in non primary key.
That is SQL 101 !
The problem is your database design.
这篇关于如何将特定值多次插入到DB的主键值中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!