Q

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

This was first published in June 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close