Alter table, Add column in PK

• Flavio

  Right there with Babe

  Points: 793

  More actions

  May 24, 2005 at 3:16 am

  #66970

  Hello everybody..

  I have a table.

  Its primary key made by two fields (composite PK with 2 field).

  I want to add a new column, and I want that this new column become the third fields of PK..

   It's possible with ALTER TABLE statement ?

  Thanks!

  Flavio

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  May 24, 2005 at 3:23 am

  #560703

  Something along these lines

  use tempdb

  create table comp

  (

   c1 int not null

   , c2 int not null

   constraint pkcomp primary key(c1,c2)

  )

  insert into comp values(1,1)

  alter table comp

   drop constraint pkcomp

  go

  alter table comp

   add c3 int null

  go

  update comp set c3=1

  alter table comp

   alter column c3 int not null

  go

  alter table comp

   add constraint pkcomp primary key(c1,c2,c3)

  select * from comp

  exec sp_pkeys 'comp'

  drop table comp

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]