By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,454 Members | 1,795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,454 IT Pros & Developers. It's quick & easy.

best way to add column not null

P: n/a
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_xref add institution_id
varchar(60) not null default ''' + @default_institution_id + ''' '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xref alter column
institution_id varchar(60) not null '
EXEC (@dynamicsql)
select @institution_iddefault = object_name(cdefault) from syscolumns
where id = object_id('institution_xref') and name = 'institution_id'
exec(' alter table institution_xref drop constraint ' +
@institution_iddefault)
set @dynamicsql = ' alter table institution_xref drop constraint
institution_xref_pk '
EXEC (@dynamicsql)
set @dynamicsql = ' alter table institution_xref with nocheck add
constraint institution_xref_pk primary key clustered (originalcode,
institution_id) '
EXEC (@dynamicsql)

thanks
Jeff
Jeff Kish
Jun 5 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Jeff Kish (je*******@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_xref add institution_id
varchar(60) not null default ''' + @default_institution_id + ''' '
....not null CONSTRAINT my_temp_default DEFAULT ''' ...
EXEC (@dynamicsql)
ALTER TABLE ... DROP CONSTRAINT my_temp_default


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 5 '07 #2

P: n/a
On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.sewrote:
>Jeff Kish (je*******@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_xref add institution_id
varchar(60) not null default ''' + @default_institution_id + ''' '

...not null CONSTRAINT my_temp_default DEFAULT ''' ...
> EXEC (@dynamicsql)

ALTER TABLE ... DROP CONSTRAINT my_temp_default
thanks. much!
Jeff Kish
Jun 6 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.