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.

SQL SERVER - Prevent Constraint to Allow NULL  constraint1

Inserting NULL will not throw error.

SQL SERVER - Prevent Constraint to Allow NULL  constraint2

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 (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – Last 5 Days to WIN Windows Phone 7 – 140 Words to Win
Next Post
SQL SERVER – Potential Bottlenecks for Performance

Related Posts

7 Comments. Leave new

  • Imran Mohammed
    April 21, 2011 8:58 am

    How about making Col2 a NOT NULL column and then adding the constraintto that column.

    ~ IM.

    Reply
  • Michael Codanti
    April 21, 2011 9:10 am

    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.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Is it better to declare the col as not null or use check constraint? Performance is better in which scenario?

    Reply
  • 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.

    Reply

Leave a Reply

Menu