Phpmyadmin #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Question

Hi, I'm importing mySql database (which was originally an access database) into phpmyadmin and its giving me this error:

SQL query:

CREATE TABLE  `Acc_inst` (

 `inst_ID` INTEGER NOT NULL AUTO_INCREMENT ,
 `inst_Name` VARCHAR( 255 ) ,
 `Inst_Ws` VARCHAR( 255 ) ,
 `inst_ph` VARCHAR( 255 ) ,
 `inst_Fx` VARCHAR( 255 ) ,
 `Inst_E` VARCHAR( 255 )
) ENGINE = INNODB DEFAULT CHARSET = utf8;

MySQL said: Documentation

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

-- There is in fact only on auto increment column and it is defined as a primary key so I dont get why its giving me this error

Solution 1

Define your auto increment column as a primary key.

CREATE TABLE  `Acc_inst` 
(    
   `inst_ID` INTEGER NOT NULL AUTO_INCREMENT ,
   `inst_Name` VARCHAR( 255 ) ,
   `Inst_Ws` VARCHAR( 255 ) ,
   `inst_ph` VARCHAR( 255 ) ,
   `inst_Fx` VARCHAR( 255 ) ,
   `Inst_E` VARCHAR( 255 ) ,
   PRIMARY KEY `inst_ID`(`inst_ID`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

Solution 2

A bit late, but i'm getting the same error in the latest version of phpMyAdmin (4.4.2). Nimara used manual SQL query, i used the special "add new table" form and still got this error.

So, for those of you that got here just like me, searching the #1075 error, you should know that this happens if you set your index / primary / autoincrement column in the form at first, then added some new columns and then wanted to submit the form. It seems that when you add more columns, phpMyAdmin does some kind of background refresh and loses the "primary" information. You still see it in your form, but in the background the SQL query it sends does not have this info any more.

So the solution would be to deselect your primary column and set it again. I'm pretty sure it's a bug, but it solves simple and fast this way.

Solution 3

CREATE TABLE  `Acc_inst` 
(    
   `inst_ID` INTEGER NOT NULL primary key AUTO_INCREMENT ,
   `inst_Name` VARCHAR( 255 ) ,
   `Inst_Ws` VARCHAR( 255 ) ,
   `inst_ph` VARCHAR( 255 ) ,
   `inst_Fx` VARCHAR( 255 ) ,
   `Inst_E` VARCHAR( 255 )
) ENGINE = INNODB DEFAULT CHARSET = utf8;

The error says you must define an auto increment column as key: Add primary key to this column definition.