SQL > Advanced SQL > AUTO_INCREMENT

AUTO_INCREMENT is used in MySQL to create a numerical primary key value for each additional row of data.

Syntax

The syntax for AUTO_INCREMENT is as follows:

CREATE TABLE TABLE_NAME
(PRIMARY_KEY_COLUMN INT NOT NULL AUTO_INCREMENT
...
PRIMARY KEY (PRIMARY_KEY_COLUMN));

Example

Assume we want to create a table that consists of a primary key, last name, and first name. We run the following CREATE TABLE statement:

CREATE TABLE USER_TABLE
(Userid int NOT NULL AUTO_INCREMENT,
Last_Name varchar(50),
First_Name varchar(50),
PRIMARY KEY (Userid));

Upon creation, there is no data in this table.

We insert the first value:

INSERT INTO USER_TABLE VALUES ('Perry', 'Jonathan');

Now the table contains the following data:

Table USER_TABLE

UseridLast_NameFirst_Name
1PerryJonathan

We then insert the second value:

INSERT INTO USER_TABLE VALUES ('Davis', 'Nancy');

Now the table has the following values:

Table USER_TABLE

UseridLast_NameFirst_Name
1PerryJonathan
2DavisNancy

Notice when we insert the first row, Userid is set to 1. When we insert the second row, Userid increases by 1 and becomes 2.

By default, AUTO_INCREMENT starts with 1 and increases by 1. To change the default starting value, we can use the ALTER TABLE command as follows:

ALTER TABLE TABLE_NAME AUTO_INCREMENT = [New Number];

where [New Number] is the starting value we want to use.

The AUTO INCREMENT interval value is controlled by the MySQL Server variable auto_increment_increment and applies globally. To change this to a number different from the default of 1, use the following command in MySQL:

mysql> SET @@auto_increment_increment = [interval number];

where [interval number] is the interval value we want to use. So, if we want to set the interval to be 5, we would issue the following command:

mysql> SET @@auto_increment_increment = 5;


Next: SQL IDENTITY




Copyright © 2017   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact