如何对具有FK到IDENTITY PK的链接（联接）表执行INSERT / UPDATE [英] How to perform INSERT/UPDATE to Linking (Join) table which has FKs to IDENTITY PKs

问题描述

我对设计自己的数据库不熟悉。我的项目是使用SQL Server 2005的订单/产品/库存类型数据。在阅读期间，我遇到了几个客户/订单类型的示例，因此我决定对我使用链接表（有时称为联结表或联接表） Order_Details，因为我在ORDERS和PRODUCTS之间存在多对多关系。

I am new to designing my own database. My project is orders/products/inventory type data, using SQL Server 2005. During my reading I came across several customer/order type of examples, and I decided to use linking-table (sometimes here called junction, or join table) for my Order_Details since I had many-to-many relationships between ORDERS and PRODUCTS.

问题是，我不确定我可以使用哪种T-SQL结构或语法插入/更新此ORDER_DETAILS链接表。这些示例使您明显可以单独存储订单详细信息，但是我不知道如何使它与订单和产品表保持同步。我在想像某种将它们全部连接在一起的View，一个更新将写入所有三个表，但是我发现一个View只允许一次更新一个表。我通过在视图上使用代替触发器尝试了一种变通方法，但是我不知道如何在触发器中编写INSERT语句，因为我必须插入链接表的FK值是IDENTITY PK

The problem is, I’m not sure what T-SQL structure or syntax I can use to insert/update this ORDER_DETAILS linking-table. The examples make it seem obvious that you would store order details separately, but I don’t know how to keep it in sync with the Orders and Products tables. I was imagining some kind of View which would have them all joined together, where one update would write to all three tables, but I found that a View is only allowed to update one table at a time. I tried a "workaround" by using an "Instead OF" trigger on the view, but I can’t figure out how to write the INSERT statements in the trigger, since the FK values I must insert to the linking table are the IDENTITY PKs from the parent tables, hence are unknown ahead of time.

然后我想这可能是链接表上FK上的UPDATE CASCADE的情况，然后执行我在那儿的插入内容，但看起来还是很混乱。我买了一本书，花了很多时间学习设计标准化的数据库，但是现在我不知道如何在其中插入数据。

Then I thought maybe this would be a case of UPDATE CASCADE on the FKs at the linking table, and just perform my INSERTS there, but still it seems like very confusing. I bought a book, and spent a large amount of time learning to design a normalized database, but now I can't figure out how to insert data into it.

我的计划是稍后编写带有一些DatagridViews等的VB.NET 2005应用程序。用户可以在其中创建订单，更新产品等。应用程序，但是这次我想我宁愿学习设计一个合适的数据库，然后构建可以直接连接到应用程序控件的视图，即让SQL完成大部分工作。谁能说明保持这些链接表所必需的JOIN / UPDATE / SPROC / VIEW吗？谢谢。

My plan is to later write an VB.NET 2005 application with some DatagridViews, etc. where the user can create orders, update products, etc. I am much better with applications, but this time I thought I would rather learn to design a proper database, and then build Views which could just be connected to application controls, i.e. let SQL do most of the work. Can anyone please shed light on the JOIN/UPDATE/SPROC/VIEW which is necessary to maintain these Linking-tables? Thanks.

为帮助解释布局，以下是一些架构详细信息：

To help explain the layout, here are some schema details:

ORDERS
----------------
OrderID  PK
OrderDate
EmployeeID fk
CostCenterID fk
etc.


ORDER_DETAILS
-----------------------
OrderID  CPK/fk
ProductID CPK/fk
Qty
OrderDetailComment


PRODUCTS
---------------------
ProductID PK
PartNumber
ProductName
etc.

这是链接表的当前定义：

Here is the current definition for the linking table:

CREATE TABLE [Order_Details] (
    [OrderID] INTEGER NOT NULL,
    [ProductID] INTEGER NOT NULL,    
    [Qty] INTEGER NOT NULL,
    [OrderDetailComment] VARCHAR(100),
    CONSTRAINT [CPK_Order_Details] PRIMARY KEY ([OrderID],[ProductID]),
    CONSTRAINT [Order_Details_Orders] FOREIGN KEY ([OrderID]) REFERENCES [Orders] ([OrderID]),
    CONSTRAINT [Order_Details_Products] FOREIGN KEY ([ProductID]) REFERENCES [Products] ([ProductID])    
)
GO

推荐答案

您打算使用哪种客户端/数据库访问技术？

What kind of client / database access technology do you plan to use?

如果您使用类似Linq的东西-to-SQL或Entity Framework-您甚至不必为这些问题而绞尽脑汁-一切都对您而言很好。

If you use something like Linq-to-SQL or the Entity Framework - you won't even have to rack your brain about those questions - all is handled nicely for you.

基本上，这些步骤会是：

Basically, the steps have to be:

• 插入订单并获取订单ID-执行此操作的方式


• 然后插入所有订单详细信息（假设您具有产品ID），现在又有了订单ID

要存储订单，您可以：

• 使用存储过程返回新创建的OrderID（使用INSERT语句上的 OUTPUT子句，或签出 SCOPE_IDENTITY（））


• 创建执行上述操作之一的内联SQL语句

因此，可以使用类似的方法（作为直接内联SQL或包装在存储的proc中-您可以选择）：

So, either use something like (as a direct inline SQL, or wrapped up in a stored proc - your choice):

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT Inserted.OrderID
VALUES (........)

或只是做

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
VALUES (........)

DECLARE @NewOrderID INT
SET @NewOrderID = SCOPE_IDENTITY()

这有帮助吗？

更新：

如果要在后续的T-SQL代码中使用新插入的ID，最好的选择是将它们存储在内存中的表变量中，如下所示：

UPDATE:
If you want to use the newly inserted ID in subsequent T-SQL code, your best option is to store them into a in-memory table variable - something like this:

DECLARE @NewOrderIDs TABLE (NewOrderID INT)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

SELECT * FROM @NewOrderIDs 

如果您只想插入一个订单，则获取其新的OrderID，然后继续使用SCOPE_IDENTITY（）可能更容易：

If you want to just insert a single order, gets its new OrderID, and then keep going inserting the order details, using the SCOPE_IDENTITY() might be easier:

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
VALUES  (..........)

DECLARE @NewOrderID INT
SET @NewOrderID = SCOPE_IDENTITY()

INSERT INTO dbo.OrderDetails(.....)
........

这篇关于如何对具有FK到IDENTITY PK的链接（联接）表执行INSERT / UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！