Data Wise Business Solutions

Data Wise Business Solutions, provides companies of any size Developers and Database professionals.

We specialize in Developers that program in PHP and Java.   For the data side, developers and Database Administrators for MySQL and Microsoft SQL Server.

• For hard to find jobs, we work with a retainer
• For most positions we charge a per hour amount

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