ANSI/ISO SQL defines CHECK constraints very simply as:
<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>
where <search condition> is a boolean expression, including query expressions.
In SQL Server, Oracle and many other DBMSs, queries aren't permitted in constraints. CHECK constraints can only reference the table against which they are defined and they can only refer to column names as if they are scalar values - i.e. the values for a single row at a time. This isn't good enough. Often we want to implement business rules that refer to more than one table or to selections or aggregations of rows from the table in question. For example, the rule "Ensure that sum of orders cannot exceed the customer's credit limit" or "Ensure that total X = total Y".
In the past, triggers have been one common solution for these types of requirement. But when SQL Server 2000 introduced user-defined functions, some people tried another way to get around the limitations of CHECK constraints: put a query in a scalar function, then reference the function in a CHECK constraint.
I've already mentioned something that hints at the flaw in such a solution. SQL Server's CHECK constraints were designed to be evaluated a single row at a time rather than in set-based fashion. This can create big problems if your CHECK constraint relies on a query and if more than one row is affected by any update. What happens is that the constraint gets checked once for each row before the statement completes. That means statement atomicity is broken and the function will be exposed to the database in an inconsistent state. The results are unpredicable and inaccurate. Here's an example. The goal is to define a constraint that guarantees the Accounts total will always balance to zero:
CREATE FUNCTION dbo.Accounts_total ()
RETURNS INT
AS
BEGIN
DECLARE @r INT;
SET @r =
(SELECT SUM(Amount) FROM dbo.Accounts);
RETURN @r;
END
GO
CREATE TABLE dbo.Accounts
(AccountNo INT PRIMARY KEY, Amount NUMERIC(10,2) NOT NULL);
INSERT INTO dbo.Accounts (AccountNo,Amount)
SELECT 1,-50 UNION ALL
SELECT 2,50 ;
ALTER TABLE dbo.Accounts ADD CONSTRAINT ck_Accounts
CHECK (dbo.Accounts_total()=0) /* balance must = 0 */;
UPDATE dbo.Accounts SET Amount = -Amount ;
INSERT INTO dbo.Accounts (AccountNo,Amount)
SELECT 3,-100 UNION ALL
SELECT 4,100 ;
SELECT * FROM Accounts;
Try this both with and without the constraint to get an idea of what's happening. You should see that the UPDATE and the second INSERT statement will cause a constraint violation error even though in logical terms the business rule is never violated (I tested it under SQL Server 2005, SP1). You should also find that this is unaffected by the transaction isolation level because the flaw occurs at row level rather than statement level.
To some extent it is possible to write queries that are safe in functions within CHECK constraints but the potential restrictions are fairly severe. In purely relational terms this is obviously a very serious flaw even if it's not actually a bug from Microsoft's point of view.