CODEWITHSUNDEEP
sql-servert-sql
kingcobra1986
kingcobra1986

Reputation: 971

How to create a computed column that references another column from another table?

I am using Microsoft SQL Server and I am still learning. I have two tables, a product table and an order details table. The product table contains a price and then the order details table has a reference to the the product, the quantity of the product and the total price. Here are the two tables:

--Create Product Table
CREATE TABLE Products.Product  
(
    product_id          INT             NOT NULL    PRIMARY KEY IDENTITY,
    product_name        VARCHAR(40)     NOT NULL,
    product_desc        VARCHAR(5000),
    product_price       SMALLMONEY      NOT NULL    CHECK (product_price >= 0)
);

--Create Order Details Table
CREATE TABLE Orders.Order_detail 
(
    order_detail_id     INT             NOT NULL    PRIMARY KEY IDENTITY,
    product_id          INT             NOT NULL,
    product_quantity    INT             NOT NULL    CHECK (product_quantity >= 0),
    order_detail_total  MONEY           NOT NULL,

    FOREIGN KEY (product_id)        REFERENCES Products.Product
);

How can I make it so that order_detail_total is a computed column that is the product_price * product_quantity?

Upvotes: 3

Views: 1283

Answers (1)

kingcobra1986
kingcobra1986

Reputation: 971

Thanks to @Andy K, I came up with this solution:

--Create Product Table
CREATE TABLE Products.Product  
(
    product_id          INT             NOT NULL    PRIMARY KEY IDENTITY,
    product_name        VARCHAR(40)     NOT NULL,
    product_desc        VARCHAR(5000),
    product_price       SMALLMONEY      NOT NULL    CHECK (product_price >= 0)
);
GO

--Create a function to calculate the order details total
CREATE FUNCTION Orders.calcOrderDetailTotal(@quantity INT, @productId INT)
RETURNS MONEY
AS
BEGIN
    DECLARE @price SMALLMONEY
    SELECT @price = product_price FROM Products.Product AS TP 
           WHERE TP.product_id = @productId
    RETURN @quantity * @price
END
GO

--Create Order Details Table
CREATE TABLE Orders.Order_detail 
(
    order_detail_id     INT             NOT NULL    PRIMARY KEY IDENTITY,
    product_id          INT             NOT NULL,
    product_quantity    INT             NOT NULL    CHECK (product_quantity >= 0),
    order_detail_total  MONEY           NOT NULL,

    FOREIGN KEY (product_id)        REFERENCES Products.Product
);

This worked for me on a test db that I created.

Upvotes: 1

Related Questions