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
