CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.

A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. This situation can produce unexpected results, as in the following example.

CREATE TABLE CheckTbl (col1 int, col2 int);

GO

CREATE FUNCTION CheckFnctn()

RETURNS int

AS

BEGIN

DECLARE @retval int

SELECT @retval = COUNT(*) FROM CheckTbl

RETURN @retval

END;

GO

ALTER TABLE CheckTbl

ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );

GO

The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.