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

sql sql-server auto-increment
dcp3450 picture dcp3450 · Jul 21, 2011 · Viewed 313.9k times · Source

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

gbn picture gbn · Jul 21, 2011

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

Related questions

MSSQL Select statement with incremental integer column... not from a table

I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on. This column does not actually resides in …

Read More
SQL-How to Insert Row Without Auto incrementing a ID Column?

I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this …

Read More
SQL Server 2005 ROW_NUMBER() without ORDER BY

I am trying to insert from one table into another using DECLARE @IDOffset int; SELECT @IDOffset = MAX(ISNULL(ID,0)) FROM TargetTable INSERT INTO TargetTable(ID, FIELD) SELECT [Increment] + @IDOffset ,FeildValue FROM SourceTable WHERE [somecondition] TargetTable.ID is not an identity …

Read More