Adding Primary key column to existing table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Adding Primary key column to existing table

Adding Primary key column to existing table

Rate Topic

Display Mode

Topic Options

Author

Message

phareesh

Posted Wednesday, March 18, 2009 10:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 24, 2011 6:36 AM
Points: 2, Visits: 18

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

Post #678686

jmuldoon

Posted Wednesday, March 18, 2009 11:14 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 14, 2009 2:47 PM
Points: 11, Visits: 50

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.

Post #678741

Francisco Lopez

Posted Friday, September 23, 2011 2:40 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 12, 2012 11:03 AM
Points: 455, Visits: 45

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

So,
1. Design table, Add [table].[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}

Post #1180419

CoolCodeShare

Posted Friday, September 23, 2011 3:07 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:44 PM
Points: 1,216, Visits: 297

Pls refer this link:

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

Post #1180426

CoolCodeShare

Posted Friday, September 23, 2011 3:13 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:44 PM
Points: 1,216, Visits: 297

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);

Post #1180429

jemish.dave

Posted Thursday, April 12, 2012 5:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 11:47 AM
Points: 9, Visits: 53

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.

Post #1282284

« Prev Topic | Next Topic »

Permissions