Posted by Jeff Kish on 06/06/07 14:20

On Tue, 5 Jun 2007 22:28:21 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

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

[Back to original message]