CHECK Constraint Error

Kep · April 11th, 2006, 01:41 AM

Hello everyone,

I noticed a strange (lack of) error with a check constraint I had on a table in my database and wondered if anybody else has seen this.

Create a simple table as:-

Code:

CREATE TABLE t_batch (
    dt_allocated DATETIME NULL,
    dt_created   DATETIME NOT NULL
)
ALTER TABLE t_batch ADD CONSTRAINT ck_bth_allocated CHECK(dt_allocated >= dt_created)
GO

Now, I would expect that if I INSERT a new row with a NULL value for dt_allocated that the check constraint would complain and stop the INSERT.

Try this:-

Code:

INSERT INTO t_batch
    (dt_allocated, dt_created)
    VALUES(NULL, GETDATE())

On my setup it works just fine, but if you run the SELECT below it returns no rows.

Code:

SELECT *
    FROM t_batch
    WHERE dt_allocated >= dt_created

Now I thought this was weird so I check to ensure the check constraint works at all.

Code:

INSERT INTO t_batch
    (dt_allocated, dt_created)
    VALUES(DATEADD(d, -1, GETDATE()), GETDATE())

This INSERT failed with a check constraint violation.

My @@OPTIONS variable returns 5496 (decimal), 1578 (hex).
My @@VERSION variable returns 'Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) '

Basically, I've installed service pack 4 with the AWE hotfix.

Has anybody seen this, knows of a patch?

Kep.

Jeff Mason · April 11th, 2006, 05:29 AM

No patch is necessary - welcome to 3 value logic. :D

A Check Constraint prevents a row from being inserted if the value of the constraint does not evaluate to FALSE. Note that because of 3 value logic, this is not the same as evaluating to TRUE.

Because of the NULL value of one of your operands, your constraint expression evaluates to UNKNOWN. Since this is not FALSE, the insert proceeds.

Modify your constraint to be something like:

CHECK(COALESCE(dt_allocated, '12/31/9999') >= dt_created)

to remove the NULL from the CHECK expression to reduce it to 2 value logic.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Kep · April 11th, 2006, 06:19 PM

Interesting. I see what you are saying. I would expect it to be "As long as it evaluates to TRUE" rather than "As long as it doesn't evalute to FALSE".

I looked in BOL and found this...

Quote:

quote:
A CHECK constraint specifies a Boolean (evaluates to TRUE or FALSE) search condition that is applied to all values entered for the column; all values that do not evaluate to TRUE are rejected.

Given the above quote I would suggest this is a bug.
Kep.

Kep.

Jeff Mason · April 11th, 2006, 06:51 PM

In contrast, the SQL Server 2005 BOL states:

Quote:

quote:
"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 SQL Server 2005 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."

Also, Inside SQL Server 2000 (my SQL Server bible) states:

Quote:

quote:
"CHECK constraints allow you to define an expression for a table that must not evaluate to FALSE for a data modification to succeed. [...] The constraint will allow the row if it evaluates to TRUE or to unknown. The constraint evaluates to unknown when NULL values are present ..."

With all due respect, the only bug here seems to be in the SQL Server BOL quote you gave. The quote indicates that the constraint expression is a boolean which evaluates to (only) TRUE or FALSE. In fact, boolean expressions can evaluate to three values: TRUE, FALSE, or unknown.

This may be why it looks like the SQL Server 2005 BOL clarifies the three value logic a bit more, and apparently corrects the misleading statement that there are only two values for a boolean.

NULLs trip up people all the time in similar situations - that's why it's best if you can avoid them in your database design.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Kep · April 11th, 2006, 07:32 PM

Fair enough. I did consider that BOL maybe wasn't defining clearly enough.

Can you supply the URL for the quote (maybe I need to update my BOL).

I got my quote from mk:@MSITStore:C:\Program%20Files\Mic...ar_da_0777.htm

Kep.

Jeff Mason · April 11th, 2006, 08:46 PM

The URL I have for the quote in SQL Server 2005 BOL is:

http://ms-help://MS.SQLCC.v9/MS.SQLS...4178cc09b8.htm

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com