Alter a MySQL column to be AUTO_INCREMENT

sql mysql alter-table
C. Ross picture C. Ross · Jan 30, 2010 · Viewed 419k times · Source

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification.

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

roman picture roman · Jan 30, 2010 
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

Related questions

ALTER TABLE to add a composite primary key

I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination. How would I ALTER TABLE to add …

Read More
ALTER table - adding AUTOINCREMENT in MySQL

I created a table in MySQL with on column itemID. After creating the table, now I want to change this column to AUTOINCREMENT. How can this be done using ALTER statements? Table definition: ALLITEMS (itemid int(10) unsigned, itemname varchar(50)) I …

Read More
ALTER TABLE without locking the table?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a …

Read More