CODEWITHSUNDEEP
sql
arthur
arthur

Reputation: 1

Compare rows between 2 tables

I am new to SQL and I need to build a database for a grocery store(not real, just a course assignment)

i have two fields from two different tables - supplied price - the price that the store buys from the supplier and price that is given to the customers

How can I make a constraint that insures that supplied price is lower then the price that is given to the customers?

The relevant tables that I have are:

CREATE TABLE Supplied_Products(
[Supplier ID]   Int NOT NULL  Foreign Key References Suppliers,
[Product ID]    Int NOT NULL Foreign Key References Products,
Price           Float NOT NULL,
CHECK (Price>0),
Constraint PK_Supplied_Products PRIMARY KEY([Supplier ID] ,[Product ID])
)
CREATE TABLE Products(
[Product-ID]    Int NOT NULL PRIMARY KEY,
[Product Name]  Varchar(20) NOT NULL,
Price           Float NOT NULL,
[Category-Name] Varchar(20) NOT NULL Foreign Key References Categories,
[Weight]        Float NOT NULL,
[Is Refrigirated] Varchar(1) DEFAULT 'N'
CHECK ([Is Refrigirated] in('Y','N')),/* Is Refrigirated can be only Y-yes or N-no*/
CHECK (Price >0)
)

Upvotes: 0

Views: 215

Answers (2)

codymanix
codymanix

Reputation: 29520

You can use stored procedures for inserting and altering rows in both tables which checks this constraint.

Upvotes: 0

codingbadger
codingbadger

Reputation: 44032

For MS SQL Server you can't use a CHECK constraint if you want to compare data in a different table.

In this scenario I would think an INSERT & UPDATE Trigger would be required to check the value being updated. You could then cancel the update/insert if the supplier price is more than the customer price.

Information on Triggers can be found here

Upvotes: 2

Related Questions