THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

When CHECK constraints using UDFs fail for multirow updates

I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails.

Suppose you need to move some stuff in your car, and you don't want to load more than 100 pounds into your small car at a time. Here is the DDL:

CREATE SCHEMA Data AUTHORIZATION dbo;
GO
CREATE SCHEMA Readers AUTHORIZATION dbo;
GO
CREATE TABLE Data.ItemsToMove(
  
TripNumber INT NOT NULL,
  
ItemName VARCHAR(50),
  
ItemWeightInPounds FLOAT
);
GO
CREATE FUNCTION Readers.TotalWeightPerTrip(@TripNumber INT)
RETURNS FLOAT
AS
BEGIN
RETURN 
(SELECT SUM(ItemWeightInPounds
  
FROM Data.ItemsToMove
  
WHERE TripNumber @TripNumber);
END
GO
ALTER TABLE Data.ItemsToMove
  
ADD CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds
  
CHECK (Readers.TotalWeightPerTrip(TripNumber) <= 100);
GO
  

Loading 90 pounds succeeds, but when you go over the limit, the constraint works:

 

INSERT INTO Data.ItemsToMove(TripNumber,
  
ItemName,
  
ItemWeightInPounds)
SELECT 1'Big Box with Clothes'40
UNION ALL
SELECT 1'Lumbar Aeron Chair'25
UNION ALL
SELECT 1'Basic Aeron Chair'25;

INSERT INTO Data.ItemsToMove(TripNumber,
  
ItemName,
  
ItemWeightInPounds)
SELECT 1'Small Box with Books'40;
  

 Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

So, all other items should be moved on the second trip:

INSERT INTO Data.ItemsToMove(TripNumber,
  
ItemName,
  
ItemWeightInPounds)
SELECT 2'Small Box with Books'40
UNION ALL
SELECT 2'Humidifier'40;
  

Now there is another problem: all bulky items are scheduled to move on the first trip, while small and hevy ones get on the second trip. Suppose you cannot fit the bulky box with clothes after you loaded your chairs, and want to move your small humidifier on your first trip instead:

UPDATE Data.ItemsToMove 
SET TripNumber CASE WHEN ItemName 'Humidifier' THEN 1
  
WHEN ItemName 'Big Box with Clothes' THEN 2
  
ELSE TripNumber END
WHERE  
ItemName IN ('Humidifier''Big Box with Clothes') AND TripNumber IN(1,2); 
 

 

The update should succeed, because the box and the humidifier have the same weight. However, the update fails:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CHK_TotalWeightPerTrip_LessThan100pounds". The conflict occurred in database "Test", table "data.ItemsToMove", column 'TripNumber'.
The statement has been terminated.

The reason is simple: the database engine modifies just one row and immediately invokes the CHECK constraint for that row. At that moment the limit for total load is exceeded no matter which row is modified first - that is the way I made up this example. You can run the following scripts and see for yourself:

ALTER TABLE Data.ItemsToMove
  
DROP CONSTRAINT CHK_TotalWeightPerTrip_LessThan100pounds;

BEGIN TRAN


UPDATE 
Data.ItemsToMove 
SET TripNumber CASE WHEN ItemName 'Humidifier' THEN 1
  
ELSE TripNumber END
WHERE  
ItemName 'Humidifier' AND TripNumber 2;


SELECT TripNumberSUM(ItemWeightInPounds
  
FROM Data.ItemsToMove
  
GROUP BY TripNumber;


ROLLBACK;

BEGIN TRAN


UPDATE 
Data.ItemsToMove 
SET TripNumber CASE 
  
WHEN ItemName 'Big Box with Clothes' THEN 2
  
ELSE TripNumber END
WHERE  
ItemName 'Big Box with Clothes' AND TripNumber 1;


SELECT TripNumberSUM(ItemWeightInPounds
  
FROM Data.ItemsToMove
  
GROUP BY TripNumber;


ROLLBACK;
  

 At this point the faulty constraint is dropped, so you can rerun your multi-row update:

UPDATE Data.ItemsToMove 
SET TripNumber CASE WHEN ItemName 'Humidifier' THEN 1
  
WHEN ItemName 'Big Box with Clothes' THEN 2
  
ELSE TripNumber END
WHERE  
ItemName IN ('Humidifier''Big Box with Clothes') AND TripNumber IN(1,2); 
  

 Create the constraint again, it will succeed. Verify that the constraint is trusted:

SELECT nameis_not_trusted
FROM   sys.check_constraints
WHERE Name='CHK_TotalWeightPerTrip_LessThan100pounds';
 

Make sure you understand why you need to know if your constraints are trusted.

You can also verify that your data is clean, that the load for every trip does not exceed 100 pounds:

 SELECT TripNumberSUM(ItemWeightInPounds
  
FROM Data.ItemsToMove
  
GROUP BY TripNumber;


 

Published Wednesday, July 01, 2009 10:08 AM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

As always great post, thanks

July 1, 2009 10:40 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement