Incorrect table definition

Forum Moderators: open

Message Too Old, No Replies

Incorrect table definition

surrealillusions

2:30 pm on Jan 28, 2008 (gmt 0)

Tried changing one field in a database to auto increment in the extra's drop down choice for that field. However, it spat an error out at me.

SQL query:

ALTER TABLE `tablename` CHANGE `id` `id` INT( 6 ) NOT NULL AUTO_INCREMENT

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

Despite searches on the net and the phpmyadmin docs i cant find a solution that suits my needs...

What do i need to do?

LifeinAsia

4:35 pm on Jan 28, 2008 (gmt 0)

The error message pretty much says it all. The solution is to remove AUTO_INCREMENT on the filed that currently has it, then make sure the field you want to make AUTO-INCREMENT is a key, then add AUTO_INCREMENT to that field.

However, BEFORE you do all this, you'd better make sure that your existing code is not going to die when you remove the first AUTO_INCREMENT. Also, that it doesn't die when you add AUTO_INCREMENT to the other field.

adwatson

4:36 pm on Jan 28, 2008 (gmt 0)

perhaps it's complaining because "id" is not your primary key field? Try adding ", primary key (id)" to the query and see if that works...

surrealillusions

4:50 pm on Jan 28, 2008 (gmt 0)

Gotcha..got it working now

thanks

I had to make the id the primary key by pressing the 'primary' icon in phpmyadmin, and then adding the auto increment in the extras bit.