I am working on an assignment and need your help with the following in SQL database:-
我正在做一个作业，需要您在SQL数据库中帮助:-
I have 3 tables
我有三个表
- Product
- 产品
- LintItem
- LintItem
- Invoice
- 发票
LineItem is a bride table and I need to insert data into LineItem but it requires ProductID and InvoiceNumber. In my case the Invoice table is emppty and it will be filled from the data that LineItem table passes.
LineItem是一个bride表，我需要将数据插入LineItem，但它需要ProductID和InvoiceNumber。在我的示例中，发票表是emppty，它将从LineItem表传递的数据中填充。
The problem is how can I create an invoice before having the data from the lineItem table?
问题是，在从lineItem表获取数据之前，如何创建发票?
I am using these table for online shopping cart.
我正在用这张桌子做网上购物车。
It's really hard for me to explain this problem. Hope you understand it, Thanks!
我真的很难解释这个问题。希望你能理解，谢谢!
1 个解决方案
#1
0
It sounds like you have a foreign key constraint forcing the existence of a Invoice record prior to inserting your line item records. It is hard to say exactly, based on the phrasing of your question but could be something like.
听起来您有一个外键约束，强制在插入行项记录之前存在一个发票记录。根据你问题的措辞很难准确地说出来，但也可能是类似的。
--Table variable to hold line items DECLARE @lineItems TABLE ( InvoiceNumber INT, Quantity INT )INSERT INTO @lineitems VALUES(1,1) INSERT INTO @lineitems VALUES(1,2)
插入@lineitems值(1,1)
--ADD INVOICE RECORD FIRST AND SUM Quantities etc.... INSERT INTO Invoice SELECT InvoiceNumber,SUM(Quantity) FROM @lineItems GROUP BY InvoiceNumber
——添加发票记录,和数量等....
--NOW YOU CAN ADD LINE ITEMS INSERT INTO LineItems SELECT * FROM @lineItems
——现在可以添加行项
This is a pattern you could use if that was your goal.
如果这是你的目标，你可以使用这种模式。
If you are wanting to insert these LineItems on the fly as the user is clicking Add from the webpage. I wouldn't use your LineItem SQL table for caching this way. Without knowing anything about your application it is hard to say but you really should be caching this data in the HTTP session or in the client as (array,json, local storage etc..). If you were to choose to do this as an SQL table just make a new LineItem without the constraints and then similarly per above you can use that table to insert into your LineItem table.
如果您想在用户点击网页添加时动态插入这些线项目。我不会使用LineItem SQL表来进行这种缓存。在不了解应用程序的情况下，这很难说，但是您确实应该将这些数据缓存到HTTP会话或客户端(数组、json、本地存储等)中。如果您选择将其作为SQL表，那么只需创建一个没有约束的新LineItem，然后类似地，您可以使用该表将其插入到LineItem表中。
#1
0
It sounds like you have a foreign key constraint forcing the existence of a Invoice record prior to inserting your line item records. It is hard to say exactly, based on the phrasing of your question but could be something like.
听起来您有一个外键约束，强制在插入行项记录之前存在一个发票记录。根据你问题的措辞很难准确地说出来，但也可能是类似的。
--Table variable to hold line items DECLARE @lineItems TABLE ( InvoiceNumber INT, Quantity INT )INSERT INTO @lineitems VALUES(1,1) INSERT INTO @lineitems VALUES(1,2)
插入@lineitems值(1,1)
--ADD INVOICE RECORD FIRST AND SUM Quantities etc.... INSERT INTO Invoice SELECT InvoiceNumber,SUM(Quantity) FROM @lineItems GROUP BY InvoiceNumber
——添加发票记录,和数量等....
--NOW YOU CAN ADD LINE ITEMS INSERT INTO LineItems SELECT * FROM @lineItems
——现在可以添加行项
This is a pattern you could use if that was your goal.
如果这是你的目标，你可以使用这种模式。
If you are wanting to insert these LineItems on the fly as the user is clicking Add from the webpage. I wouldn't use your LineItem SQL table for caching this way. Without knowing anything about your application it is hard to say but you really should be caching this data in the HTTP session or in the client as (array,json, local storage etc..). If you were to choose to do this as an SQL table just make a new LineItem without the constraints and then similarly per above you can use that table to insert into your LineItem table.
如果您想在用户点击网页添加时动态插入这些线项目。我不会使用LineItem SQL表来进行这种缓存。在不了解应用程序的情况下，这很难说，但是您确实应该将这些数据缓存到HTTP会话或客户端(数组、json、本地存储等)中。如果您选择将其作为SQL表，那么只需创建一个没有约束的新LineItem，然后类似地，您可以使用该表将其插入到LineItem表中。