Adding a NOT NULL column to existing non-empty table

If you have a table with existing data ( as shown below)

and you’ve been asked to add a NOT NULL column to a table that already has data populated, it is not that simple as may seem at first glance.

It did not work because the table must have been empty. And this is logical – you are adding a NOT NULL column, but populating all rows with NULL in that case. Sure it won’t work.

The solution is to add a COLUMN with a DEFAULT keyword containing a default value. Then it will work, because it will update all existing rows with a new NOT NULL and DEFAULT value. See below

And now, we can see that all rows in this table have been populated with 0.

 

 

Leave a comment

Your email address will not be published. Required fields are marked *