PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: Add auto_increment to existing column in mysql

26 Jan 2014

Add auto_increment to existing column in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key.


Create table with auto_increment in mysql:


                      You can add auto_increment while creating new table. The mysql query is:

                       CREATE TABLE table1 
                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                                 Game_name varchar(15), Image varchar(15)) 
Now the table is created with auto increment.


Add auto_increment to existing column in mysql:


                      You created a table in which the column with specific definitions in mysql. You created table column without auto increment. Then how can you add it to existing column in mysql.  The following mysql query is used to add auto increment to existing column in mysql.
Mysql Query:
                         Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY


Consider the example:
The structure of table1 in database new look like this:


Add auto increment to existing column in mysql


                         Now we are going to add auto increment to column 'No' in mysql table 'table1'.  The mysql query is:
                     Alter table table1 modify No int(5) AUTO_INCREMENT PRIMARY KEY

Now your structure look like this:


Add auto increment to column in mysql


     Now you can see the column name 'No' is auto increment. Suppose  your mysql query like below:

     Alter table table1 modify No int(5) AUTO_INCREMENT

Then you'll get error like this:


mysql error while adding auto increment in mysql


                                 So you should be add 'AUTO_INCREMENT' with 'PRIMARY KEY' in mysql table.

Related Post:

2 comments:

  1. thank you... It is useful

    ReplyDelete
  2. Sometimes you should be careful with auto_increment. More info you can read here: Be Careful With MySQL's auto_increment. How We Ended Up Losing Data I leave it here and maybe it will be useful for some people. Cheers!

    ReplyDelete

Note: only a member of this blog may post a comment.