ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql