[mysql] ALTER table - adding AUTOINCREMENT in MySQL

I created a table in MySQL with on column itemID.

After creating the table, now I want to change this column to AUTOINCREMENT.

How can this be done using ALTER statements?

Table definition:

ALLITEMS (itemid int(10) unsigned, itemname varchar(50))

I am using the following code but it is throwing a syntax error

Error: syntax incorrect.

ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTOINCREMENT; 

ALTER TABLE allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;

ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;

The syntax:

   ALTER TABLE `table1` CHANGE `itemId` `itemId` INT( 11 ) NOT NULL AUTO_INCREMENT 

But the table needs a defined key (ex primary key on itemId).

Basic syntax for adding an AUTO_INCREMENT PRIMARY KEY to the OP's existing table:

ALTER TABLE allitems
MODIFY itemid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY;

Or for a new table, here's the syntax example from the docs:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

Traps and things to note:

• An AUTO_INCREMENT column must have an index on it. (Usually, you'll want it to be the PRIMARY KEY, but MySQL does not require this.)
• It's usually a good idea to make your AUTO_INCREMENT columns UNSIGNED. From the docs:

  Use the UNSIGNED attribute if possible to allow a greater range.

• When using a CHANGE or MODIFY clause to make a column AUTO_INCREMENT (or indeed whenever you use a CHANGE or MODIFY clause) you should be careful to include all modifiers for the column, like NOT NULL or UNSIGNED, that show up in the table definition when you call SHOW CREATE TABLE yourtable. These modifiers will be lost otherwise.

ALTER TABLE tblcatalog
    CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE `ALLITEMS`
    CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;