65

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

yogeswaran K
  • 2,278
  • 8
  • 29
  • 45

5 Answers5

115

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>
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
22

Choose either:

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

zerkms
  • 249,484
  • 69
  • 436
  • 539
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 ''

user3602795
  • 31
  • 1
2

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

Sterling Archer
  • 22,070
  • 18
  • 81
  • 118
Arvind Srikanth
  • 21
  • 2
  • Just a small note, I think the correct order should be: ALTER TABLE db.ABC ADD COLUMN x bit DEFAULT 0 NOT NULL instead of ALTER TABLE db.ABC ADD COLUMN x bit NOT NULL DEFAULT 0 – vnkid Jul 01 '20 at 08:17
0
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
Anil Soman
  • 2,443
  • 7
  • 40
  • 64
  • 1
    This will not work on already existing table. – zerkms Dec 17 '10 at 06:18
  • 2
    ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Val' cannot be added to non-empty table '#Test' because it does not satisfy these conditions. – Adriaan Stander Dec 17 '10 at 06:18