= NULL in check constraint - by Vidar Nordnes

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 478176 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 7/29/2009 6:20:51 AM
Access Restriction Public


If you have a check constraint with field1 = NULL, this will allow ANY input.
I know = NULL isn't valid. It should of course be IS NULL, but a developer where I work had written "Field1 IN (NULL, 'a')" in the check constraints expression window in management studio. When you click ok there, it changes it to "Field1 = NULL OR Field1 = 'a'"

The users were of course able to put in what ever they wanted in this field

I know IN is the same as =, so NULL shouldn't be written like this, but the bug here is that Field1 = NULL (which should return false), actually is handled as true if used in a check constraint
Sign in to post a comment.
Posted by Microsoft on 7/31/2009 at 1:28 PM
Thanks for your feedback. We could do check for field1 = NULL for example but I am not sure it is the right thing to do. What if it is something like field1 + dbo.fn() = NULL? Do we analyze dbo.fn() return value also? What if it is expression on the left hand side? Database engine should provide primitives and it is up to the developer to write the correct expresssions. We cannot presume the intent and perform validations in some specific scenarios.
Additionally, it is unclear to what extent we need to validate. For example, someone might code a check constraint with expression (1=0). Do you see that as valid or not? Maybe it is an interim expression added by the programmer or it is indeed what he / she wants. Similarly, you can write an expression like col IS NOT NULL even if the column is declared as NOT NULL. Do you make that invalid or not?
I have resolved this as "won't fix" since I don't see us adding specific checks like this to the product. If you leverage some of the unit testing features in tools like Visual Studio Database Edition, you can catch errors like this and make changes as necessary.

Umachandar, SQL Programmability Team