Check Constraints in SQL

Management wants to implement checks to ensure that the price customers are charged falls within the 20 percent of the product’s cost.

Desired output : if NETAMT on SALES is less than 20% of the Product Value it rollback the transaction and throw an error or Print Msg : "Can not be sold at this price"

Reference Information

INITIAL Source Data:

PRODUCTS
PRODID	QUANTITY	VALUE		INSERT DATE		DESCRIPTION		UPDATE
1		4		$10		May 1, 2012		Toy Car			May 23, 2012
3		7		$12		May 3, 2012		Toy Truck		May 23, 2012
4		1		$11		May 2, 2012 		Electric Motor		May 23, 2012
5		5		$5		May 5, 2012 		Remote Control		May 23, 2012
6		13		$2		May 1, 2012 		AA Battery		May 23, 2012
	
CUSTOMER
CUSTID		NAME		ADDRESS		REGION		INSERT		UPDATE
3453		Bob Nobody	123 There St.		Western Canada	Jan 2, 2012
1243		Joe Someone	567 Here St.		Western Canada	Jan 2, 2012
678		Pete Peter	986 Nice Ave.		Eastern Canada		Jan 3, 2012
876		P. Reaber	1 Royal St.		Eastern Canada		Jan 5, 2012
987		Frank Bills	12 York Ave.		Eastern Canada		Jan 2, 2012

SALESPERSONS
SALEID		NAME		MANAGERID		REGION		INSERT		UPDATE
1		Joe Sales	1			Western Canada	Jan 1, 2012 
2		Barry Norton	1			Western Canada	Jan 1, 2012
3		Mike Nash	1			Western Canada	Jan 1, 2012
4		Jane Sales	4			Eastern Canada		Jan 1, 2012
5		Nancy Wills	4			Eastern Canada		Jan 1, 2012
6		Peter Parker	4			Eastern Canada		Jan 1, 2012
7		Billy Boss	4			Eastern Canada		Jan 1, 2012

SALES
PRODID	QUANTITY	NETAMT	CUSTID		SALEID		INSERT	
1		1		12		3453		2		May 24, 2012	
3		1		13		876		6		May 21, 2012 
5		2		12		987		7		May 13, 2012 
5		3		16		1243		3		May 8, 2012 
6		3		8		678		5		May 12, 2012 
6		1		3		1243		3		May 10, 2012 
6		1		3		987		6		May 11, 2012 

Open in new window

STARS1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
don't you mean greater than 20% of the units cost or unitcost*1.2 is minimum
Mike EghtebasDatabase and Application DeveloperCommented:
Hi David,

A constraint (trigger) to be installed to pop up a message when user enters price over 20% per email exchanges I had this morning with STARS1.

I suppose, STARS1 needs to supply his update routine to embed this functionality in his update routine.

Mike
arnoldCommented:
To get the message, you need to implement the check on the interface side which can be pushed to the validation if the item value is included in the interface tge says rep sees.
If you are defining a transaction, you do not need a constraint as the transaction will need to check the defined rules.
The flow of data entry presumably is such that the items are selected to build the items desired and only submitted as a whole. The purchase is all or nothing validation.
I.e. The sales rep transaction must be correct for all items, and quantities.

Let me see whether a constraint on a column referencing an external column in a separate table can be done.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Mike EghtebasDatabase and Application DeveloperCommented:
@STARS1,

You wrote: "... SALES is less than 20% of the Product Value it rollback the transaction...."

1. arnold is looking into producing a message like "Can not be sold at this price" which has nothing to do with rollback of a transaction coding.

2. If your front-end is using ADO.net + stored procedure for example to update the tables, the ADO.net portion could be controlling the transaction and the messaging has to be incorporated with it to roll back a transaction.

3. If your front end is using a stored procedure only to update the tables, then the entire rollback capability is built into that stored procedure.

For situations 2 or 3 above, you need to supply your related code for modification and add the applicable TAs like .net, aps.net, etc. to your question (possibly to a new question).

mike
arnoldCommented:
The puzzling thing pushing the enforcement all the way to the SQL is the portion I am questioning.
The definition of a transaction is that it will not be committed if there is an error which an attempt to record an event that the constraint if setup will prevent.

This formulation suggests that one would need to include a constraint to make sure that more items than available are sold.......

Pushing the message back is a function of capturing responses.

On the front end there are two possible validation options, one using client side scripting as the optimal mechanism. The next deals with server side validation where client side validation is not available (client side scripts disabled).
arnoldCommented:
A check constraint is what would be needed, but he check is dynamic based on data from two columns.
You have to based on the itemID get the value that is then enforced to be at least 1.2*value

https://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx
Mike EghtebasDatabase and Application DeveloperCommented:
@STARS1,

Suppose your boss says never mind about that 20% issue (for a minute). When user enters sales information on some front-end (asp.net etc.) how date gets updated in SQL Server?

Most likely, there is a stored procedure handles updating the tables and includes transaction rollback etc.

Please get hold of that stored procedure so we can revise to include messaging you have specified under condition you are asking for.

If ado.net is used (to control) transaction then we may need that piece of front-end code calling the updating stored procedure. But for now, let's focus on the updating stored procedure. Post its T-SQL here.

Mike
STARS1Author Commented:
This is just a practice type exam question, no front end involved.

 "NETAMT" in "SALES" table should be within 20% of the "Value" each time a product is sold so no product will be sold less then 20% of the product cost

"Value" is given in Products table

So each time sale is being made and before it inserts the "NETAMT" in "SALES" table should check if the NETAMT is not less then 20% of the "Value" of that product.

May be trigger on Insert or CHECK Constraint can do that but I don't know how
arnoldCommented:
a trigger or a check constraint function that will query that will compare the thresholds for price offered versus value*1.2
You would need to pass the itemID being sold and the price for which it is being sold to the check function.

You should make the comtext of your question clear.
Andrei FomitchevCommented:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE
-- ALTER
TRIGGER sales_iu 
   ON sales 
   AFTER INSERT,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	IF (SELECT i.netamt/i.quantity/p.Value FROM inserted i JOIN Products p ON i.ProdId = p.ProdId) < 0.2
	RAISERROR('Can not be sold at this price',16,1)
END
GO

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.