Unable to execute Auto_Increment

Question

Post reply

Unable to execute Auto_Increment

carlton 84646

Ten Centuries

Points: 1026

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 Ten Centuries Points: 1358 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: 101092 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246959 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)
);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin