best way to add column not null

[Jeff Kish]

Hi.

I've read up on this, and have something that works, but I was wondering if
there is anything I'm overlooking with this.

Situation is:

I have a bunch of tables.. I need to modify table2 as part of an upgrade of a
database schema.

I am using T-SQL scripts to do the trick which I'm writing myself.

I need to add a new varchar(8) column that is not null to the primary key.
I have a default I would like to use for the initial ddl modification.
I want to get rid of the default after the modification is complete, but leave
the column not null for future operations.
..

(Some if the code I'm using I took from one of Erlands posts.. hope I'm not
abusing it).
Here is the code I'm using now.. it basically adds the column 'institution_id '
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.

I can only feel I'm supposed to be maybe using a constraint column with a name
to do this easier/more properly.

set @dynamicsql = ' alter table institution_xre f add institution_id
varchar(60) not null default ''' + @default_instit ution_id + ''' '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xre f alter column
institution_id varchar(60) not null '
EXEC (@dynamicsql)
select @institution_id default = object_name(cde fault) from syscolumns
where id = object_id('inst itution_xref') and name = 'institution_id '
exec(' alter table institution_xre f drop constraint ' +
@institution_id default)
set @dynamicsql = ' alter table institution_xre f drop constraint
institution_xre f_pk '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xre f with nocheck add
constraint institution_xre f_pk primary key clustered (originalcode,
institution_id) '
EXEC (@dynamicsql)

thanks
Jeff
Jeff Kish

[Erland Sommarskog]

Re: best way to add column not null

Jeff Kish (jeff.kish@mro. com) writes:

I need to add a new varchar(8) column that is not null to the primary key.

Ouch! That can be paintful, at least if there are foreign keys
referencing the table.

abusing it).
Here is the code I'm using now.. it basically adds the column
'institution_id '
as not null along with a default.
Then I jump through a couple of hoops trying to get rid of the default.
Finally I setup the primary key again.
>
I can only feel I'm supposed to be maybe using a constraint column with
a name to do this easier/more properly.

Yes, if you name the constraint it's a little easier:

set @dynamicsql = ' alter table institution_xre f add institution_id
varchar(60) not null default ''' + @default_instit ution_id + ''' '

....not null CONSTRAINT my_temp_default DEFAULT ''' ...

EXEC (@dynamicsql)

ALTER TABLE ... DROP CONSTRAINT my_temp_default




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jeff Kish]

Re: best way to add column not null

On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>Jeff Kish (jeff.kish@mro. com) writes:

>I need to add a new varchar(8) column that is not null to the primary key.

>
>Ouch! That can be paintful, at least if there are foreign keys
>referencing the table.
>

>abusing it).
>Here is the code I'm using now.. it basically adds the column
>'institution_i d'
>as not null along with a default.
>Then I jump through a couple of hoops trying to get rid of the default.
>Finally I setup the primary key again.
>>
>I can only feel I'm supposed to be maybe using a constraint column with
>a name to do this easier/more properly.

>
>Yes, if you name the constraint it's a little easier:
>

> set @dynamicsql = ' alter table institution_xre f add institution_id
>varchar(60) not null default ''' + @default_instit ution_id + ''' '

>
>...not null CONSTRAINT my_temp_default DEFAULT ''' ...
>

> EXEC (@dynamicsql)

>
ALTER TABLE ... DROP CONSTRAINT my_temp_default

thanks. much!
Jeff Kish