ALTER TABLE to add NOT NULL fields

5 Nov 2005 4:23 AM

scuba79

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...

5 Nov 2005 8:30 AM

Tony Rogerson

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

Show quote

"scuba79" <scub***@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...

5 Nov 2005 4:35 PM

Dan Guzman

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

Show quote

"scuba79" <scub***@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...