NOT NULL clause in a SQL SERVER check constraint

Question

I was surpised to see that after creating this check constraint (in SQL server):

ALTER TABLE [dbo].[MYTABLE]
ADD CONSTRAINT MyConstraint
CHECK (MyColumn >0);

I was able to add an element with a NULL value:

insert into dbo.MYTABLE(MyColumn) values(NULL);

... which is actually what I wanted to prevent :)

I had to modify my constraint to :

ALTER TABLE [dbo].[MYTABLE]
ADD CONSTRAINT MyConstraint
CHECK (MyColumn IS NOT NULL);

This second version correctly prevents me to insert an element with MyColumn = NULL.

Yes in this very exemple, I could have set MyColumn to 'NOT NULL'. But my real problem includes other conditions where MyColumn could be null that had to be included into the constraint. I narrowed my problem to the code below which actually does not seem logical to me.

It looks SQL SERVER considers a NULL value as > 0?? However that is not the case when you run:

SELECT * from MyTable where MyColumn >0;

It propoerly excludes the NULL value. So why then would the check constraint behave differently???

Answer 1

No sql server considers a NULL to be NULL. If you want to prevent a NULL from being inserted then make the column NOT NULL. Any type of equality check against a NULL value will always return NULL. It can't be greater than 0, less than 0 or equals 0 because the value is unknown.