How to set auto increment in MySQL

What is auto increment
Auto increment is an option which automatically generates the value of a column. MySQL provides AUTO_INCREMENT option to mark a column as auto increment.
When a row is inserted in a table, then the column marked as AUTO_INCREMENT generates a value which is 1 more than the value of this column that was last inserted.
Usually, primary key column is marked as AUTO_INCREMENT.

Change auto increment initial value
By default, the starting value of the column which is marked as AUTO_INCREMENT will be 1. This means that when the first row is inserted in a table, then the AUTO_INCREMENT column will be 1.
But it is possible to change the initial value of auto increment at the time of table creation and after table is created.
At table creation
Syntax for specifying the initial value of an auto increment column while creating the table is by writing the initial value after AUTO_INCREMENT option as shown below.

CREATE TABLE STUDENT(
   id INT NOT NULL AUTO_INCREMENT 1000,
   name VARCHAR2(100),
   PRIMARY KEY(id)
);

For the above table, the value of id will start from 1000. Thus, when first record will be inserted into the above table, its id will be 1000. For second record, the value of id will be 1001 and so on.

After table creation
It is also possible to change the value of auto increment column even after the table is created and contains some records using ALTER TABLE statement.
Syntax for this is shown below.

ALTER TABLE [Table Name] AUTO_INCREMENT = [value];

In this case, the value for auto increment column starts from the given value when a new record is inserted after AUTO_INCREMENT initial value is modified.
Thus, suppose a table contains 2 records with the value of auto increment column as 1 and 2. When the following query is executed

ALTER TABLE [Table name] AUTO_INCREMENT = 100;

then the first record that is inserted after this query will have the value of auto increment column as 100, next one will have 101 and so on.

Click the clap below if you liked this article.

0

Leave a Reply

Mark Your Impression

  Subscribe  
Notify of
Close Menu

Never Miss an article !

Get the new post delivered straight into your inbox, enter your email and hit the button

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

codippa will use the information you provide on this form to be in touch with you and to provide updates and marketing.