MySQL Error Message : There can be only one auto column and it must be defined as a key

Another article regarding MySQL error message where the error message itself as specified in the title of this article is “There can be only one auto column and it must be defined as a key”. This error is specifically happened in the following situation :

There is a table where the table itself is going to be altered. Altering the table is done to add a primary key. This article has a strong relation with the one written in the article titled “MySQL Error Message Multiple primary key defined” in this link.

There is a table which is going to be added with a primary key by doing the following step :

1. Alter the table by adding a column which has an auto_increment attribute.

2. Modify the table by adding a constraint of primary key.

But unfortunately, the step failed in the first one. Below is the error message generated upon altering the table by adding a column which has an auto_increment attribute as shown below :

mysql> alter table user_role add id int not null auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>

So, the error given in the above output of the SQL query executed has already point out the hint of the error. It must be only one auto column and it must be defined as a key. But defining the the column as the primary key will generate the error as specified in the article titled “MySQL Error Message Multiple primary key defined” in this link .

So, in order to solve the problem of the error defined as “Incorrect table definition; there can be only one auto column and it must be defined as a key” can be solved by running the solution as defined in the article titled “MySQL Error Message Multiple primary key defined” in this link .

The error happened on altering the table named user_role which is described as follows :

mysql> desc user_role;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id_user | int(11) | NO   | PRI | NULL    |       |
| id_role | int(11) | NO   | PRI | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0,01 sec)
mysql>

So, run the solution on the article titled “MySQL Error Message Multiple primary key defined” in this link , by dropping the table and recreating the table with only one primary key with the additional column as foreign key referred to another tables.