0

I am trying to replace simple CHECK constraint with an embedded function within a CHECK constraint however it doesn't seem to restrict the data I can enter. The constraint is to prevent product amount of less than 0.25 and more than 5,000. The original check worked fine, however the function doesn't seem to do anything at all.

The original constraint:

ALTER TABLE Prices
ADD CONSTRAINT CheckPrices CHECK ((Amount > 0.25) AND (Amount <= 5000.00))

The function:

ALTER FUNCTION dbo.CheckProductPrices
(
@productSKU int,
@priceDate smalldatetime
)
RETURNS bit
AS
BEGIN

DECLARE @retVal bit = 0

SELECT @retVal = CASE WHEN Amount > 0.25 AND Amount <= 5000.00 THEN 1 ELSE 0 END
FROM Prices
WHERE productSKU = @productSKU
AND priceDate = @priceDate

RETURN @retVal
END

The new CHECK constraint:

ALTER TABLE Prices
    ADD CONSTRAINT CheckPrices CHECK (dbo.CheckProductPrices([productItem], [priceValidDate]) = 1)

I don't understand why the new constraint isn't stopping invalid prices the way the original constraint did.

Marc B
  • 356,200
  • 43
  • 426
  • 500
user3062656
  • 1
  • 1
    Is `@productSKU` unique? Also can there be multiple rows returned given an `@productSKU` and `@priceDate`? – Ric Dec 03 '13 at 18:31
  • Is there a real need to replace the existing check? Checking with function might be slower. What if SELECT inside function returns more than one row in result? What if it returns 0 rows? You should take all of that in consideration before ditching the old check constraint. – Ivan Golović Dec 03 '13 at 18:33
  • @IvanG that's what I was getting at with my comment re multiple rows. The first check constraint seems like a much better idea than using a function. – Ric Dec 03 '13 at 18:36
  • `@productSKU` and `@priceDate` together make up the unique primary key for a given row. And it's more of a learning exercise than for practical purposes, I want to see how functions could be used in such a situation. – user3062656 Dec 03 '13 at 18:41
  • when you say "the new constraint isn't stopping invalid prices the way the original constraint did." what is happening? – Ric Dec 03 '13 at 18:46
  • With the old constraint for instance, when I would try to insert a value of 10,000 for Amount, an error would say something like "the data is not committed, the update conflicted with the constraint CheckPrices and the statement has been terminated". This is what is suppose to happen as it doesn't meet the check constraint requirements. However with the new constraint, even though the function by itself reports the 10,000 value is false when compared to the CASE, the check constraint doesn't appear to do anything and the 10,000 value is allowed to be updated. – user3062656 Dec 03 '13 at 19:04
  • try the fiddle: http://www.sqlfiddle.com/#!3/720ba/3 everything seems fine to me. Are you sure you're passing the correct column from the table in? – Ric Dec 03 '13 at 19:42
  • My suggestion is to add an unique constraint on ProductSKU and PriceDate. – bjnr Dec 03 '13 at 21:14

0 Answers0