Allowing only two null values for a column in SQL table

My friend Subhasis Swain asked me yesterday night, Vikram, is it possible to restrict table column to have only two null values.

Though I don’t find any practical carrying out for the same, but yes we can create our logic to hold only two null values in a column.

 I said Yes Subhasis, but alas I invited sleepless night agreeing to write code for the same on SQL Server 2014, which was his demand to download and install at night 2:00 AM

Along with it, there was one more condition, If I succeed then I will give cold coffee treat to him and Shankhodeep Karmakar, one of my very good friend at CCD that to 2/3 :-O

Let’s see how we can do this, here for the demo purpose I am creating a table with two column id and name.

create table TAB_TwoNullOnly

(id int,

name varchar(20))

go

Now I am trying to write a logic using trigger to restrict two null values for name field.

create trigger TRG_AllowTwoNullValue on TAB_TwoNullOnly

for insert,update

as

       declare @row_count int

       select @row_count = COUNT(*) from TAB_TwoNullOnly where name is null

       if @row_count > 2

       begin

              rollback

              print 'you cannot have more then two null values'

       end

       print 'After trigger fired.'

go

Here this trigger is written for insert and update DML statement so when a user fire insert or update table, the trigger will come into picture.

Inside the trigger we are checking how many null values are present in table including the latest insert and update, and if it is  greater then 2 then we do rollback our transaction which insert null in name field else allow the insert or update operation to perform.

Now since trigger is in place, let’s insert data in table.

insert into TAB_TwoNullOnly values(1,'Shibani')

insert into TAB_TwoNullOnly values(2,null)

insert into TAB_TwoNullOnly values(3,null)

Now we have inserted two null values in name field

select * from TAB_TwoNullOnly

id          name

----------- --------------------

1           Shibani

2           NULL

3           NULL

Now if we try to insert one more record with null value in Name column, it should throw error. Lets try out

insert into TAB_TwoNullOnly values(4,null)

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 6

The transaction ended in the trigger. The batch has been aborted.

Here we saw that it is not allowing me to enter null value because already there are two null value present in the Name column.

Lets now update the name field with null where Id =1, again it should not allow me to update since updating value will make the count of null value to 3, which is not comply with the problem statement.

update TAB_TwoNullOnly

set name = null

where id=1

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 8

The transaction ended in the trigger. The batch has been aborted.

So here saw with the help of trigger we can achieve such kind of constraint on table values.

Now today I m puting in order for 2/3 coffee at CCD with Subhasis and Sankhodeep. :D

This dawn my code was so loud on bed, it woke up my roommate. I said him to rollover and go back to sleep ;) ;)