Autoincrement Id and key problem (MySQL)

i have table and i tried to add

`id` int(11) NOT NULL auto_increment,

to table 开发者_如何学编程but i get

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

So the questions are:

1. How I can find which one is the key column
2. Can I change it without problems and add the new ID field (with autoincrement)?

If the table is already created use ALTER:

ALTER TABLE `table` ADD COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT, 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (`id`)

If the table is being created you must set the auto_increment as primary key:

CREATE TABLE IF NOT EXISTS `database`.`table` (
`id`INT(11) NOT NULL AUTO_INCREMENT ,
`a` VARCHAR(45) NULL ,
`b` VARCHAR(255) NULL ,
`c` VARCHAR(45) NULL ,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci

`id` int(11) NOT NULL auto_increment primary key,

It must be made the primary key

ALTER TABLE `database`.`table_name` ADD COLUMN `new_column_name` INT NOT NULL AUTO_INCREMENT  AFTER `last_column_name_in_the_table` , CHANGE COLUMN `old_column_name` `old_column_name` INT(11) NOT NULL  

, DROP PRIMARY KEY 

, ADD PRIMARY KEY (`new_column_name`) ;

You can find the current primary key just by running show create table as matthewh suggested, and look for something like this in the output:

PRIMARY KEY (old_column_name),

Also you should check out MySQL Workbench if you can use it. It makes making changes like this really easy.

Doesnt anyone use unsigned integers for primary keys. And why bother using an optional display width i.e. int(11) when you're not even using zerofill !!

drop table if exists foo;
create table foo
(
 id int unsigned not null auto_increment primary key,
 ...
)
engine=innodb;

First backup the database. Then drop any foreign key associated with the table. truncate the foreign key table.Truncate the current table.Add the id with auto increment . Use sqlyog or workbench or heidisql or dbeaver or phpmyadmin. Then insert the table from backup using batch update.