AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Syntax for MySQL

The following SQL statement defines the "Student_id" column to be an auto-increment primary key field in the "Student" table:

Example

 CREATE TABLE  student(
Student_id int  NOT NULL AUTO INCREMENT ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
Age int,
 PRIMARY KEY  (Student_id)
 );

You can click on above box to edit the code and run again.

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Student AUTO_INCREMENT = 100;

To insert a new record into the "Student" table, we will NOT have to specify a value for the "Student_id" column (a unique value will be added automatically):

INSERT INTO Student(First_name, Last_name) VALUE ( 'Lars', 'Monsen') ; The SQL statement above would insert a new record into the "Student" table. The "Personid" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".