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