ivo tanev ivo tanev - 3 months ago 7
SQL Question

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)?


Answer

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