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

http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table — ypercubeᵀᴹ, Jul 21 '11 at 14:38
Out of interest, where does the `auto_increment` syntax come from? Is it Access? — Kieren Johnstone, Jul 21 '11 at 14:39
@Kieren: `auto_increment` is used in MYSQL (and possibly elsewhere). — ypercubeᵀᴹ, Jul 21 '11 at 14:40
Possible duplicate of [Auto increment primary key in SQL Server Management Studio 2012](https://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012) — underscore_d, Dec 12 '17 at 14:30

score 118 · Accepted Answer · edited May 08 '18 at 21:42

118

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

edited May 08 '18 at 21:42

adam.wulf

2,149
20
27

answered Jul 21 '11 at 14:35

gbn

422,506
82
585
676

1

PERFECT! thanks for your help. I'll accept the answer once the timer allows me to. :D – dcp3450 Jul 21 '11 at 14:39
6

Is OK, but the correct syntax is without the second "ADD", it is: ALTER TABLE MyTable ADD MytableID int NOT NULL IDENTITY (1,1), CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyTableID) – Daniel Silva Aug 17 '16 at 18:38

score 21 · Answer 2 · edited Jul 02 '15 at 17:40

21

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

edited Jul 02 '15 at 17:40

Richard Barker

1,161
2
12
30

answered Jul 21 '11 at 14:38

Andreas Ågren

3,879
24
33

this create table syntax is **good practice** as advised by author of [this](http://stackoverflow.com/a/4862427/2218697) post in comments, hope helps someone. – Shaiju T Nov 01 '15 at 11:22

score 13 · Answer 3 · edited Jun 20 '20 at 09:12

You can also perform this action via SQL Server Management Studio.

Right click on your selected table -> Modify

Right click on the field you want to set as PK --> Set Primary Key

Under Column Properties set "Identity Specification" to Yes, then specify the starting value and increment value.

Then in the future if you want to be able to just script this kind of thing out you can right click on the table you just modified and select

"SCRIPT TABLE AS" --> CREATE TO

so that you can see for yourself the correct syntax to perform this action.

score 1 · Answer 4 · edited Sep 26 '17 at 05:58

1

If you have the column it's very easy.

Using the designer, you could set the column as an identity (1,1): right click on the table → design → in part left (right click) → properties → in identity columns, select #column.

Properties:

Identity column:

edited Sep 26 '17 at 05:58

TT.

15,774
6
47
88

answered Jul 31 '17 at 18:59

gustavo herrera

51
3

score 0 · Answer 5 · edited Sep 26 '17 at 05:44

0

In SQL Server 2008:

Right click on table
Go to design
Select numeric datatype
Add Name to the new column
Make Identity Specification to 'YES'

edited Sep 26 '17 at 05:44

answered Sep 26 '17 at 05:19

Chandan Deb

1
1

score -1 · Answer 6 · edited Apr 26 '23 at 04:50

-1

you can try this...

ALTER TABLE Your_Table
 ADD table_ID int NOT NULL PRIMARY KEY auto_increment;

edited Apr 26 '23 at 04:50

RF1991

2,037
4
8
17

answered Nov 13 '17 at 18:53

Wayne Salazar

11

1

Thanks for your answer. However, it would be helpful if you could provide a brief explanation of your code and how it solves the problem. This will make your answer more informative and easier to understand for future readers. – Houssem Apr 26 '23 at 10:07

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

6 Answers6

Linked