auto number problem

[mic2100]

hi,

i know this may well be an easy answer but i just can't seem to figure it out.

i have a table with over 7000 entries but there is no PK column the unique index is created by combining several fields. But i don't want to use it like this and have deceided to include a new field called failuresid that will become a autonumber and PK. When i try to create the field using phpmyadmin the mysql server returns the following error.


Incorrect table definition; there can be only one auto column and it must be defined as a key


Currently there are no other fields defined as PK, indexes, and unique keys and i am completly baffeled. also none of the fields are set as a autonumber.

Here is a copy of the table structure.


CREATE TABLE `failures` (
`HESNumFK` int(10) NOT NULL,
`InspectionDate` datetime NOT NULL,
`FailCodeFK` int(10) NOT NULL,
`Notes` longtext,
`Pass` tinyint(1) NOT NULL,
`HesJobGroup` int(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


any help on this would be greatly received thanks.

[chaosprime]

Probably phpmyadmin is issuing something like ALTER TABLE ADD `failuresid` INT UNSIGNED NOT NULL auto_increment, which will fail not because there's another auto column, but because it's not a key. To add an auto column, you have to issue a compound ALTER TABLE like ALTER TABLE ADD `failuresid` INT UNSIGNED NOT NULL auto_increment, ADD PRIMARY KEY (`failuresid`).

[mic2100]

thanks