Alter a MySQL Column to Be Auto_Increment

Alter a MySQL column to be AUTO_INCREMENT

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

How to add AUTO_INCREMENT to an existing column?

I think you want to MODIFY the column as described for the ALTER TABLE command. It might be something like this:

ALTER TABLE users MODIFY id INTEGER NOT NULL AUTO_INCREMENT;

Before running above ensure that id column has a Primary index.

ALTER table - adding AUTOINCREMENT in MySQL

CREATE TABLE ALLITEMS(
    itemid INT(10)UNSIGNED,
    itemname VARCHAR(50)
);

ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY;

DESC ALLITEMS;

INSERT INTO ALLITEMS(itemname)
VALUES
    ('Apple'),
    ('Orange'),
    ('Banana');

SELECT
    *
FROM
    ALLITEMS;

I was confused with CHANGE and MODIFY keywords before too:

ALTER TABLE ALLITEMS CHANGE itemid itemid INT(10)AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE ALLITEMS MODIFY itemid INT(5);

While we are there, also note that AUTO_INCREMENT can also start with a predefined number:

ALTER TABLE tbl AUTO_INCREMENT = 100;

Insert auto increment primary key to existing table

An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.

mysql add an autoincrement column with start value

No, it works to add an AI column with a starting position. But you almost got the syntax right. Here's a demo:

mysql> CREATE TABLE foo (v varchar(10));

mysql> INSERT INTO foo VALUES ('one'), ('two'), ('three');

Then comes the tricky syntax. You have to declare the column as AUTO_INCREMENT, but then also give the table option for the AUTO_INCREMENT starting value. And you need a comma to separate the ADD COLUMN from the table option.

mysql> ALTER TABLE foo ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY,
    AUTO_INCREMENT=999999;

mysql> select * from foo;
+-------+---------+
| v     | id      |
+-------+---------+
| one   |  999999 |
| two   | 1000000 |
| three | 1000001 |
+-------+---------+

---

Related Topics