What is Auto-increment in MySQL and how to set it?

A very often and commonly used feature in databases in general (and MySQL is not an exemption) is to set a field of a row that will automatically increment the value upon inserting it in a database. In other words it will have a unique number that is increased for 1 from the last added row.

Usually developers like to add a column named ID in their tables, and use those fields as auto-increments. That is done to easily distinguish rows among each other, and the ID columns will never have a duplicate.

Syntax

CREATE TABLE Office

   (

   ID int NOT NULL AUTO_INCREMENT,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Address varchar(255),

   City varchar(255),

   PRIMARY KEY (ID)

   );

In the example above, the column ID will always increase for 1 upon every new record being added.

The starting auto-increment number is 1, but that may be changed by using ALTER TABLE statement, as shown:

In case if we want to start with 90 and also each time increase by another number, let say 5 instead of one, a keyword IDENTITY must be used:

CREATE TABLE Office

   (

   ID int IDENTITY(90,5) NOT NULL AUTO_INCREMENT,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Address varchar(255),

   City varchar(255),

   PRIMARY KEY (ID)

   );

A common practice is to assign the auto-increment as a PRIMARY KEY in which case our example form above would look like this:

CREATE TABLE Office

   (

   ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Address varchar(255),

   City varchar(255),

   PRIMARY KEY (ID)

   );