How to add AUTO INCREMENT to an existing column

Question

How do I add auto increment to an existing column of a MySQL table

User · Answer

ALTER TABLE Table name ADD column datatype AUTO INCREMENT ADD primary key column

User · Answer

if you have FK constraints and you don t want to remove the constraint from the table  use  index  instead of primary  then you will be able to alter it s type to auto increment

User · Answer

This worked for me in case you want to change the AUTO INCREMENT-attribute for a not-empty-table  1  Exported the whole table as  sql file 2  Deleted the table after export 2  Did needed change in CREATE TABLE command 3  Executed the CREATE TABLE and INSERT INTO commands from the  sql-file    et viola

User · Answer

I had existing data in the first column and they were 0 s  First I made the first column nullable  Then I set the data for the column to null  Then I set the column as an index  Then I made it a primary key with auto incrementing turned on  This is where I used another persons answer above  ALTER TABLE  table name  CHANGE COLUMN  colum name   colum name  INT 11  NOT NULL AUTO INCREMENT FIRST   This Added numbers to all the rows of this table starting at one  If I ran the above code first it wasn t working because all the values were 0 s  And making it an index was also required before making it auto incrementing  Next I made the column a primary key

User · Answer

I managed to do this with the following code   ALTER TABLE  table name  CHANGE COLUMN  colum name   colum name  INT 11  NOT NULL AUTO INCREMENT FIRST    This is the only way I could make a column auto increment    INT 11  shows that the maximum int length is 11  you can skip it if you want

User · Answer

Simply just add auto increment Constraint In column or MODIFY COLUMN  -     ALTER TABLE  emp  MODIFY COLUMN  id  INT NOT NULL UNIQUE AUTO INCREMENT FIRST    Or add a column first then change column as -  1  Alter TABLE  emp  ADD COLUMN  id    2  ALTER TABLE  emp  CHANGE COLUMN  id   Emp id  INT NOT NULL UNIQUE AUTO INCREMENT FIRST

User · Answer

Alter table table name modify table name column name data type AUTO INCREMENT   eg    Alter table avion modify avion av int AUTO INCREMENT

User · Answer

Alter table table name modify column name datatype length  AUTO INCREMENT PRIMARY KEY   You should add primary key to auto increment  otherwise you got error in mysql

User · Answer

Method to add AUTO INCREMENT to a table with data while avoiding    Duplicate entry    error    Make a copy of the table with the data using INSERT SELECT   CREATE TABLE backupTable LIKE originalTable   INSERT backupTable SELECT   FROM originalTable   Delete data from originalTable  to remove duplicate entries    TRUNCATE TABLE originalTable   To add AUTO INCREMENT and PRIMARY KEY  ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO INCREMENT   Copy data back to originalTable  do not include the newly created column  id   since it will be automatically populated   INSERT originalTable  col1  col2  col3   SELECT col1  col2 col3 FROM backupTable   Delete backupTable   DROP TABLE backupTable     I hope this is useful   More on the duplication of tables using CREATE LIKE   Duplicating a MySQL table  indexes and data

User · Answer

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

[mysql] How to add AUTO_INCREMENT to an existing column?

Examples related to mysql

Examples related to auto-increment