MySql: there can be only one auto column and it must be defined as a key

This error happened to me when I tried to add a column with AUTO_INCREMENT but that column was not a key of any sort.

I follow a convention; for my objects in my code, I use a string UUID and I store it in the database as the primary key. Now I also wanted to add a column named ‘db_id’, which would auto increment with every insert so I know the order of the record insertion at any point in time. I don’t use the auto incremented db_id as my primary key because it is easy to predict and allows potential loopholes. The db_id is just there for order in the table, it is never retrieved in the object model.

So to allow a non-primary key column to be auto-incremented, simply add it as an INDEX:

CREATE TABLE `ignition_chamber` (
  `id` varchar(50) NOT NULL,
  `db_id` INT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `component_desc` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX(`db_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Credit: there can be only one auto column and it must be defined as a key