Add Primary Key to Existing Table

Question

Post reply

Add Primary Key to Existing Table

hkflight

SSCommitted

Points: 1949
More actions
May 24, 2011 at 11:45 am

#236785

I need to add a primary key to an existing table of 30000 rows. Is there no way to do this without using stored procedures? I tried using the ROW_COUNT function with no luck.
THanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

calvo SSChampion Points: 12930 More actions · Answer 1

ALTER TABLE [tableName]

ADD CONSTRAINT [constraintName]

PRIMARY KEY ([columnName])

make sure the field is not nullable and there are no duplicates

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

hkflight SSCommitted Points: 1949 More actions · Answer 2

Sorry, Ive added the field, but how do I populate it with autoincremented values?

Thanks!

calvo SSChampion Points: 12930 More actions · Answer 3

ALTER TABLE [tableName]

ADD [columnName] int identity(seed,increment)

This will populate the new field with an auto-incrementing number

Then you can add the primary key constraint on the new field

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

calvo SSChampion Points: 12930 More actions · Answer 4

And make sure you do adequate testing prior to releasing to production.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

hkflight SSCommitted Points: 1949 More actions · Answer 5

I tried this code but recieved the following:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'seed'.

calvo SSChampion Points: 12930 More actions · Answer 6

seed is the starting number.

increment is the amount you want to increment for each record insert.

ALTER TABLE [tableName]

ADD [columnName] int identity(1,1)

will yield

1,2,3,4,5...

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

hkflight SSCommitted Points: 1949 More actions · Answer 7

That worked perfectly. Sorry for my lack of experience and thank you again for your help and patience!

jarid.lawson SSCrazy Points: 2953 More actions · Answer 8

I have used this code since I first began, and after a few years I have not changed it yet. I recommend keeping it on hand...after a while it will be put to memory to the point you have to think hard to remember to change the standard field name:

Alter Table dbo.[TableName]

Add ID_Field Int

Identity(1,1)

Primary Key

And yes, I call the field ID_Field. I changed that one part recently because my new company did not get the Oid_Record field title (OriginalID_Record)...other than that I have not changed this script in years.

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]