alter table to add "not null" column 1

[BG12424]

Is there a work around for using the ALTER TABLE command to add a column that is "NOT NULL"? for instance, can we add a NULL column, then change it to be NOT NULL in a two-step process? Thanks

regards,
Brian

http://www.newleaftechinc.com

 

[ptheriault]

You can use the alter table by rename method.
for example
if you have a table named mytable
you would script it out and create a table named mytable2 with the new field added to it.
The insert all the data from mytable into mytable2
Rename mytable to mytable_old
and rename mytable2 to mytable
Or
you could just create a default for the new field you are adding.
alter table mytable add column mycolumn int default 0

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLBill]

You can do it in ONE step. Here's an example:

ALTER TABLE table_name ADD column_name VARCHAR(20) NOT NULL DEFAULT 'UNKNOWN'

The issue is that you MUST supply a default value for the rows that already exist in the database without a value for that column.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

Sure you can alter a column to be not null later, no renaming or data inserting required.

ALTER TABLE Blah ADD NewCol varchar(20)
UPDATE Blah SET newcol = somethingcomplicated
ALTER TABLE Blah ALTER COLUMN NewCol varchar(20) NOT NULL