Check constraints and an overlooked issue
Posted by decipherinfosys on December 20, 2008
We have covered check constraints and their usefulness in some of our blog posts in the past. Ran into an issue at a client site the other day where the DBA had used a UDF (User Defined Function) in the check constraint and it was not doing what the DBA had thought that it would do. Before we present the issue, it is important to know that the check constraints in SQL Server are evaluated one row at a time so if you are planning to use a UDF which spans multiple rows and is based on SET logic, then you may not be getting the results that you thought you would.
Here is an example of t he issue that our client was facing:
CREATE TABLE dbo.GENERAL_LEDGER (GENERAL_LEDGER_ID INT IDENTITY PRIMARY KEY, AMT NUMERIC(7,2) NOT NULL DEFAULT 0, AP_AR BIT);
The table is a simple General Ledger table with the amount and the AP/AR column flag. Let’s create a function which checks to make sure that the amount is always 0 (picking up a hypothetical condition to show the point that we are trying to make here).
CREATE FUNCTION dbo.GL_Sum
DECLARE @amt INT;
SELECT @amt = (SELECT SUM(AMT) FROM dbo.GENERAL_LEDGER);
Now, let us insert some data first:
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (1000, 1);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (-1000, 2);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (10, 1);
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR) VALUES (-10, 2);
And now, let us add a check constraint using the UDF that we created above.
ALTER TABLE dbo.GENERAL_LEDGER ADD CONSTRAINT CK_AMT CHECK (dbo.GL_SUM() = 0);
Now, let’s try to update all the records with a positive number hence violating the constraint:
UPDATE dbo.GENERAL_LEDGER SET AMT = abs(AMT)
And we will get this error:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_AMT”. The conflict occurred in database “DecipherTest”, table “dbo.GENERAL_LEDGER”.
The statement has been terminated.
And that is perfectly fine but imagine loading up data in bulk from another table by doing a “Insert into general_ledger () select … from” statement. Let’s try to simulate that by trying to insert 2 records which will satisfy the business rule as well since we will have a +ve value in one and a -ve in the other one.
INSERT INTO dbo.GENERAL_LEDGER (AMT, AP_AR)
SELECT 90, 1
SELECT -90, 2
However, this time also it will give us the same error as before even though the business rule is not really violated since this is part of the same transaction. So, the constraint is really getting checked per row rather than by one single unit of work. This is just one of the issues that we have seen a couple of times at different client sites and hence wanted to bring it to the attention of our readers. What happens then is that you end up with logic that does not work the way you thought it would. If there is a need for a business rule like the one above, and you do want to handle it in the DB layer, you can do so by using a trigger. Triggers are fired per statement and not per row.