SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Adding a not null column to an existing table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jihaes
Starting Member

5 Posts

Posted - 10/22/2007 :  15:02:04  Show Profile  Reply with Quote
This works fine in 2005 but not in 2000...

I understand I cannot add a new column with a NOT NULL constraint in an existing table with entries but what if the table is empty and you go back to modify the table?

I tried this
ALTER TABLE <tablename USER> ADD <columnname test2> <type> NOT NULL
and throws

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'TEST2' cannot be added to table 'user' because it does not allow nulls and does not specify a DEFAULT definition.

I'm guessing this is a known issue? Any ideas for the fix?
Thanks

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 10/22/2007 :  15:03:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
If the table is empty, then just drop the table and recreate it using the format that you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jihaes
Starting Member

5 Posts

Posted - 10/22/2007 :  15:04:32  Show Profile  Reply with Quote
Is there a different way besides that by chance?
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

USA
385 Posts

Posted - 10/22/2007 :  15:06:47  Show Profile  Visit jhocutt's Homepage  Send jhocutt an AOL message  Reply with Quote
create table tmp (
c1 int not null
)
GO
-- THROWS ERROR
alter table tmp add c2 int not null
GO

-- WORKS
alter table tmp add c2 int not null default 0
GO

drop table tmp
GO


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 10/22/2007 :  15:11:38  Show Profile  Reply with Quote
Yep, adding a default is a work around that will make it work. He mentioned that he doesn't have a default specified so I'm assuming he doesn't want to use a default.
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

USA
385 Posts

Posted - 10/22/2007 :  15:14:47  Show Profile  Visit jhocutt's Homepage  Send jhocutt an AOL message  Reply with Quote
Then if he does not want to use a default and he does not want to drop the table then he does not have many options left.
This uses a default without allowing the default :)


create table tmp (
c1 int not null
)
GO
-- THROWS ERROR
alter table tmp add c2 int not null
GO

-- WORKS
alter table tmp add c2 int not null default NULL
GO

-- WORKS I GUESSIt forces a not null value
insert into tmp (c1) values (1)
GO
-- Server: Msg 515, Level 16, State 2, Line 1
-- Cannot insert the value NULL into column 'c2', table 'dbo.tmp'; column does not allow nulls. INSERT fails.
-- The statement has been terminated.

-- WORKS
insert into tmp (c1,c2) values (1,0)
GO

drop table tmp
GO


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Edited by - jhocutt on 10/22/2007 15:21:57
Go to Top of Page

jihaes
Starting Member

5 Posts

Posted - 10/22/2007 :  15:53:52  Show Profile  Reply with Quote
Thanks for your ideas. I guess I was just trying to see if there was a fix. We'll just have to put a default value. Thanks!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/22/2007 :  17:01:40  Show Profile  Reply with Quote
another possible option is to create the new table with a not null load that table from the "original" then drop the origianl table and use sp_rename to rename the "new" table to the same name as the "original" table.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/23/2007 :  02:59:51  Show Profile  Reply with Quote
Can't you add the new column with a default, and then drop the default?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/23/2007 :  03:06:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or

1) Add the column allowing null.
2) Update the column
3) Change the column to not null.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/23/2007 :  04:27:00  Show Profile  Reply with Quote
That's not a bad idea as the OP said the table was empty, so your step (2) is "Null"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000