Search code examples
sqlsql-serverauto-increment

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

Solution

  • 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