Alter Table

Question

Post reply

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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

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]