How to add NOT NULL columns?

Question

I am starting to use database-projects and struggling with simple tasks. If I would like to add a new column which is NOT NULL, how can I make it work if there is already data in the table?

I used to write diff-scripts, in such a case I would have written something like:

-- ... ADD COLUMN X ... 
-- insert data into x
-- ... ALTER COLUMN X NOT NULL

How would I need to handle such cases with a database-project?

Thx for any tipps sl3dg3

Answer 1

I found finally two possible ways. In a nutshell:

• Use a default-constraint (as suggested by Rain)
• Fill the data in the post-deployment script, drop the default-constraint
• After deployment, remove the default-constraint from the source

Or

• Backup the table's data in the pre-deployment script into a temporary table, delete data from the target table
• In the post-deployment script, move the data back into the target table, including the new data for the new NOT NULL - column, drop the table

Found here: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/49bf2a88-d80d-4a9f-970e-728bd530332e/

Or here: http://blogs.msdn.com/b/bahill/archive/2009/03/30/managing-data-motion-during-your-deployments-part-1.aspx

Both means extra work, I was hoping for more support from the framework for such common cases. But at least it can be done like that.

Answer 2

You could also, instead of specifying a DEFAULT value for the column, from the Publish DB Dialog; Go into Advanced->Advanced Deployment Options and check the "Generate Smart Defaults" box.

From the description in VS: Generate Smart Defaults: Automatically provides a default value when updating a table that contains data with a column that does not allow null values.