SQL SERVER – Prevent Constraint to Allow NULL

With naked eyes, we often spot the evident problems but the specific details are missed many a time. Something similar happened recently. One of the blog readers sent me an email asking about a bug in how CHECK CONSTRAINT works. He suggested that check constraint accepts NULL even though the rule is specified.

After looking at the whole script, I found out what he has done and how to prevent this type of error.

Let us first reproduce the script where the constraint allows NULL value in the column.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT, Col1 INT, Col2 INT)
GO
-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1
CHECK (Col1 > 0)
GO
-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,1)
GO
-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,0,1)
GO
-- Insert will work fine with NULL
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,NULL,1)
GO

Inserting the zero will throw an error.

Inserting NULL will not throw error.

The reality is that any constraint will prevent values that are evaluated as FALSE. When NULL is evaluated, it is not evaluated as FALSE but as UNKNOWN. Owing to the same reason, the constraint is allowing NULL to be inserted. If you want NULL not to be inserted, the constraint has to be created in such a way that NULL will not be allowed.

The following script is created on Col2 where NULL is not allowed. When NULL is attempted to inserted, it will throw Error 547 as displayed in the earlier image.

-- Add the Constraint on Col2
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col2
CHECK (Col2 > 0 AND Col2 IS NOT NULL)
GO
-- Insert will throw an error
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,NULL)
GO
-- Insert will work fine
INSERT INTO TestDB.dbo.TestTable (ID, Col1, Col2)
VALUES(1,1,1)
GO
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

10 thoughts on “SQL SERVER – Prevent Constraint to Allow NULL

  1. Of course you could also declare, or alter, the column with NOT NULL so that you couldn’t store a NULL in the table even without the constraint.

    Like

  2. Here u r using two constraints i.e first one is CHECK Constraint and the Second one is NOTNULL Constraint. How to implement all these in one single CHECK Constraint

    Like

  3. the simple answer i would be saying to the error is null and 0 both are different for sql server. Null0.
    instead of writing a day blog on this.

    Hope coming article will be worth of reading.

    Like

  4. Pingback: SQL SERVER – How to ALTER CONSTRAINT Journey to SQLAuthority

  5. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  6. I rember you explaining in one of your sessions about NULL, you using your fingers to explain the concept of null, so in this case null would act as a value which can be greater than 0, but we do not know what the value is.

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s