Tuesday, 17 December 2013

SQL Server: Add a 'not null' column to an existing table

I tried to add a 'not null' column to an existing table, but SQL Server (rightfully) complained. I had two options - give the column a default value (no thanks) or add a nullable column and change it.

I chose the latter.

1. Add a nullable column
Alter table [Foo] add [MyFoo] int null
2. Set all the values to a value
Update [Foo] set [MyFoo] = 1
3. Set the column to be 'not null'
Alter table [Foo] Alter Column [MyFoo] int not null

No comments:

Post a comment