SQL create statement incorrect syntax near auto increment

Question

I created the following table, but I got the error below;

Incorrect syntax near 'AUTO_INCREMENT'.

SQL:

CREATE TABLE [dbo].[MY_TABLE] (
    [ID] INT NOT NULL AUTO_INCREMENT,
    [NAME]          NVARCHAR (100) NULL,
    [SCHOOL]             NVARCHAR (100) NULL,
    PRIMARY KEY (ID)
);

I think I have done everything right. Can someone help me out?

score 48 · Accepted Answer · answered Feb 24 '13 at 06:56

48

It is IDENTITY not AUTO_INCREMENT in SQL Server.

Try this instead:

CREATE TABLE [dbo].[MY_TABLE] (
    [ID] INT NOT NULL IDENTITY(1, 1),
    [NAME]          NVARCHAR (100) NULL,
    [SCHOOL]             NVARCHAR (100) NULL,
    PRIMARY KEY (ID)
);

answered Feb 24 '13 at 06:56

Mahmoud Gamal

78,257
17
139
164

Yes that worked. According to W3c http://www.w3schools.com/sql/sql_autoincrement.asp it was auto_increment – Sharon Watinsan Feb 24 '13 at 06:58
4

May i know what (1,1) means ? – Sharon Watinsan Feb 24 '13 at 06:59
5

@sharonHwk - The first one is the start number, the second is the seeding, so `(1, 1)` means: 1, 2, 3, 4, ... – Mahmoud Gamal Feb 24 '13 at 07:02
@sharonHwk - On the link you provided you need to keep reading down to the part where it says "Syntax for SQL Server" – JohnFx Feb 24 '13 at 07:03

score 2 · Answer 2 · answered Feb 24 '13 at 06:58

2

Its not AUTO_INCREMENT. Here the Demo from sqlfiddle

answered Feb 24 '13 at 06:58

spajce

7,044
5
29
44

score 2 · Answer 3 · edited Feb 09 '23 at 05:20

Use IDENTITY for MSSQL as shown below. *AUTO_INCREMENT is for MySQL and MariaDB:

CREATE TABLE [dbo].[MY_TABLE] (
    [ID] INT NOT NULL IDENTITY, -- Here
    ...
);

In addition, you can have a custom IDENTITY with "()" as shown below. The 1st argument is for start value and the 2nd argument is for increment value so in the example below, if first inserting a row, ID is 20 and if second inserting a row, ID is 23, then ID is 26, 29, 32...:

                               -- Increment value
CREATE TABLE [dbo].[MY_TABLE] (--  ↓
    [ID] INT NOT NULL IDENTITY(20, 3),
    ...                     --  ↑
);                          -- Start value

And, IDENTITY is equivalent to IDENTITY(1, 1).

SQL create statement incorrect syntax near auto increment

3 Answers3

Linked

Related