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 (
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.
ALTER TABLE <table_name> CHANGE <field_name> <field_name> INT(number like 1) AUTO-INCREMENT
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
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.