Mysql - Alter a column to be AUTO_INCREMENT

Question

I'm trying to modify a table to make it's primary key column AUTO_INCREMENT after the fact. I have tried the following sql, but got a syntax error:

ALTER TABLE document
ALTER COLUMN document_id AUTO_INCREMENT

Am I doing something wrong or is this not possible?

+--------------------+
| VERSION()          |
+--------------------+
| 5.0.75-0ubuntu10.2 |
+--------------------+

Answer 1

alter table document modify column document_id int(4) auto_increment

Answer 2

AUTO_INCREMENT is part of the column's datatype, you have to define the complete datatype for the column again:

ALTER TABLE document
ALTER COLUMN document_id int AUTO_INCREMENT

(int taken as an example, you should set it to the type the column had before)

Answer 3

You must specify the type of the column before the auto_increment directive, i.e. ALTER TABLE document ALTER COLUMN document_id INT AUTO_INCREMENT.

Answer 4

Roman is right, but note that the auto_increment column must be part of the PRIMARY KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY):

ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY

Answer 5

The SQL to do this would be:

ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;

There are a couple of reasons that your SQL might not work. First, you must re-specify the data type (INT in this case). Also, the column you are trying to alter must be indexed (it does not have to be the primary key, but usually that is what you would want). Furthermore, there can only be one AUTO_INCREMENT column for each table. So, you may wish to run the following SQL (if your column is not indexed):

ALTER TABLE `document` MODIFY `document_id` INT AUTO_INCREMENT PRIMARY KEY;

You can find more information in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.html for more information about specifying columns.