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

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

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

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

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

Related

Recent Posts