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.
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.