How do I add a column to a table in SQL Server that doesn't allow nulls?

KingNestor_64878 0
Viewed 63310

I have a table that I want to add a bit column, which I wish to default to false for all existing data.

How do I alter my table in such a way that it allows me to specify NOT NULL before I have inserted false for my existing rows?

Should I create it as nullable, do an insert than switch it non-nullable?

sql-server sql
6 Answers

You could add the column and provide the default value to be used for all existing rows.

ALTER TABLE foo 
ADD bar bit 
DEFAULT 0 NOT NULL;
user_110690 
ALTER TABLE foo ADD bar bit DEFAULT 0 NOT NULL WITH VALUES;

The "with values" clause propigates the default value into existing rows.

user_1300740 
ALTER TABLE dbo.MyTable ADD MyColumn bit NOT NULL DEFAULT 0

For what it is worth, you can fire up Enterprise Manager, make the changes in the UI, and then have it generate a Change Script - and you can see how it would accomplish these kinds of tasks.

Bryan_Batchelder_297860

I have also done it as you say "create it as nullable, do an insert than switch it non-nullable". I've not had a problem doing it this way.

I've not yet needed to find a better way, however I'm intrigued if there is another way....

MrEdmundo_520260

I usually create the field as nullable with a default as false in your case update all the fields that were in the database prior then switch it to null

jmein_24860
Related