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

Question

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?

Answer 1

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;

• marc_s: I typically like to NAME my constraint - even default constraints - in case I need to drop them later on. The default "DF__TimeZones__IsUsa__3D5E1FD2" names are a bit hard to remember.....

Answer 2

As an alternative to the answers listed there is another syntax available which may suit some people better, for example if you use tools like 'ReadyRoll'.

ALTER TABLE [dbo].[FOO] ADD BAR bit NOT NULL CONSTRAINT [df_Bar] DEFAULT 0

Check out this SO answer to see why naming your constraints (the df_Bar above) is nice.

Answer 3

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

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

• KingNestor: @marc_s, I just ran it without the "WITH VALUES" for my bit column and it worked. Are you sure?
• Cylon Cat: @KingNestor, try it with a default value of 1, or something that's not all binary zeros.
• Bryan Batchelder: WITH VALUES is used in the case of a NULLABLE column and you want the default value to be used instead of NULL.
• marc_s: @KingNestor: you're absolutely right - if the column is defined as NOT NULL, it will be filled with the default value even without the "WITH VALUES" clause. Thanks for pointing that out! But the WITH VALUES is needed if the column is defined as NULLable

Answer 4

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.

Answer 5

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....

Answer 6

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