By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,796 Members | 1,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,796 IT Pros & Developers. It's quick & easy.

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty existing table!

P: n/a
Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.

Jan 8 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi,

You can use a workaround in this case... Put a DEFAULT constraint on
your column and it will work...

Enjoy,

Cédric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
aling a écrit :
Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.
Jan 8 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.