Search code examples
mysqlsqlauto-incrementalter-table

Make column auto_increment and reposition

Hey. I'm currently learning to use MySql and have a couple of questions.

1) How do I update a column (empty table) to make it an AUTO_INCREMENT column? I tried

ALTER TABLE book UPDATE id AUTO_INCREMENT;

but I get an SQL Syntax error.

2) If I add a new column, how can I change its position in the table? For example, if I add a new column, it's added to the end. What if I want to move it to the beginning?

Thanks

Solution

  • Per the ALTER Syntax you probably want to be using the MODIFY keyword (not UPDATE).

    Also, not sure if it will work (depending on your column defintiions), but this is how you'd go about it:

    Try this:

    ALTER TABLE book
MODIFY id AUTO_INCREMENT;

    If it doesn't you'll need to drop the column first, then re-add it:

    ALTER TABLE book
DROP COLUMN id;

ALTER TABLE book
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST; -- "FIRST": Add column to beginning

    You may also want it to be the PRIMARY KEY, but I don't know what your table structure is so just a guess.