SQL Server Home
= NULL in check constraint
as Won't Fix
7/29/2009 6:20:51 AM
User(s) can reproduce this bug
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
SQL Server 2008 - Enterprise Edition
Windows Server 2008
Operating System Language
Steps to Reproduce
CREATE TABLE [dbo].[Table_1](
[field1] [nvarchar](50) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [CK_Table_1] CHECK (([field1]=NULL))
ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [CK_Table_1]
INSERT INTO Table_1 (field1) VALUES ('b')
allowed to insert what ever you want
error message saying either that the check constraint isn't valid, or an errormessage saying the input isn't valid
to post a comment.
Please enter a comment.
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
to post a workaround.
Please enter a workaround.
© 2014 Microsoft