MYSQL Auto Increment
Category : TECH Author : Ankur Rastogi Date : Sat Jun 04 2016 Views : 32

MYSQL Auto Increment is used to declare any field as auto increment. Which means that whenever a new record is inserted into the table this value will gets generated by incrementing its value in previous row. Often it is used as a primary key as it generated unique value for each record.
Often when we create a new table. We use to keep an Id field thats purpose is to uniquely define each record. So we can keep this record is auto increment primary key as this will get auto generated by an incremented value each time a new record is inserted in it.
Syntax for declaring a column auto increment is
CREATE TABLE table_name (column_name1 auto increment , column_name2 data_type, column_name3 data_type);
By default starting value for auto increment field is 1. If we want to change the starting value for auto increment than syntax for that will be
CREATE TABLE table_name (column_name1 auto increment = 20 , column_name2 data_type, column_name3 data_type);
Let us explore this more with the help of example.
Consider Table student_list which contains details for student
+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
| 1 | Ankur Rastogi | First | 4 |
| 2 | Amit Kumar | Second | 8 |
| 3 | Ram Kumar | Second | 8 |
| 4 | Shiv Saxena | First | 6 |
| 5 | Aman Aggarwal | Third | 3 |
| 6 | Nipun Gupta | Fourth | 2 |
| 7 | Arpit Gupta | Third | 1 |
| 8 | Shantanu Rastogi | Second | 3 |
| 9 | Ramesh Kumar | Third | 1 |
| 10 | Neelima Dhingra | Second | NULL |
| 11 | Anu Sibbal | Third | NULL |
+-----+------------------+--------+-----------+
Let us see create table command for student_list using auto increment.
CREATE TABLE student_list (sid int(5) NOT NULL AUTO_INCREMENT, name varchar(200) NOT NULL, year varchar(10) NOT NULL, hostel_id tinyint(1) DEFAULT 0, PRIMARY KEY(sid));
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| sid | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | | NULL | |
| year | varchar(10) | NO | | NULL | |
| hostel_id | tinyint(1) | YES | | 0 | |
+-----------+--------------+------+-----+---------+----------------+

MYSQL Auto Increment is used to declare any field as auto increment. Which means that whenever a new record is inserted into the table this value will gets generated by incrementing its value in previous row. Often it is used as a primary key as it generated unique value for each record.
Often when we create a new table. We use to keep an Id field thats purpose is to uniquely define each record. So we can keep this record is auto increment primary key as this will get auto generated by an incremented value each time a new record is inserted in it.
Syntax for declaring a column auto increment is
CREATE TABLE table_name (column_name1 auto increment , column_name2 data_type, column_name3 data_type);
By default starting value for auto increment field is 1. If we want to change the starting value for auto increment than syntax for that will be
CREATE TABLE table_name (column_name1 auto increment = 20 , column_name2 data_type, column_name3 data_type);
Let us explore this more with the help of example.
Consider Table student_list which contains details for student
+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
| 1 | Ankur Rastogi | First | 4 |
| 2 | Amit Kumar | Second | 8 |
| 3 | Ram Kumar | Second | 8 |
| 4 | Shiv Saxena | First | 6 |
| 5 | Aman Aggarwal | Third | 3 |
| 6 | Nipun Gupta | Fourth | 2 |
| 7 | Arpit Gupta | Third | 1 |
| 8 | Shantanu Rastogi | Second | 3 |
| 9 | Ramesh Kumar | Third | 1 |
| 10 | Neelima Dhingra | Second | NULL |
| 11 | Anu Sibbal | Third | NULL |
+-----+------------------+--------+-----------+
Let us see create table command for student_list using auto increment.
CREATE TABLE student_list (sid int(5) NOT NULL AUTO_INCREMENT, name varchar(200) NOT NULL, year varchar(10) NOT NULL, hostel_id tinyint(1) DEFAULT 0, PRIMARY KEY(sid));
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| sid | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | | NULL | |
| year | varchar(10) | NO | | NULL | |
| hostel_id | tinyint(1) | YES | | 0 | |
+-----------+--------------+------+-----+---------+----------------+
Disclaimer: The above content reflect author’s personal views and do not reflect the views of OYEWIKI. Neither OYEWIKI nor any person/organization acting on its behalf is liable to accept any legal liability/responsibility for any error/mislead in this information or any information available on the website. This website in no way accepts the responsibility for any loss, injury, damage, discomfort or inconvenience caused as a result of reliance on any information provided on this website.
If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.