Unable to execute Auto_Increment

Question

Post reply

Unable to execute Auto_Increment

carlton 84646

SSC Eights!

Points: 902

October 29, 2021 at 4:28 pm

#3946034

Hello Community.

I have the following code

CREATE TABLE "circuits" (
  "circuitId" int(11) NOT NULL AUTO_INCREMENT,
  "circuitRef" varchar(255) NOT NULL DEFAULT '',
  "name" varchar(255) NOT NULL DEFAULT '',
  "location" varchar(255) DEFAULT NULL,
  "country" varchar(255) DEFAULT NULL,
  "lat" float DEFAULT NULL,
  "lng" float DEFAULT NULL,
  "alt" int(11) DEFAULT NULL,
  "url" varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY ("circuitId"),
  UNIQUE KEY "url" ("url")
);

I'm getting the error:

Incorrect syntax near 'AUTO_INCREMENT'.

Can someone let me know why I'm getting the error?

Should I be using IDENTITY instead?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak SSC Eights! Points: 939 More actions · Answer 1

Yes. MS SQL Server uses identity, not auto_increment, which appears to be a mysql construct.

In MSSQL, I tend to prefer using sequence over identity (which now actually uses sequence under the covers).

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

A few other things need changed to match SQL Server syntax as well.

CREATE TABLE "circuits" (
  "circuitId" int NOT NULL IDENTITY(1, 1), --<<--
  "circuitRef" varchar(255) NOT NULL DEFAULT '',
  "name" varchar(255) NOT NULL DEFAULT '',
  "location" varchar(255) DEFAULT NULL,
  "country" varchar(255) DEFAULT NULL,
  "lat" float DEFAULT NULL,
  "lng" float DEFAULT NULL,
  "alt" int DEFAULT NULL, --<<--
  "url" varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY ("circuitId"),
  CONSTRAINT "url" UNIQUE ("url") --<<--
);

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

carlton 84646 SSC Eights! Points: 902 More actions · Answer 3

Thanks Scott, much appreciated. Saved me a lot of time and effort

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

Here are some further ideas and suggestions for you:

Rather than using DEFAULT NULL, just define the column as NULLable.
There's no need for all those double quotes.
It's generally considered good practice to name constraints. I've added a PK name in the code below.
FLOAT is often not the best choice for a numeric datatype. This is what Redgate's help has to say about FLOAT:

"The FLOAT (8 byte) and REAL (4 byte) data types are suitable only for specialist scientific use since they are approximate types with an enormous range (-1.79E+308 to -2.23E-308, and 2.23E-308 to 1.79E+308, in the case of FLOAT).
Any other use needs to be regarded as suspect, and a FLOAT or REAL used as a key or found in an index needs to be investigated.
The DECIMAL type is an exact data type and has an impressive range from -10^38+1 through 10^38-1. Although it requires more storage than the FLOAT or REAL types, it is generally a better choice."

CREATE TABLE circuits
(
    circuitId INT NOT NULL IDENTITY(1, 1)
   ,circuitRef VARCHAR(255) NOT NULL
        DEFAULT ''
   ,name VARCHAR(255) NOT NULL
        DEFAULT ''
   ,location VARCHAR(255) NULL
   ,country VARCHAR(255) NULL
   ,lat FLOAT NULL
   ,lng FLOAT NULL
   ,alt INT NULL
   ,url VARCHAR(255) NOT NULL
        DEFAULT ''
   ,CONSTRAINT PK_Circuits
        PRIMARY KEY (circuitId)
   ,CONSTRAINT url
        UNIQUE (url)
);

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.