Incorrect syntax near ‘auto_increment may arise due to the factor that the SQL does not support Auto increment. The solution to the error is easy and requires the user to log in through MySQL.

Bobcares answers all questions no matter the size, as part of our Server Management Services

Let us take a look at the auto-increment error and its solution in detail

Incorrect syntax near ‘auto_increment’ Error

The main reason to get an error on AUTO_INCREMENT as SQL server does not support Auto-increment. SQL Server, like any other database, does not support single quotes in column names. For example, consider the table given below:

CREATE TABLE Invoice(
Invoice_No INT NOT NULL AUTO_INCREMENT,
Order_ID INT NOT NULL,
TotalPrice VARCHAR(30) NOT NULL,
Quantity VARCHAR(30) NOT NULL,
PRIMARY KEY (Invoice No),
FOREIGN KEY (Order_ID) REFERENCES OrderInfo (Order_ID) );

This table will generate the Incorrect syntax near the 'AUTO_INCREMENT'. error

The solution to the error

Firstly make sure to use MySQL, because AUTO INCREMENT doesn’t work with other DBs like SQL Server (use Identity(1, 1) instead).  Secondly, when marking it as the PK, use Invoice No rather than Invoice No. Finally, Declare explicitly whether NAME is NULL or NOT NULL, removing the user’s reliance on the current connection settings.

So considering the above-mentioned solutions rewrite the table into the following:

CREATE TABLE sqlalchemy_generic_types ( sqlalchemy_generic_type_id INT IDENTITY(1, 1) PRIMARY KEY,
ObjectName VARCHAR(25) NOT NULL,
Description VARCHAR(100) NOT NULL
);

Take note of the modifications given to the table given above:

1. The id assigns an increasing value by IDENTITY(). The id will have a descriptive name.
2. Because the space has been removed from ObjectName, the name does not need to be escaped.
3. To define the table, no escape characters are required.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude it is easy to manage and solve the ‘incorrect syntax near auto_increment’ error. The error might occur due to the fact that SQL does not support Auto increment put in a single column.