How do I add a auto_increment primary key in SQL Server database?

Question

I have a table set up that currently has no primary key. All I need to do is add a primary key, no null, auto_increment.

I'm working with a Microsoft SQL Server database. I understand that it can't be done in a single command but every command I try keeps returning syntax errors.

edit ---------------

I have created the primary key and even set it as not null. However, I can't set up the auto_increment.

I've tried:

ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment 

I'm using NVARCHAR because it wouldn't let me set NOT NULL under int

Answer 1

It can be done in a single command. You need to set the IDENTITY property for "auto number":

ALTER TABLE MyTable ADD mytableID int NOT NULL IDENTITY (1,1) PRIMARY KEY 

More precisely, to set a named table level constraint:

ALTER TABLE MyTable    ADD MytableID int NOT NULL IDENTITY (1,1),    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID) 

See ALTER TABLE and IDENTITY on MSDN

Answer 2

If the table already contains data and you want to change one of the columns to identity:

First create a new table that has the same columns and specify the primary key-kolumn:

create table TempTable (     Id int not null identity(1, 1) primary key     --, Other columns... ) 

Then copy all rows from the original table to the new table using a standard insert-statement.

Then drop the original table.

And finally rename TempTable to whatever you want using sp_rename:

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