Mysql & Php Newbie

MySQL & php Newbie

Hi there. I'm new to the world of web programming (scripting, whatever you want to call it!)
This is going to be my log of all problems I encountered and how I went about resolving them.
Its more a reference for me, but hopefully it will also help other newbies out there as well...

MySQL Error - #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I was trying to add an extra field to an existing table using phpMyAdmin, as follows:
Table: my_table
Field: my_field
Type: INT(10)
Attributes: UNSIGNED
Null: NOT NULL
Extra: auto_increment
Pressing Save resulted in an error:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
Hmmm, I don't have any other numeric fields in the table and none of the existing fields are set to auto_increment (*#%*##$%*!!!!!)
Did a bit of digging around on oogle and found nothing that helped me to resolve the problem (I'm sure the answer exists out there, but patience is a virtue I lack!)
I tried dropping the table and recreating using sql (rather then using phpMyAdmin - lucky I didn't have any data in it). Still the same error!
Tried various methods to no avail. In the end tried the following and it worked a charm:
ALTER TABLE `my_table` ADD `my_field` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (`my_field`);
Turns out that if you want to add an auto_increment field on a table the field MUST be indexed (primary, unique or otherwise) in the same command as it is created.
On closer inspection, the phpMyAdmin GUI I was using to create the field was trying to run the above sql in two parts, ie...
ALTER TABLE `my_table` ADD `my_field` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Followed by...
ALTER TABLE `my_table` ADD INDEX (`my_field`);
Because the index was not getting created at the same time as the ADD `my_field` command MySQL hurled up an error (a fault with phpMyAdmin me thinks!)
"How comes it all works fine if you add the auto_increment field when you manually create a table", I hear you ask?! Well, if you look at the list of indexes (after table is created) it would appear that either phpMyAdmin (or MySQL, I don't know which) automatically tries to create a primary index on the auto_increment field and if there is another field defined as the primary key then MySQL chucks up an error, in which instance if you define a non-primary or unique index on the auto_increment field all works well!