SQL: Add new auto increment column to existing table

Using Alter, AUTO_INCREMENT command, you can a dd an auto increment column to existing table.

 

Syntax

ALTER TABLE {TABLE_NAME} ADD {COLUMN_NAME} INT PRIMARY KEY AUTO_INCREMENT;

 

For example, let’s create an employee table.

 

CREATE TABLE employee (

    first_name varchar(255) NOT NULL,

    last_name varchar(255)

);

 

Insert some data to employee table.

 

INSERT INTO employee(first_name, last_name) VALUES('krishna', 'gurram');

INSERT INTO employee(first_name, last_name) VALUES('gopi', 'battu');

INSERT INTO employee(first_name, last_name) VALUES('sailu', 'dokku');

 

mysql> CREATE TABLE employee (
    ->     first_name varchar(255) NOT NULL,
    ->     last_name varchar(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO employee(first_name, last_name) VALUES('krishna', 'gurram');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO employee(first_name, last_name) VALUES('gopi', 'battu');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee(first_name, last_name) VALUES('sailu', 'dokku');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM employee;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| krishna    | gurram    |
| gopi       | battu     |
| sailu      | dokku     |
+------------+-----------+
3 rows in set (0.00 sec)

 

Let’s add new column emp_id with auto increment.

mysql> ALTER TABLE employee ADD emp_id INT PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM employee;
+------------+-----------+--------+
| first_name | last_name | emp_id |
+------------+-----------+--------+
| krishna    | gurram    |      1 |
| gopi       | battu     |      2 |
| sailu      | dokku     |      3 |
+------------+-----------+--------+
3 rows in set (0.00 sec)

 

 

 

Previous                                                    Next                                                    Home