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 KishJeff Kish (jeff.kish@.mro.com) writes:
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
set @.dynamicsql = ' alter table institution_xref add institution_id
varchar(60) not null default ''' + @.default_institution_id + ''' '
...not null CONSTRAINT my_temp_default DEFAULT ''' ...
Quote:
Originally Posted by
EXEC (@.dynamicsql)
ALTER TABLE ... DROP CONSTRAINT my_temp_default
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:
Quote:
Originally Posted by
>Jeff Kish (jeff.kish@.mro.com) writes:
Quote:
Originally Posted by
>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.
>
Quote:
Originally Posted by
>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:
>
Quote:
Originally Posted by
> set @.dynamicsql = ' alter table institution_xref add institution_id
>varchar(60) not null default ''' + @.default_institution_id + ''' '
>
>...not null CONSTRAINT my_temp_default DEFAULT ''' ...
>
Quote:
Originally Posted by
> EXEC (@.dynamicsql)
>
ALTER TABLE ... DROP CONSTRAINT my_temp_default
thanks. much!
Jeff Kish
