CODEWITHSUNDEEP
mysqlsqlmysql-workbench
mapgiordani
mapgiordani

Reputation: 65

How can I create an AUTO_INCREMENT column in a table that already exists and has data?

I want to create a column that will auto increment when a new row is inserted. The table has already data and this data does not need to receive this index, or it can be NULL. I just want to start to increment since now.

It looks simple, but I run in Workbench this:

ALTER TABLE `serra`.`acionamento` 
ADD COLUMN `indice` INT NULL AUTO_INCREMENT AFTER `date_insercao`

... and it says

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

This column really needs to be a primary key?

Upvotes: 0

Views: 843

Answers (1)

mapgiordani
mapgiordani

Reputation: 65

I found the solution I was looking for...

I was missing an UNIQUE configuration...

ALTER TABLE `serra`.`acionamento` 
ADD COLUMN `indice` INT NOT NULL AUTO_INCREMENT AFTER `column`,
ADD UNIQUE INDEX `indice_UNIQUE` (`indice` ASC);

Thanks for the comments

Upvotes: 1

Related Questions