How to add not null Column in existing table in SQL Server 2005?
This question is related to
sql-server
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>
Choose either:
a) Create not null with some valid default value
b) Create null, fill it, alter to not null
IF NOT EXISTS (SELECT 1
FROM syscolumns sc
JOIN sysobjects so
ON sc.id = so.id
WHERE so.Name = 'Table1'
AND sc.Name = 'Col1')
BEGIN
ALTER TABLE Table1
ADD Col1 INT NOT NULL DEFAULT 0;
END
GO
There are two ways to add the NOT NULL Columns to the table :
ALTER the table by adding the column with NULL constraint. Fill the column with some data. Ex: column can be updated with ''
ALTER the table by adding the column with NOT NULL constraint by giving DEFAULT values. ALTER table TableName ADD NewColumn DataType NOT NULL DEFAULT ''
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
Source: Stackoverflow.com