MySQL: having trouble with rename of auto_incriment column

Question

On some versions of mysql (e.g. 5.0.77, for Win32 (ia32)) I'm having trouble with the following query

ALTER TABLE `table_name` CHANGE COLUMN `old` `new` integer auto_increment

I'm getting Incorrect table definition; there can be only one auto column and it must be defined as a key

How that can be ommited?

Answer 1

If the column is already an auto-increment primary key, just change its name and supply the data type. (MySQL will error if the data type isn't supplied here). The existing auto-increment and key definition will be retained.

ALTER TABLE `table_name` CHANGE COLUMN `old` `new` INT

If you are changing its name and making an auto-increment where there previously wasn't one, you need to specify the index or make it a primary key in the same statement:

ALTER TABLE `table_name` CHANGE COLUMN `old` `new` INT PRIMARY KEY AUTO_INCREMENT

Note however, that if you have another column on this table already serving as AUTO_INCREMENT, then the error as reported is correct and you will not be able to specify a second AUTO_INCREMENT column. You have to choose one or the other.

Answer 2

You need to first disable auto-increment, then make the changes, then turn it back auto_increment. Try this (this will change it to INT):

ALTER TABLE `table_name` MODIFY COLUMN `old` INT;
ALTER TABLE `table_name` CHANGE COLUMN `old` `new` integer
ALTER TABLE `table_name` MODIFY COLUMN `new` INT AUTO_INCREMENT;

Answer 3

try this one:

  ALTER TABLE `t1` CHANGE COLUMN `uid` `id` INT  AUTO_INCREMENT;

Another one interesting answer:

ALTER TABLE table_name MODIFY COLUMN 'old_id','new_id' INT auto_increment;

click