Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KabirPatel
Yak Posting Veteran
54 Posts |
 Posted - 2007-07-05 : 12:26:20
|
| Hi,I want to add a non-nullable column to an EMPTY table in SQL erver 2000.To do this I tried the following:ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD [JobID] NVARCHAR (50) NOT NULL and it comes back with the message "ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified."This happens despite the table being empty and despite me adding NOCHECK. Any ideas why? Whats the best way to get around this?Cheers,Kabir |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-05 : 12:51:22
|
| YEs even if the table is empty you cannot add a non-nullabe column. If the table is empty, you can drop/recreate the table with the not null option. The same statement will work fine in 2005 however.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-07-05 : 13:27:23
|
Add the column with a default value on the column. If you don't want the default, then remove it after adding the column.create table MyTemp ( x int not null )goalter table MyTemp add y int not null constraint DF_MyTemp default 0goalter table MyTemp drop constraint DF_MyTemp CODO ERGO SUM |
|
|
|
|
|
|
Adding a non-nullable column