MySQL 5.5 error report & quot;ERROR 1075 (42000): Incorrect table definition"

-- an error is reported when adding self-increasing fields to the table

Mysql > desc T7

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (20) | YES | | NULL |

| | id | int (4) | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > alter table T7 add id2 int (4) auto_increment

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Cause of error: the self-increment field is not defined as a key.

Solution: you can define the new field as unique key or primary key.

Mysql > alter table T7 add id2 int (4) auto_increment primary key

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table T7 drop column id2

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table T7 add id2 int (4) auto_increment unique key

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0