Alternate table with new not null Column in existing table in SQL [duplicate]

How to add not null Column in existing table in SQL Server 2005?

Solution 1:

You will either have to specify a DEFAULT, or add the column with NULLs allowed, update all the values, and then change the column to NOT NULL.

ALTER TABLE <YourTable> 
ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>

Solution 2:

Choose either:

a) Create not null with some valid default value
b) Create null, fill it, alter to not null

Solution 3:

There are two ways to add the NOT NULL Columns to the table :

1. ALTER the table by adding the column with NULL constraint. Fill the column with some data. Ex: column can be updated with ''

2. ALTER the table by adding the column with NOT NULL constraint by giving DEFAULT values. ALTER table TableName ADD NewColumn DataType NOT NULL DEFAULT ''

Solution 4:

The easiest way to do this is :

ALTER TABLE db.TABLENAME ADD COLUMN [datatype] NOT NULL DEFAULT 'value'

Ex : Adding a column x (bit datatype) to a table ABC with default value 0

ALTER TABLE db.ABC ADD COLUMN x bit NOT NULL DEFAULT 0

PS : I am not a big fan of using the table designer for this. Its so much easier being conventional / old fashioned sometimes. :). Hope this helps answer