Adding a not null column to an existing table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding a not null column to an existing table

Author

Topic

jihaes
Starting Member

5 Posts

Posted - 2007-10-22 : 15:02:04

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

38200 Posts

Posted - 2007-10-22 : 15:03:45

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/

jihaes
Starting Member

5 Posts

Posted - 2007-10-22 : 15:04:32

Is there a different way besides that by chance?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-22 : 15:06:47

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

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-22 : 15:11:38

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.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-22 : 15:14:47

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

jihaes
Starting Member

5 Posts

Posted - 2007-10-22 : 15:53:52

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!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-22 : 17:01:40

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.

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 02:59:51

Can't you add the new column with a default, and then drop the default?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 03:06:33

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"

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 04:27:00