Error When Editing MySQL Field to AUTO_INCREMENT

I am trying to edit a field in MySQL via PHP to NOT NULL and AUTO_INCREMENT. I have tried the following route to do this but I keep getting errors. Any idea why?

$con = mysql_connect("host", "user", "pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database_name", $con);

$int = "ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT(11) NOT NULL AUTO_INCREMENT";

if (!mysql_query($int,$con))
  {
  die('Heres Your Error: ' . mysql_error());
开发者_如何学编程  }

mysql_close($con)

This is the error message:

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

The problem with MySQL in this instance is the "chicken and the egg" situation.

If the table already has an AUTO_INCREMENT column, the first half of the error message applies in that only one column can be AUTO_INCREMENT.

Creating a table with AUTO_INCREMENT must be done in conjunction with defining the PRIMARY KEY.

If the table does not have an AUTO_INCREMENT column, the second half of the error message applies in that creating an AUTO_INCREMENT must be done in conjunction with PRIMARY KEY.

Try this:

ALTER TABLE `table_name` CHANGE `column_name` `column_name` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;

It sounds like you either already have an auto increment field or you already have a Primary Key on the table.

If you want your new column to be an auto-increment, you will have to drop your existing Key to add this new auto-increment field to the table

MySQL Doc on Alter table