0

For some reason i keep getting the following error when i try to set my uid as auto_increment. I am using phpMyAdmin.

Error

SQL query:

ALTER TABLE `ITEMS` ADD `uid` INT( 2 ) NOT NULL AUTO_INCREMENT FIRST ,
ADD `name` VARCHAR( 100 ) NOT NULL AFTER `uid` ,
ADD `status` VARCHAR( 100 ) NOT NULL AFTER `name`

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

I had a few other tables in my database that originally had index as auto_increment but I changed all those columns to uid varchar. But now I am adding a table named ITEMS and I want uid to be the "master" key that I can use as a reference point to all my other tables.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Denoteone
  • 4,043
  • 21
  • 96
  • 150

2 Answers2

1

You need to add the PRIMARY KEY constraint to your new column. BTW, this may mean removing that constraint from any old column that was originally AUTO_INCREMENT.

kvista
  • 5,039
  • 1
  • 23
  • 25
  • This was my issue. I deleted the primary key which was set in 3 other tables?! and that worked. Thanks! – Denoteone Jan 13 '11 at 01:32
1

You're not adding a table, but changing it. New specification comes in conflict with what exists in the schema, you can do show table and post it here for us to see.

Dennis Kreminsky
  • 2,117
  • 15
  • 23
  • That error was because I had gotten one field in and was going back to add the uid. thanks for feedback. – Denoteone Jan 13 '11 at 01:33