How SQL Server evaluate Null Value in Check Condition

Understanding NULL value with check constraint

           

As I said earlier when the condition of check constraint evaluates to false, it will not allow inserting /updating record  in the table.

In the given below example I am creating a check constraint which is allowing only three word in the country field.

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’))

Now I am trying to insert these three record using insert statement

           

            insert into MyCountry(country_name) values(‘india’)

            insert into MyCountry(country_name) values(NULL)

            insert into MyCountry(country_name) values(‘uss’)

Now what will happen, when I will try to execute these three insert statement:

a)      Only first insert statement insert record.

b)      First and second insert record.

c)      First and third insert record successfully.

The correct solution is (b).

The reason the second INSERT statement worked is that the check constraint did not equate to FALSE when the second INSERT statement was run. Because of this, the database engine inserted the record. So why did this happen? This happened because when NULL is used in a comparison operation it equates to UNKNOWN. Since UNKNOWN is not FALSE there is no violation of the check constraint. Therefore, you need to be careful when you write your check constraints where you want to reject values that contain NULLS. Another way to code the above constraint is

           

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’) and country_name is not NULL)

Post Reference: Vikram Aristocratic Elfin Share