Adding Primary key column to existing table

Question

Post reply

Adding Primary key column to existing table

phareesh

Old Hand

Points: 300
More actions
March 18, 2009 at 10:19 am

#97433

I have a table with 10000 records. The table does not have any primary key constraint.
Now i want to add a primary column.How can i add a column with primary key constraint to the existing table? Can somebody provide the query to alter the column

Viewing 6 posts - 1 through 5 (of 5 total)

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

jmuldoon Old Hand Points: 377 More actions · Answer 1

Hi.

It would go something like this.

Step 1

First, add your new column.

Alter table TableName add NewCol int default(0) not null

I think that you must have a not nullable column. If I remember correctly, MSSql Server doesn;t like making primary keys from nullable columns.

You can accomodate this in an alter table by adding a default constraint then declare it as not null.

Step 2. Load the values into the new column. since the table had 1000 rows in it, now those are all set to the value of the default constraint. Since you are ablout to make this column a primary key, there must be unique values for each row in the new column.

Step 3. Create the primary key constraint

alter table TableName add constraint New_primary_key primary key (NewCol)

Optionally, you could remove the default constraint on the new column.

Francisco Lopez SSCommitted Points: 1645 More actions · Answer 2

Using SQL Server 10.0.2531 Management Studio, I was able to skip the updating values part.

So,

1. Design table, Add

.[Id] column as Identity seed 1 increment 1, save.

2. Right click [Id] column, set as primary key, save. Done.

SQL server added the incrementing values automatically! Awesome product.

{Francisco}

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 3

Pls refer this link:

http://msdn.microsoft.com/en-us/library/ms190273.aspx

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 4

You may use code like this:

ALTER TABLE [TableName] WITH NOCHECK

ADD CONSTRAINT [PrimaryKeyName] PRIMARY KEY CLUSTERED ([ExistingColumn])

WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

jemish.dave SSC Enthusiast Points: 125 More actions · Answer 5

Go to Sql Server Mgmt Studio.

Navigate to the table.

Right click --> Edit/Modify Table

DO NOT SAVE AFTER DOING BELOW CHANGES

Add New Column.

[ select identity,primary key, foreign key, etc....]

Right Click newly created column --> select Generate Change Script

Done.

A new dialogue box will appear goving you the script for all the changes done above.

Copy the script and use as required.

Verify the script provided by Microsoft and do necessary changes.