How to Add AUTO_INCREMENT to an Existing MySQL Table Column?

Altering a Table to Add AUTOINCREMENT in MySQL

Imagine you have an existing MySQL table with a column named itemID, and you wish to modify it to automatically generate unique values upon insertion. This can be achieved effortlessly using ALTER statements.

To rectify the syntax error you encountered in your previous code, let's delve into the correct approach:

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

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

DESC ALLITEMS;

Break down the commands:

• CREATE TABLE creates a new table named ALLITEMS with the initial column definitions.
• ALTER TABLE modifies the existing ALLITEMS table.
• CHANGE is used here because itemid is already a column in the table and we want to change its definition.
• AUTO_INCREMENT specifies that the itemid column will automatically increment by 1 each time a new row is inserted.
• PRIMARY KEY defines itemid as the primary key, ensuring uniqueness within the table.
• DESC displays the updated table structure, confirming the added AUTOINCREMENT property.

Finally, you can insert data and observe the automatic assignment of unique item IDs:

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

SELECT
    *
FROM
    ALLITEMS;

The above is the detailed content of How to Add AUTO_INCREMENT to an Existing MySQL Table Column?. For more information, please follow other related articles on the PHP Chinese website!