Alter Table

• sistemas_casinomnes

  SSChasing Mays

  Points: 659

  More actions

  May 26, 2010 at 9:59 am

  #387023

  I have a table with a primary key.

  I need to create a query with a command alter table to add a column to that table and the column must be a primary key too.

  How must be the command alter table ?

  Thanks

  Vky.

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 26, 2010 at 10:14 am

  #1172869

  Let say you have table like:

  CREATE TABLE [dbo].[MyTestTable]

  (

  [RecId] [int] NOT NULL,

  [Val] [nchar](10) NULL,

  CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED

  (

  [RecId] ASC

  )

  )

  GO

  then, if you want to do what you asked you can:

  -- 1. drop existing PK

  ALTER TABLE dbo.MyTestTable DROP CONSTRAINT PK_MyTestTable

  -- 2. add new not null column

  ALTER TABLE dbo.MyTestTable ADD NewRecID int not null

  -- 2. create new PK

  ALTER TABLE dbo.MyTestTable ADD CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED

  (

  [NewRecId] ASC

  )

  The above will work if your table is empty. If not you will need to populate your new column before creating PK.

  You also have means to find what is the name of existing PK if any:

  select name from sys.indexes where [object_id] = object_id('MyTestTable') and is_primary_key = 1

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]