Auto Increment columns start with a default value of one and automatically increment by one whenever a new row is added to MySQL table. It is advisable to create table with auto increment column since they help maintain uniqueness of rows. But sometimes you may already have a table full of data and without an auto increment column. And you may need to add auto increment ID to existing table in MySQL. In this article, we will learn how to do this.
How to Add Auto Increment ID to Existing Table in MySQL
Let us say you have the following table users(fname, lname, email, password).
mysql> create table users(fname varchar(10), lname varchar(10, email varchar(10), password varchar(10), primary key(email));
Let us say you add some rows of data into it.
insert into users(fname, lname, email, password)
values('John', 'Doe', 'john.doe@gmail.com','john'),
('Jim', 'Doe', 'jim.doe@gmail.com','jim'),
('Jane', 'Doe', 'jane.doe@gmail.com','jane');
Let us say you try to add an auto increment column ID using ALTER TABLE statement as shown below.
mysql> ALTER TABLE users mysql> ADD id int NOT NULL AUTO_INCREMENT
If you run the above statement, you will get the following error.
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
This is because auto increment column must be a part of primary key in your table whereas it already has email column as primary key. In such cases, you need to drop primary key first, then add auto increment column, and then set it as primary key.
mysql> ALTER TABLE users DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
If you want, you can separate the dropping of primary key and adding of auto increment as shown below.
mysql> ALTER TABLE users DROP PRIMARY KEY mysql> ALTER TABLE users ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
If the above command does not work for you, you can use the following statement to set newly added auto increment field as primary key.
mysql> ALTER TABLE users ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;
In this article, we have learnt how to add auto increment column to existing MySQL table. It is important to remember that auto increment column must be set as primary key. So if your table already has primary key defined, then you need to drop it first, then set the auto increment column as primary key. Otherwise, you will get an error. If your table does not have any primary key, then you will not get any error while adding auto increment key.
Also read:
JS File Upload Size Validation
How to Print Contents of Div in JavaScript
How to Check if Web Page is Loaded in IFrame or Web Browser
How to Detect If Internet Connection is Offline in JavaScript
How to Remove All Child Elements of DOM Node in JS