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