自动将主要对象分配到外部参考表中 [英] automatically asign primary into foreign reference table

问题描述

大家好，我是编程的初学者，很高兴认识像您这样的人.

请我如何从数据库中委派自动生成的主键，并将其分配给变量，然后将其插入到作为外键引用的表中，例如产品表和orderDetail表.

谢谢.

Hi all, I''m beginner in programming and am happy to have meet someone like you.

Please how do I delegate auto generated primary key from a database and assign it to a variable and insert it into a table which references it as a foreign key such as from product table and orderDetail table.

Thank you.

推荐答案

下面给我举一个例子.
说明:每当将记录插入表中时，都会获得相应的ID，以备将来使用.在这里，我们已将autogenerated(Identity)列创建为PK，在为OrderDetails表创建外键约束时引用了相同的PK.


创建表[dbo].[OrderInfo](
[OrderID] [int] IDENTITY(1,1)主键不为空，
[OrderDate] DATETIME NULL，
[CustomerID] int NOT NULL
)

GO


创建过程[dbo].[sp_Insert_OrderInfo]
(
@CustomerID int，
@OrderID int输出

)
AS
设置NOCOUNT ON

插入[dbo].[OrderInfo]
([OrderDate]
，[CustomerID]
)
值
(GETDATE()
，@CustomerID
)


SET @ OrderID = scope_identity()


GO

创建表[dbo].[OrderDetails](
[OrderID] [int]不为空，
[OrderValue]浮点NULL，
[ShippingAddress] nvarchar(4000)，
[ExpDelDate] DATETIME，
CONSTRAINT fk_OrderID外键(OrderID)
参考[dbo].[OrderInfo](OrderID)
)

GO

谢谢，

Kuthuparakkal

Let me give you an example(below).
Explanation: Whenever you insert a record to the table, you get the corresponding ID for it for future use. Here we have created autogenerated(Identity) column as PK, ans same PK is referenced when creating Foreign Key constraint for OrderDetails table.


CREATE TABLE [dbo].[OrderInfo](
[OrderID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[OrderDate] DATETIME NULL,
[CustomerID] int NOT NULL
)

GO


CREATE PROCEDURE [dbo].[sp_Insert_OrderInfo]
(
@CustomerID int,
@OrderID int OUTPUT

)
AS
SET NOCOUNT ON

INSERT INTO [dbo].[OrderInfo]
( [OrderDate]
, [CustomerID]
)
VALUES
( GETDATE()
, @CustomerID
)


SET @OrderID= scope_identity()


GO

CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[OrderValue] Float NULL,
[ShippingAddress] nvarchar(4000),
[ExpDelDate] DATETIME,
CONSTRAINT fk_OrderID FOREIGN KEY (OrderID)
REFERENCES [dbo].[OrderInfo](OrderID)
)

GO

Thanks,

Kuthuparakkal

这篇关于自动将主要对象分配到外部参考表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！