|
|
Posted by Erland Sommarskog on 06/05/07 22:28
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
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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
Navigation:
[Reply to this message]
|