SQL Server add auto increment primary key to existing table

sql-server sql-server-2008 primary-key alter-table
fearofawhackplanet picture fearofawhackplanet · Feb 1, 2011 · Viewed 554.4k times · Source

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).

I'm assuming I can run a query to fill this column with incremental numbers, and then set as primary key and turn on auto increment. Is this the correct way to proceed? And if so, how do I fill the initial numbers?

Answer

marc_s picture marc_s · Feb 1, 2011

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY

and then you can make it the primary key:

ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)

or if you prefer to do all in one step:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED

Related questions

Add primary key to existing table

I have an existing table called Persion. In this table I have 5 columns: persionId Pname PMid Pdescription Pamt When I created this table, I set PersionId and Pname as the primary key. I now want to include one more column …

Read More
Composite Primary Key On Table Variable In Stored Procedure

I'm new to stored procedures and trying to add a composite primary key to a table variable. DECLARE @statistictemp TABLE ( MajorName VARCHAR(50) NOT NULL, SubName VARCHAR(50) NOT NULL, DetailedName VARCHAR(50) NOT NULL, UniversityID SMALLINT NOT NULL, StatisticValue DECIMAL(9,3) ); ALTER TABLE @…

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More