Data Wise Business Solutions

I  have discussed with other articles that a good database design shouldn’t use nulls.   See Real World Use of nulls.  Nulls can cause issues in application development.

I was helping out a friend with a query and noticed that he had written a check (in WHERE) for a column with two conditions.   One of them being null.   The column was a status column (I.e true/false).    First off, All status columns should have a default value of zero (false).   

To help him with his query and worry about fixing the database later I helped him to write his query better.

Before:
    Select...
    From...
    Where status <> 1 OR status is null. 

On the top of my list of best practices is never to use OR.   He didn’t think he had another option until I showed him this version:

After:
    Select...
    From...
    Where coalesce(status,0) <> 1

Notice NO OR.   Coalesce will return the first non null.   In above example, if status is null, it will then return 0.  If status was 0 or 1, it would return that value.

Should also note that coalesce will work on all database engines. 

Now lets fix the database issue.   How to add a default to an existing column?  
First lets look at a sample table.  Col1 and Col2 allow nulls:

CREATE TABLE Table1 (
   Table1ID INT AUTO_INCREMENT NOT NULL,
   Col1 INT,
   Col2 VARCHAR(50),
  PRIMARY KEY (Table1ID)
);

Put some data into that table (mysql code):
insert into Table1 (Col1, Col2)
VALUES (1,NULL), (NULL,'A'), (null,null);

we get:

select * from Table1;

Table1ID    Col1    Col2
1            1      NULL
2            NULL   A
3            NULL   NULL

Now lets update col1.  Set a default and make it not nullable.

-mysql
ALTER TABLE Table1
 CHANGE Col1 Col1 INT(11) NOT NULL DEFAULT '0';

--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';
 
--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';

--mssql 2008
ALTER TABLE Table1
add constraint dfTable1 default '0' for Col1

Looking at the existing data you will see that Col1 values that were once null are now 0
select * from Table1;
Table1ID  Col1  Col2
1         1     NULL
2         0     A
3         0     NULL