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:
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
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.
`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;
