Add Auto Increment to Existing Column in MySQL
The auto-increment is an attribute in MySQL that allows a unique number to be automatically generated when new records are added to a table. Sometimes we want the primary key to be automatically generated, so it is usually applied in the primary key columns in order to add a unique number to each row in the table.
If you define a column with the auto-increment feature, MySQL will automatically provide a new number to each inserted row in the column of the table.
In this article, we will see how to add an auto-increment to an existing column in MySQL, but before that, let us understand the necessity of adding an auto-increment to a column.
Need to add auto-increment to an existing column in MySQL:
Steps to add auto-increment to an existing column in MySQL:
Connect to your MySQL database:
Open the MySQL command-line client and connect to your MySQL database with the help of suitable credentials.
Back up your data:
Before making any changes to your database structure, it is always a good practice to back up your data to avoid any possible data loss or unexpected changes.
Alter the Column:
To add the auto-increment attribute to an existing column in the database, you have to modify its definition using the ALTER TABLE statement. The syntax for adding auto-increment may vary depending on the database.
You have to use the name of your table in place of 'tableName', the name of the existing column in place of 'columnName', and the data type of the existing column in place of 'columnDataType'.
Let us consider an existing table called 'Students', which contains columns like Id, Name, Address, Subject, and Marks.
Use the given command to describe the table:
Before adding an auto-increment feature to the column, the description of the 'Students' table looks like it is shown below.
We will add an auto-increment attribute to an existing column 'Id' in the 'Students' table. We will use the given statement to add auto increment:
Now, we will again describe the 'Students' table using the given command:
You can see below that the description of the existing 'Id' column in the 'Students' table is set to auto-increment.
Now, if you insert a new record in the table, you do not require to provide any value in the 'Id' column, as it will be auto-incremented.
Let us insert a new record in the 'Students' table, but we will not provide any value for the 'Id' column. We will use the given command to insert a new record:
Use the given command to view the records of the table:
As you can see in the below table, we have not given any value to the 'Id' column, but it is auto-incremented.
Let us consider another existing table called 'Employees', which contains columns like EmpId, EmpName, EmpAddress, EmpDepartment, and EmpSalary.
We will add an auto-increment attribute to an existing column 'EmpId' in the 'Employees' table. We will use the given statement:
Let us insert a new record in the 'Students' table, but we will not provide a value for the 'EmpId' column. We use the following command to insert a new record:
INSERT INTO Employees(EmpName, EmpAddress, EmpDepartment, EmpSalary)
Use the given statement to view the records of the table:
As you can see in the below table, we have not provided value to the 'EmpId' column, but it is auto-incremented.
In this article, you have learned how to add auto increment to an existing column in MySQL. The auto-increment feature is used to automatically assign a unique value whenever a new record is added to the table. It provides data integrity and simplifies data entry. You can add auto increment using the steps discussed above in this article.