Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding Primary key column to existing table Expand / Collapse
Author
Message
Posted Wednesday, March 18, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 18, 2009 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, September 23, 2011 2:40 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Friday, September 23, 2011 3:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 7:06 AM
Points: 1,216, Visits: 308
Pls refer this link:

http://msdn.microsoft.com/en-us/library/ms190273.aspx
Post #1180426
Posted Friday, September 23, 2011 3:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 7:06 AM
Points: 1,216, Visits: 308
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
Posted Thursday, April 12, 2012 5:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse