Alter a column to be AUTO_INCREMENT – Mysql

Hi friends,
Today, I’m trying to modify a table to make it’s primary key column AUTO_INCREMENT after the fact. Here I going to demonstrate how to reset auto-increment value in MYSQL. The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

First create the table with field. Here we create the table name sub_heads with id as integerlength 10, abbreviation as varchar length 10 and subhead_item as varchar length 50.

CREATE TABLE sub_heads (
id int(10),
abbreviation varchar(10),
subhead_item varchar(50)
);

After creating table `sub_heads`, alter the table structure, put id as integer and put it as autoincrement.For modify a table structure we use ALTER table command.

I created a table ‘sub_heads’ in MySQL with on column `id,abbreviation,subhead_item`. After creating the table, now I want to change this column `id` to AUTOINCREMENT.

SYNTAX

ALTER TABLE <table_name> CHANGE <field_name> <field_name> INT(number like 1) AUTO-INCREMENT

Example:
1)ALTER TABLE `sub_heads` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;

OR
2)Alter table sub_heads modify id int(10) AUTO_INCREMENT

There are two method available in alter a column to be auto-increment in MYSQL. CHANGE and MODIFY are available in alter command. We can modify the table structure using these methods.