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:

• Manually assigning unique values to each record becomes hard when working with big datasets. You can apply auto increment to a column as it automatically generates a unique number whenever a new record is inserted in the table and eliminates the need to specify a value for that column explicitly.
• A primary key is used to uniquely identify each row in a table, which makes sure data integrity. You can use the auto-incremented column as the primary key of the table. It simplifies the management of the primary keys.
• By using an auto-increment column, you can ensure data consistency within the column. Each new row inserted will be provided a value that is greater than the previous row, which maintains a sequential order. This sequential order may be important for some types of analysis, sorting, or reporting operations on the data.
• In databases that establish relationships between tables using foreign keys, an auto-increment primary key simplifies the process of maintaining referential integrity. When creating foreign key relationships, the auto-increment value serves as a unique identifier that can be referenced by other tables, ensuring consistency and integrity in the relationships between the tables.
• Adding an auto-increment column to an existing table can facilitate data migration and integration tasks. When combining data from different sources or merging tables, having an auto-increment column helps avoid conflicts or duplication of primary key values.
• Adding auto-increment to an existing column makes sure that new records added to the table contain unique identifiers, even when you are working with a dataset that already contains records. It allows for seamless data expansion without altering existing data or modifying primary key constraints.

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.

Syntax:

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'.

Example 1:

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.

Example 2:

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.

Conclusion:

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.