How to use auto_increment comand after the table is already created in mysql?

Question

I have a table named employee which has three columns empid (which is int), empname (varchar) and salary(int). I have already made the table and inserted some data as well. Now I want to make empid autoincrement.

I used the command:

ALTER TABLE employee ADD AUTO_INCREMENT(empid);

But I get the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(empid)' at line 1

Answer 1

You need to alter the table, to modify the column itself, and add the AUTO_INCREMENT to that column.

ALTER TABLE employee 
MODIFY empid INT(11) NOT NULL AUTO_INCREMENT;

If it isn't already the primary key, you should add that as well

ALTER TABLE employee 
MODIFY empid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;