Auto increment sql ошибка - Не ошибается лишь тот, кто ничего не делает!

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:

The id assigns an increasing value by IDENTITY(). The id will have a descriptive name.
Because the space has been removed from ObjectName, the name does not need to be escaped.
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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

Источник

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

728k174 gold badges1326 silver badges1455 bronze badges

asked Feb 23, 2015 at 12:57

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.

answered Oct 1, 2015 at 8:32

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 ….

answered Feb 23, 2015 at 13:01

marc_smarc_s

728k174 gold badges1326 silver badges1455 bronze badges

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

answered Feb 23, 2015 at 14:28

EralperEralper

6,4112 gold badges20 silver badges27 bronze badges

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

answered Feb 12 at 18:45

Источник

Доброго дня,
Читаю учебник по MySQL и при выполнении примеров из него, у меня не получается создать новую таблицу с AUTO_INCREMENT’ом. Код:

CREATE TABLE album (
artist_id SMALLINT(5) NOT NULL,
album_id SMALLINT(4) NOT NULL AUTO_INCREMENT,
album_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (artist_id, album_id)
);

Ошибка:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Я понимаю суть того на что оно ругается, но не понимаю — почему? У меня же указан единственный столбец с AUTO_INCREMENT и он является частью индекса. В книге идентичный пример работает почему-то. Если сделать PRIMARY KEY только из album_id — таблица создается.

Вопрос задан

более трёх лет назад
3084 просмотра

В книге идентичный пример работает почему-то

Фокусы руками, найдите различие:

mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=myisam;
Query OK, 0 rows affected (0,00 sec)

mysql>

У вас с книгой разные дефолтные движки, в будущем от этого могут ещё сюрпризы возникать. Очень разные myisam и innodb по поведению, возможностям и ограничениям.

Пригласить эксперта

Показать ещё
Загружается…

03 июн. 2023, в 22:36

5000 руб./за проект

03 июн. 2023, в 19:30

500 руб./за проект

03 июн. 2023, в 19:30

750 руб./в час

Минуточку внимания

Источник

I get an error on AUTO_INCREMENT while creating the following table. Please help.

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) );

Karan

55.8k20 gold badges118 silver badges189 bronze badges

asked Apr 20, 2015 at 1:32

Make sure you’re using MySQL, since AUTO_INCREMENT doesn’t work for other DBs such as SQL Server (use Identity(1, 1) instead with it).
Use Invoice_No and not Invoice No when marking it as the PK.

answered Apr 20, 2015 at 2:09

KaranKaran

55.8k20 gold badges118 silver badges189 bronze badges

Источник

Here is my current table design:

I have several columns and no KEYs set, so when I try the following command:

ALTER TABLE users MODIFY COLUMN id INT auto_increment;

it gives me an error. I want the id column in the table to auto-increment and I want it to start with 1.

AdamMc331

16.4k10 gold badges71 silver badges133 bronze badges

asked Dec 7, 2014 at 21:15

I think you are missing the primary key definition. Try this:

ALTER TABLE users MODIFY id INT AUTO_INCREMENT PRIMARY KEY

Also, I recommend you check this out.

answered Dec 7, 2014 at 21:20

markusmarkus

1,6211 gold badge17 silver badges31 bronze badges

You must specify PRIMARY_KEY with AUTO_INCREMENT:

ALTER TABLE users MODIFY id INT PRIMARY_KEY AUTO_INCREMENT;

answered Dec 7, 2014 at 23:09

Kevin KopfKevin Kopf

13.2k14 gold badges49 silver badges65 bronze badges

Источник

Incorrect syntax near ‘auto_increment’ Error

The solution to the error

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Минуточку внимания

Не пропустите эти материалы по теме: