How to Add an Identity to an Existing Column in MySQL?

Adding an identity (auto-increment) property to an existing column in MySQL is a common task when you want to assign unique values automatically. This feature is particularly useful for maintaining unique identifiers in a table. In this guide, we will explore the syntax, and usage, and provide examples of how to add an identity to an existing column in MySQL.

In this article, we will explore the topic of how we can add an identity to an existing column in MySQL. using the same method and with the help of syntax and working examples.

Adding an Identity (Auto-Increment) to an Existing Column in MySQL

To add an identity property to an existing column in MySQL, you need to use the ALTER TABLE statement along with the MODIFY clause. Here’s the basic Syntax:

ALTER TABLE your_table

MODIFY COLUMN your_column INT AUTO_INCREMENT;

• your_table: Replace this with the name of the table containing the column you want to modify.
• your_column: Specify the name of the existing column to which you want to add the identity property.

Example of Adding an Identity (Auto-Increment) to an Existing Column in MySQL

Example 1: Adding Identity to an Existing Column

In this example we have created the database as “Identity_db” and consider a table named employees with a column employee_id that needs to be converted into an auto-incrementing identity column.

-- SQL Code
CREATE DATABASE Identity_db;
USE Identity_db;

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(50)
);

INSERT INTO employees (employee_id, employee_name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Bob');

-- Before modification
SELECT * FROM employees;

-- Adding identity to the existing column
ALTER TABLE employees
MODIFY COLUMN employee_id INT AUTO_INCREMENT;

-- After modification
SELECT * FROM employees;

Output:

Before Modification Output

After modification Output:

After modification Output

Explanation: The SQL code modifies the employee_id column in the employees table to have an auto-incrementing identity property. The output displays the updated data with new employee_id values automatically assigned in sequential order.

Example 2: Adding Identity to an Existing Column with Data

In this example, let’s add an identity property to an existing column order_number in the orders table, which already contains data.

-- SQL Code
CREATE DATABASE Identity_db;
USE Identity_db;

CREATE TABLE orders (
    order_number INT,
    product VARCHAR(50)
);

INSERT INTO orders (order_number, product) VALUES
(101, 'Laptop'),
(102, 'Smartphone'),
(103, 'Tablet');

-- Before modification
SELECT * FROM orders;

-- Adding identity to the existing column
ALTER TABLE orders
MODIFY COLUMN order_number INT AUTO_INCREMENT;

-- After modification
SELECT * FROM orders;

Output:

Before modification Output

-- Adding identity to the existing column
ALTER TABLE orders
MODIFY COLUMN order_number INT AUTO_INCREMENT;

This statement is expected to encounter an error similar to the one you mentioned previously, as MySQL only allows one auto-increment column per table, and it must be defined as a key. To resolve this, you might need to ensure that order_number is not part of any key or index.

-- Drop existing key or index
ALTER TABLE orders DROP PRIMARY KEY;
-- Adding identity to the existing column
ALTER TABLE orders
MODIFY COLUMN order_number INT AUTO_INCREMENT;

So, let’s assume you’ve resolved any key or index issues and proceed with the modified query.

-- After modification
SELECT * FROM orders;

Output:

After modification Output

Explanation: The SQL code modifies the order_number column in the orders table, adding the AUTO_INCREMENT property. The output displays the updated data with new order_number values automatically assigned in sequential order, ensuring uniqueness for each record.

Conclusion

Adding an identity to an existing column in MySQL provides a seamless way to introduce auto-incrementing values to unique identifiers. The ALTER TABLE statement with the MODIFY clause simplifies this process. It is crucial to be cautious when modifying columns with existing data, ensuring a smooth transition to maintain data integrity. Overall, this feature enhances the usability and efficiency of database tables in various scenarios.