Insert new record with composite primary key, which consists of two foreign keys from different tables

Question

Please help me figure out how to Insert new record with composite primary key, which consists of two foreign keys from different tables. I am working in C#, WPF if that matters.

I have three tables: Sales, SaleItem, Item.

CREATE TABLE [dbo].[Sales] (
    [saleID]     INT      IDENTITY (1, 1) NOT NULL,
    [saleTime]   DATETIME NOT NULL,
    [customerID] INT      NULL,
    [TIN]        INT      NOT NULL,
    CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ([saleID] ASC),
    CONSTRAINT [FK_Sales_Customers] FOREIGN KEY ([customerID]) REFERENCES [dbo].[Customers] ([customerID]),
    CONSTRAINT [FK_Sales_Company] FOREIGN KEY ([TIN]) REFERENCES [dbo].[Company] ([TIN])
);



CREATE TABLE [dbo].[Item] (
    [ItemSKU]     INT           IDENTITY (1, 1) NOT NULL,
    [itemName]    NVARCHAR (50) NOT NULL,
    [volume]      FLOAT (53)    NOT NULL,
    [measureUnit] NVARCHAR (50) NOT NULL,
    [producer]    NVARCHAR (50) NOT NULL,
    [supplierID]  INT           NOT NULL,
    [retailPrice] NUMERIC (18)  NOT NULL,
    CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ([ItemSKU] ASC),
    CONSTRAINT [FK_Item_Suppliers] FOREIGN KEY ([supplierID]) REFERENCES [dbo].[Suppliers] ([supplierID])
);

 CREATE TABLE [dbo].[SaleItem] (
        [saleID]   INT IDENTITY (1, 1) NOT NULL,
        [itemSKU]  INT NOT NULL,
        [quantity] INT NOT NULL,
        CONSTRAINT [PK_SaleItem] PRIMARY KEY CLUSTERED ([saleID] ASC, [itemSKU] ASC),
        CONSTRAINT [FK_SaleItem_Sales] FOREIGN KEY ([saleID]) REFERENCES [dbo].[Sales] ([saleID]),
        CONSTRAINT [FK_SaleItem_Item] FOREIGN KEY ([itemSKU]) REFERENCES [dbo].[Item] ([ItemSKU])
    );

I want to insert a new record into SaleItem table (the third one) where saleID is the last ID recorded in Sales table and ItemSKU which is equal to the value I get from another window.

I want these values:

SaleID = SELECT TOP 1 saleID FROM Sales ORDER BY saleID DESC";
ItemSKU = "SELECT itemName FROM Item WHERE ItemSKU = @sku";

I think I must do it in one query but I have no idea how. Can you please give me a hint? I

RichardCL · Accepted Answer · 2016-04-28T13:43:15.497

First, you need to remove the IDENTITY property from the dbo.SaleItem table. The IDENTITY property is only required on the parent table, dbo.Sales.

You can do a single INSERT statement like this. It uses two subqueries, which are the SELECT statements in parentheses, to get values from the other two tables.

INSERT INTO dbo.SaleItem (saleID, itemSKU, quantity)
    VALUES ((SELECT MAX(saleID) FROM dbo.Sales),
        (SELECT ItemSKU FROM dbo.Item WHERE itemName = N'Widget'),
        50);

You might want to turn it into a stored procedure, like this:

CREATE PROCEDURE dbo.up_InsertSaleItem
(
    @itemName nvarchar(50),
    @quantity int
)
AS
INSERT INTO dbo.SaleItem (saleID, itemSKU, quantity)
    VALUES ((SELECT MAX(saleID) FROM dbo.Sales),
        (SELECT ItemSKU FROM dbo.Item WHERE itemName = @itemName),
        @quantity);

Then to use the stored procedure:

-- Test the stored procedure
EXEC dbo.up_InsertSaleItem @itemName=N'Widget', @quantity=50;

SELECT *
    FROM dbo.SaleItem;

To read more about subqueries, see Microsoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan, Chapter 4: Subqueries.

Insert new record with composite primary key, which consists of two foreign keys from different tables

1 Answers1