Changing a NULL column to NOT NULL

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

This is a wonderful question because it raises so many issues. First and foremost is an examination of what will happen if you try to restore the backup.

By changing the column to NOT NULL from NULL, you want to ensure that every row has a value. Presumably, the way the table existed up until this point, there must have been some rows with NULLs.

When you created the backup, the rows with NULLs were also backed up. Then you truncated the table, which removed all the existing rows, and changed the column to NOT NULL. Unfortunately, you cannot restore the backup now, because all the rows that had NULLs will be rejected.

This raises the interesting question of how to work around this issue. One obvious answer is to change those NULLs to something else before creating the backup:

UPDATE table
   SET column = 'shazam'
 WHERE column IS NULL

Notice that once you've done this, substituted a real value for every NULL in that column, you can safely create the backup, truncate the table, make the change from NULL to NOT NULL, and restore the backup.

In fact, as soon as you've updated all the NULLs to some real value, you could simply try changing the column from NULL to NOT NULL without taking the backup. The database system will of course check the entire table, but since there are no NULLs any more, the change should work with the data in place.

However, this raises the further issue of whether you can actually come up with a "magic value" to change all the NULLs to. For example, I have seen people change NULLs to the empty string (string of zero length), and even define columns with that as the default:

CREATE TABLE (
... column VARCHAR(37) NOT NULL DEFAULT ''

In my opinion, this is a design error. It's even worse than:

CREATE TABLE (
... 
, column VARCHAR(37) NOT NULL DEFAULT 'shazam'
... )

Using an empty string instead of NULL creates its own problems, which we won't go into here. Having a "magic value" that in effect says "this isn't a real value, but instead is a special value" is, again in my opinion, wrong.

However, if there is a "real" real value that you can change the NULLs to, then go ahead.

For an interesting discussion of the problems with NULLs, see Nulls - A Socratic Enquiry.