2
  • Interface: HeidiSQL
  • Database: SQL Server

I'm trying to create table keeping ID (int) column as primary key as well as Auto_Increment.

But SQL Server is throwing Error 102:

Incorrect Syntax near 'Auto_increment'.

Please Help. Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anubhav Chatterjee
  • 21
  • 1
  • 1
  • 2

4 Answers4

7

The problem is that HeidiSQL will use the keyword AUTO_INCREMENT instead of IDENTITY. However AUTO_INCREMENT is not a valid specification for SQL Server CREATE TABLE statements. Here is how you do it:

CREATE TABLE "YourTableName"
(
   "ID" INT NOT NULL IDENTITY(1,1)
   PRIMARY KEY ("ID")
);

I didn't find a way to generate the CREATE TABLES with IDENTITY specification by using HeidiSQL. If someone does, please tell us.

Marcell
  • 809
  • 8
  • 23
  • 3
    Add all your columns, don't hit on Save button, instead, go to the tab "Create Code", copy all code and paste in a query tab, add IDENTITY(1,1) to the column ID, then execute the code – ainasiart Jul 20 '17 at 14:45
1

In SQL Server, it's called an IDENTITY:

CREATE TABLE dbo.YourTAble
(
   ID INT NOT NULL IDENTITY(1,1),
    .............
)

The official MSDN documentation on CREATE TABLE contains all the possible options - that should always be your first place to look for answers like this ....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

If you can use the SSMS (SQL Server Management Studio) as the GUI for database management tasks, you can change the ID column as an auto increment column by setting its Identity Specification attributes as follows

enter image description here

Eralper
  • 6,461
  • 2
  • 21
  • 27
0

the problem is you forgot to add 'key' keyword. the correct syntax is:

create table mytable (
   first_col int primary key AUTO_INCREMENT
);
select * from mytable
Blomiir
  • 1
  • 3