Problem solve Get help with specific problems with your technologies, process and projects.

Changing a NULL column to NOT NULL

An Oracle user asks SQL expert Rudy Limeback how to repopulate a table after modifying the table to NOT NULL.

I have modified a column so that it would be not null. I created a backup of the table, truncated the original and modified the column. How do I repopulate the table?

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'

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:

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

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

, 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.

Dig Deeper on Oracle development languages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.