Trending News
Home / mySQL / Alter a column to be AUTO_INCREMENT – Mysql

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.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com