MySQL - there can be only one auto column and it must be defined as a key

This error usually occurs when you create a new mysql table, but forget to specify that a column should be the primary key. Here's an example:

Here you can see the user trying to create a table called 'history':

mysql> create table history(hi_id int auto_increment, hi_message text, hi_datetime datetime, hi_user text);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

The user has specified the hi_id table to be auto_increment'ed, assuming that mysql will automatically understand that column to be the primary key. This is wrong. You must specify keys at the table creation stage. Here is the corrected statement:

mysql> create table history(hi_id int auto_increment primary key, hi_message text, hi_datetime datetime, hi_user text);
Query OK, 0 rows affected (0.00 sec)

And it works..

christo