how to add not null column in sql server 2005

Question

how to add not null column in sql server 2005

anzz

SSCertifiable

Points: 5503
More actions
August 16, 2011 at 8:35 am

#241777

I am trying to add NOT NULL column to existing table.
when I try to add it throws an error.
Msg 4901, Level 16, State 1, Line 1
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 'test_Column' cannot be added to non-empty table 'test_1' because it does not satisfy these conditions.
thanks in advance,

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Danzz (8/16/2011)
I am trying to add NOT NULL column to existing table.
when I try to add it throws an error.
Msg 4901, Level 16, State 1, Line 1
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 'test_Column' cannot be added to non-empty table 'test_1' because it does not satisfy these conditions.
thanks in advance,

the error kind of explains it...but what you need to do is add a default value to the command you are using to add the column:

ALTER TABLE dbo.YOURTABLE

ADD MyNewColumn INT NOT NULL DEFAULT(42)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

anzz SSCertifiable Points: 5503 More actions · Answer 2

Thanks for the quick replay,

But there are 1 million rows in the table, adding DEFAULT value is a best pratice.

Is there any alternative solutions possiable?

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Yes ,you can create a new table with a reference to the main table via a key column.

Jayanth Kurup[/url]