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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: